Join Types

We wish to join two or more tables based on a join condition. In this section, we will cover the most common types of joins, including:

  1. Inner Join: returns only the rows that meet the join condition in both tables.
  2. Left Join: returns all rows from the left table and the rows that meet the join condition from the right table.
  3. Right Join: returns all rows from the right table and the rows that meet the join condition from the left table.
  4. Full Join: returns all rows from both tables, including the rows that do not have matching values in the other table. Any rows that do not have matching values in the other table will be completed with NULL.
  5. Cross Join: returns the Cartesian product of the two tables, which means it returns every possible combination of rows from the two tables.
  6. Multiple Joins: where we cover how to join more than 2 tables and use different join types.

This section is complemented by:

  • Join Columns: Specify the columns we wish to use in the join condition.
  • Join Special Cases: We cover different challenges we might face when performing a join operation.

Inner Join

We wish to join two tables based on the values of a common column and return only rows that meet the join condition and a union of the columns of both tables.

In this example, we wish to return only rows where the values of col_1 are the same in both table_1 and table_2.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 USING (col_1);

Here is how this works:

  • We use the INNER JOIN operation in SQL to combine table_1 and table_2 based on the common column col_1.
  • We pass the common column name to USING clause which performs an equality comparison between the col_1 in table_1 and col_1 in table_2. See Join Columns to learn how to change this behavior.
  • We give each table an alias using AS, in this example table_1 alias is t1 and table_2 alias is t_2. We recommend using table alias always when performing a join operation, so we can easily select the columns we wish to keep from each table.
  • We specify the list of columns we wish to keep in the SELECT statement. See Selecting By Name.
  • The output table will contain the rows that have the same value for col_1 in both table_1 and table_2 and all columns from table_1 and col_4 from table_2.

Left Join

We wish to join two tables based on the values of the common columns and return all rows from the left table and the rows that meet the join condition from the right table and a union of the columns of both tables.

In this example, we wish to return all rows from table_1 and only rows from table_2 where the values of col_1 are the same in both table_1 and table_2.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         LEFT JOIN table_2 AS t2 USING (col_1);

Here is how this works:

  • This works similarly to the Inner Join scenario above except we use LEFT JOIN instead of INNER JOIN.
  • Rows coming from table_1 that don’t match the join condition will have NULL for all columns coming from table_2.
  • The output table will contain all rows from table_1 and rows from table_2 that have the same value for col_1 in both table_1 and table_2. All columns coming from table_2 will have NULL for rows of table_1 that do not match the join condition.

Right Join

We wish to join two tables based on the values of common columns and return all rows from the right table and the rows that meet the join condition from the left table and a union of the columns of both tables.

In this example, we wish to return all rows from table_2 and only rows from table_1 where the values of col_1 are the same in both table_1 and table_2.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         RIGHT JOIN table_2 AS t2 USING (col_1);

Here is how this works:

  • This works similarly to the Left Join scenario above except we use RIGHT JOIN instead of LEFT JOIN.
  • Rows coming from table_2 that don’t match the join condition will have NULL for all columns coming from table_1.
  • The output table will contain all rows from table_2 and rows from table_1 that have the same value for col_1 in both table_1 and table_2 . All columns coming from table_1 will have NULL for rows that do not match the join condition.
  • A right join is just a swapped left join and the only difference is the order of the columns in the output table. If the order of the columns doesn't matter, we recommend to always using a left join as it is easier to understand.

Full Join

We wish to join two tables based on the common columns and return all rows from both tables, including the rows that do not have matching values in the other table. Any rows that do not have matching values in the other table will be filled with NULL.

A full join is useful when we want to combine data from two tables and retrieve all rows where the intersection is shown once plus the difference of both tables

In this example, we wish to return all rows from table_1 and table_2 and combine rows that have matching values for col_1 in both tables.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         FULL JOIN table_2 AS t2 USING (col_1);

Here is how this works:

  • This works similarly to the Inner Join scenario above except we use FULL JOIN instead of INNER JOIN.
  • Rows that don’t match the join condition will have NULL for all columns coming from the other table.
  • The output table will contain all rows from table_1 and table_2 where rows that have the same value for col_1 in both table_1 and table_2 are combined.

Cross Join

We wish to join two tables and return all possible combinations of rows from the two tables. The number of rows in the result table is equal to the number of rows in the first table multiplied by the number of rows in the second table. For example, we might use a cross-join to combine a table of products with a table of prices to create a table of every possible product-price-range combination.

In this example, we wish to return all possible combinations of rows from table_1 and table_2.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         CROSS JOIN table_2 AS t2;

Here is how this works:

  • We use CROSS JOIN operation to generate all possible combinations of rows from the two tables.
  • Unlike other join operations, we don't specify join columns.
  • The output table will contain all possible combinations of rows from table_1 and table_2 and all columns from both tables. The number of rows will be 4 * 4 = 16 and we only keep the columns selected.

Multiple Joins

We wish to join more than two tables using one of the above-mentioned join types.

In this example, we wish to join table_1, table_2, and table_3 based on the common column col_1 as follows :

  • Return rows that match in table_1 and table_2 (inner join).
  • enrich the rows from the inner join of table_1 and table_2 with rows from table_3 that match the join condition (left join)
SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4,
       t3.col_5
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 USING (col_1)
         LEFT JOIN table_3 AS t3 USING (col_1)

Here is how this works:

  • This works similarly to the Inner Join scenario above with an additional join LEFT JOIN operation.
  • The output table will contain all rows that have the same value for col_1 in table_1 and table_2 and col_5 will have NULL values for rows where col_1 doesn't match values in table_1 or table_2 .
SQL
I/O