abdou / classroom
Back to SQL Tutorial

SQL Lesson 7: OUTER JOINs

Learn to use LEFT JOIN, RIGHT JOIN, and FULL JOIN to include unmatched rows from tables.

SQL Lesson 7: OUTER JOINs

Depending on how you want to analyze the data, the INNER JOIN we used last lesson might not be sufficient because the resulting table only contains data that belongs in both of the tables.

If the two tables have asymmetric data, which can easily happen when data is entered in different stages, then we would have to use a LEFT JOIN, RIGHT JOIN or FULL JOIN instead to ensure that the data you need is not left out of the results.

SELECT
column, another_column, ...
FROM
mytable
INNER/LEFT/RIGHT/FULL JOIN
another_table
ON
mytable.id = another_table.matching_id
WHERE
condition(s)
ORDER BY
column, ... ASC/DESC
LIMIT
num_limit OFFSET num_offset;

Like the INNER JOIN these three new joins have to specify which column to join the data on.
When joining table A to table B, a LEFT JOIN simply includes rows from A regardless of whether a matching row is found in B. The RIGHT JOIN is the same, but reversed, keeping rows in B regardless of whether a match is found in A. Finally, a FULL JOIN simply means that rows from both tables are kept, regardless of whether a matching row exists in the other table.

When using any of these new joins, you will likely have to write additional logic to deal with NULLs in the result and constraints (more on this in the next lesson).

Did you know?

You might see queries with these joins written as LEFT OUTER JOIN,RIGHT OUTER JOIN, or FULL OUTER JOIN, but the OUTERkeyword is really kept for SQL-92 compatibility and these queries are simply equivalent toLEFT JOIN, RIGHT JOIN, and FULL JOIN respectively.

Exercise

In this exercise, you are going to be working with a new table which stores fictional data about Employeesin the film studio and their assigned office Buildings. Some of the buildings are new, so they don't have any employees in them yet, but we need to find some information about them regardless.

Since our browser SQL database is somewhat limited, only the LEFT JOIN is supported in the exercise below.

Table: buildings (Read-only)

Table: employees (Read-only)

Query results

Exercise 7 — Tasks

  1. Find the list of all buildings that have employees
  2. Find the list of all buildings and their capacity
  3. List all buildings and the distinct employee roles in each building (including empty buildings)

Solve all tasks to continue to the next lesson.

Progress0 of 3 tasks completed