Constructing Queries That Retrieve Data From Multiple Tables

The previous lesson covered the conceptual difference between an inner join and an outer join, using the Employees and Timesheets tables to show how each one answers a different question. This lesson covers the actual Transact-SQL syntax for constructing both.

SELECT <column_name>, ...n
FROM ltable_name [LEFT JOIN | RIGHT JOIN | FULL JOIN | INNER JOIN]
      rtable_name ON
      lcolumn_name = rcolumn_name
      [WHERE <search_conditions>]

A note on terminology before going further: LEFT JOIN, RIGHT JOIN, and FULL JOIN here are shorthand. The word OUTER is optional in T-SQL, so LEFT JOIN and LEFT OUTER JOIN mean exactly the same thing, as do the right and full equivalents. The previous lesson used the full "outer join" terminology to keep the concept clear; this syntax uses the shorter form you will actually type most often.

Explanation:

1. <column_name> is the name of the column from which you are requesting data. If you wish to retrieve data from all columns, you can use the * wildcard.
2. ltable_name is the name of the table on the left side of the join.
3. rtable_name is the name of the table on the right side of the join.
4. lcolumn_name is the join column from ltable_name, the one whose values are compared against rcolumn_name to determine which rows match.
5. rcolumn_name is the join column from rtable_name, compared against lcolumn_name for the same purpose.
6. search_conditions, in the WHERE clause, specifies which rows from the joined result to actually keep. If you omit WHERE, every row the join itself produces is retrieved, matched rows for an inner join, matched rows plus preserved unmatched rows for an outer join. So specifying a WHERE clause is generally a good idea once you only want a subset of that result.

Using the Employees and Timesheets tables from the previous lesson, here is the actual syntax for each of the two queries already discussed conceptually.

Inner join, returning only employees who have logged time:

SELECT Employees.EmployeeID, Employees.LastName, Timesheets.AnnualTime
FROM Employees
INNER JOIN Timesheets
ON Employees.EmployeeID = Timesheets.EmployeeID;

This returns EmployeeID 1001 and 1003 only, exactly as shown in the previous lesson's Inner Join result table. EmployeeID 1002 has no row in Timesheets, so it has no row to match, and it does not appear.

Left outer join, returning every employee, including anyone who has not yet logged time:

SELECT Employees.EmployeeID, Employees.LastName, Timesheets.AnnualTime
FROM Employees
LEFT JOIN Timesheets
ON Employees.EmployeeID = Timesheets.EmployeeID;

This returns all three employees. EmployeeID 1002 still appears, with NULL in the AnnualTime column, exactly as shown in the previous lesson's Left Outer Join result table. Employees is the left table in this query, which is why its rows are the ones preserved.

The same syntax extends to the two join types the previous lesson named but did not work through in detail.

A right join preserves every row from the table on the right side instead of the left. Swapping which table is listed first changes which side gets preserved, so a right join on Timesheets as the right table behaves the same as a left join with the table order reversed:

SELECT Employees.EmployeeID, Employees.LastName, Timesheets.AnnualTime
FROM Timesheets
RIGHT JOIN Employees
ON Timesheets.EmployeeID = Employees.EmployeeID;

This produces the same three-row result as the left join example above. Employees is still the table being preserved; it is just listed on the right side of the RIGHT JOIN instead of the left side of a LEFT JOIN.

A full join preserves every row from both tables, matched or not. With this particular data, where every Timesheets row already has a matching Employees row, a full join on these two tables would return the same result as the left join. The difference only shows up when the right table can also contain rows with no match on the left, a Timesheets entry for an EmployeeID that does not exist in Employees, for example, which this dataset does not have.

Just as OUTER is optional alongside LEFT, RIGHT, and FULL, INNER is optional in its own join. A bare JOIN, with no keyword in front of it at all, defaults to an inner join:

SELECT Employees.EmployeeID, Employees.LastName, Timesheets.AnnualTime
FROM Timesheets
JOIN Employees
ON Timesheets.EmployeeID = Employees.EmployeeID;

This produces exactly the same result as the explicit INNER JOIN example above. Most written SQL still spells out INNER JOIN anyway, since it makes the intent obvious to the next person reading the query, but recognizing a bare JOIN as an inner join matters once you start reading other people's code.

With both the concept and the syntax covered, the next lesson in this module moves on to aliasing table and column names, which becomes especially useful once a query joins several tables and the full names grow unwieldy.

Click the exercise link below to practice constructing joins across multiple tables.
Multiple Table Inner Join - Exercise