Working with joins¶

Inner join¶

An inner join pairs each row in one table with the matching rows in the other table.

The following example shows an inner join:

SELECT p.project_ID, project_name, employee_ID, employee_name, e.project_ID
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_id = p.project_id
  ORDER BY p.project_ID, e.employee_ID;

+------------+------------------+-------------+-----------------+------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   | PROJECT_ID |
|------------+------------------+-------------+-----------------+------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |       1000 |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |       1000 |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |       1001 |
+------------+------------------+-------------+-----------------+------------+

In this example, the output contains two columns named PROJECT_ID. One PROJECT_ID column is from the projects table, and one is from the employees table. For each row in the output, the values in the two PROJECT_ID columns match because the query specified e.project_id = p.project_id.

The output includes only valid pairs; that is, rows that match the join condition. In this example, there is no row for the project named NewProject, which has no employees assigned yet, or the employee named NewEmployee, who hasn’t been assigned to any projects yet.

Outer join¶

An outer join lists all rows in the specified table, even if those rows have no match in the other table. For example, a left outer join between projects and employees lists all projects, including projects that don’t yet have any employee assigned.

SELECT p.project_name, e.employee_name
  FROM projects AS p LEFT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
+------------------+-----------------+

The project named NewProject is included in this output, even though there is no matching row in the employees table. Because there are no matching employee names for the project named NewProject, the employee name is NULL.

A right outer join lists all employees (regardless of project).

SELECT p.project_name, e.employee_name
  FROM projects AS p RIGHT OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NULL             | NewEmployee     |
+------------------+-----------------+

A full outer join lists all projects and all employees.

SELECT p.project_name, e.employee_name
  FROM projects AS p FULL OUTER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_name, e.employee_name;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Terry Smith     |
| Malaria Vaccine  | Pat Wang        |
| NewProject       | NULL            |
| NULL             | NewEmployee     |
+------------------+-----------------+

Cross join¶

A cross join combines each row in the first table with each row in the second table, creating every possible combination of rows, which is called a Cartesian product. Because most of the result rows contain parts of rows that aren’t actually related, a cross join is rarely useful by itself. In fact, cross joins are usually the result of accidentally omitting the join condition.

The result of a cross join can be very large and expensive. If the first table has N rows and the second table has M rows, then the result is N x M rows. For example, if the first table has 100 rows and the second table has 1000 rows, then the result set contains 100,000 rows.

The following query shows a cross join:

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| COVID-19 Vaccine | Pat Wang        |
| COVID-19 Vaccine | NewEmployee     |
| Malaria Vaccine  | Terry Smith     |
| Malaria Vaccine  | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
| Malaria Vaccine  | NewEmployee     |
| NewProject       | Terry Smith     |
| NewProject       | Maria Inverness |
| NewProject       | Pat Wang        |
| NewProject       | NewEmployee     |
+------------------+-----------------+

You can make the output of a cross join more useful by applying a filter in the WHERE clause:

SELECT p.project_name, e.employee_name
  FROM projects AS p CROSS JOIN employees AS e
  WHERE e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

The result of this cross join and filter is the same as the result of the following inner join:

SELECT p.project_name, e.employee_name
  FROM projects AS p INNER JOIN employees AS e
    ON e.project_ID = p.project_ID
  ORDER BY p.project_ID, e.employee_ID;

+------------------+-----------------+
| PROJECT_NAME     | EMPLOYEE_NAME   |
|------------------+-----------------|
| COVID-19 Vaccine | Terry Smith     |
| COVID-19 Vaccine | Maria Inverness |
| Malaria Vaccine  | Pat Wang        |
+------------------+-----------------+

Natural join¶

A natural join joins two tables on columns that have the same names and compatible data types. Both the employees and the projects table created previously, have a column named project_ID. A natural join implicitly constructs the ON clause: ON projects.project_ID = employees.project_ID.

If two tables have multiple columns in common, then a natural join uses all of the common columns in the constructed ON clause. For example, if two tables each have columns named city and province, then a natural join constructs the following ON clause:

ON table2.city = table1.city AND table2.province = table1.province

The output of a natural join includes only one copy of each of the shared columns. For example, the following query produces a natural join that contains all of columns in the two tables, except that it omits all but one copy of the redundant project_id columns:

SELECT *
  FROM projects NATURAL JOIN employees
  ORDER BY employee_id;

+------------+------------------+-------------+-----------------+
| PROJECT_ID | PROJECT_NAME     | EMPLOYEE_ID | EMPLOYEE_NAME   |
|------------+------------------+-------------+-----------------|
|       1000 | COVID-19 Vaccine |    10000001 | Terry Smith     |
|       1000 | COVID-19 Vaccine |    10000002 | Maria Inverness |
|       1001 | Malaria Vaccine  |    10000003 | Pat Wang        |
+------------+------------------+-------------+-----------------+

You can combine a natural join with an outer join.

You can’t combine a natural join ON clause because the join condition is already implied. However, you can use a WHERE clause to filter the results of a natural join.