SQL Server Inner Join

This SQL tutorial focuses on the SQL Server Inner Join statement, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts describing how to use the JOIN statement in SQL Server. To read additional posts regarding this subject, please use the following links:

• SQL Server Inner JOIN – Joining data items from tables, based on values common to both tables.
• SQL Server Outer JOIN– Joining data items from tables, based on values common to both tables, while displaying all data from one table regardless of if there is a match on the second table.
• SQL Server Self JOIN– Join a table to itself.

Querying Multiple Table

Sometimes you may need to use data from more than one table. In this illustration, the result set displays data from two separate tables: First Name from Employees table, Department Name from Departments table.

• Multiple tables (two or more tables) can be linked only  if they have common values (in this case, department number) or a logical connection of some kind.
• Relating between two tables requires you to determine the join condition. In the example shown above, the join condition was based on the equality operator (=).

Joining Two Tables

SELECT table_a.column_name , table_b.column_name, table_a.column_name ..
FROM   table_a JOIN table_b
ON  table_a.column_name = table_b.column_name

For example, we would like to retrieve data from two tables – Employees and Departments. We would like to retrieve the employee’s last name and salary from the Employees table and employee’s department name from the Departments table:

SELECT employees.last_name , employees.salary ,
       departments.department_name
FROM   employees JOIN departments
ON  employees.department_id = departments.department_id

• In the SQL Server SELECT clause, precede the column name with the table name for clarity.
• When a column is common to both tables, it must be prefixed with the table name.
• In the SQL Server FROM clause, you need to specify the tables from which you would like to retrieve the data. These tables are specified with the keyword JOIN between them.
• After the ON keyword, specify the join condition.
• To determine the relation between Employees and Departments tables – values in the Department_id column on both tables must be equal. This type of relation is referred as an Equi Join. 
• Equi joins are also called Simple Joins or Inner Joins.
• Frequently, this relation involves primary key and foreign key complements.

Table Aliases

Qualifying column names with table names can be time consuming, and may result in a very long, unreadable query. In SQL Server, Instead of writing a full table name after each column, use Table Aliases. Just as Column Alias gives a column another name, a table alias gives a table another name.

Let us repeat the task shown in the previous example, this time by using Table Aliases:

SELECT emp.last_name , emp.salary , dep.department_name
FROM   employees emp JOIN departments dep
ON  emp.department_id = dep.department_id

• Use the SQL Server FROM clause to define the table aliases.
• Write an alias after each table name.
• You can assign any alias to a table (for example, you can assign the letter A to the Employees table); however, it is advisable to assign meaningful aliases.
• After defining an alias to a table, that alias must be substituted for the table name throughout the SQL Server SELECT statement. All explicit references to the table must use the table alias, not the table name.
• The table alias is valid only for the current SQL Server SELECT statement.

Adding Additional Clauses

you can carry on adding additional clauses to your SQL Server SELECT statement (restricting the rows returned, adding aggregations, defining the sorting order and so on).

Below are several SQL Server examples:

This SQL Server example would retrieve all employees whose salary is greater than 6000 and their department number equals 90:

SELECT emp.last_name , emp.salary , dep.department_name
FROM   employees emp JOIN departments dep
ON  emp.department_id = dep.department_id
WHERE emp.department_id = 90

You can change the sorting order, thereby displaying the data items sorted by the employee’s salary:

SELECT  emp.last_name , emp.salary , dep.department_name
FROM   employees emp JOIN departments dep
ON  emp.department_id = dep.department_id
WHERE emp.department_id = 90
ORDER BY emp.salary DESC

Joining an additional table requires us to:

• Specify the table name in the SQL Server FROM clause (by using an additional JOIN keyword).
• Determine the additional join condition, and specify this condition by using additional ON keyword.

SELECT emp.last_name , emp.salary , dep.department_name, reg.region
FROM  employees emp JOIN departments dep
ON emp.department_id = dep.department_id
                    JOIN regions reg
ON dep.region_id = reg.region_id

• The same concept applies to joining four tables or more – adding the table name after the JOIN keyword, and specifying additional join condition after the ON keyword.

None Equi Join

A SQL Server None Equi Join is a join condition containing something other than equality operator. For example: sometimes you would like to join two tables that do not have a shared column, and seemingly have no join condition:

What is John’s salary level? in this case there is no shared column, but you can tell that he belongs to Salary Grade: Grade C , because when comparing his salary to the data found in the Salary Grades table, it turned out that his salary is between Min Salary (3001) and  Max Salary (4000) of Grade C.

This relation is obtained using the SQL Server BETWEEN operator :

SELECT emp.last_name , emp.salary , job.grade_level
FROM   employees emp JOIN job_grades job
ON emp.salary BETWEEN job.lowest_sal AND job.highest_sal

You can join multiple tables (two or more) as long as you can find any logical condition that can be used for joining the tables.

SQL Server Cartesian Product

Also referred as CROSS JOIN, retrieves back all of the possible combinations between the tables; each row in the first table is paired with each of the rows in the second table. A cartesian product tends to generate a large number of rows, and it’s results are rarely useful.

SELECT emp.last_name, dep.department_name
FROM  employees emp CROSS JOIN departments dep