Joins

Because a normalized database splits data across multiple tables, we need a way to bring it back together when querying. We do this using JOIN operations.

A JOIN combines columns from one or more tables into a new result set based on a related column between them (typically Primary Key / Foreign Key relationships).

INNER JOIN

The INNER JOIN returns rows when there is a match in both tables. If a row in Table A has no corresponding match in Table B, it is excluded from the results.

sql

SELECT 
    users.first_name, 
    orders.total_amount
FROM users
INNER JOIN orders 
    ON users.id = orders.user_id;

(If a user has no orders, they will not appear in this result).

LEFT JOIN (Left Outer Join)

The LEFT JOIN returns all rows from the left table (the one specified before the JOIN), and the matched rows from the right table. If there is no match, the result will contain NULL for the columns from the right table.

sql

SELECT 
    users.first_name, 
    orders.total_amount
FROM users
LEFT JOIN orders 
    ON users.id = orders.user_id;

(This returns all users. If a user hasn't placed an order, orders.total_amount will be NULL).

RIGHT JOIN (Right Outer Join)

The RIGHT JOIN is the opposite of the LEFT JOIN. It returns all rows from the right table, and the matched rows from the left table.

TIP

RIGHT JOIN is rarely used in practice. Most developers prefer to rewrite queries using LEFT JOIN by swapping the order of the tables, as reading left-to-right is generally more intuitive.

FULL OUTER JOIN

The FULL OUTER JOIN returns rows when there is a match in either the left or right table. It is essentially a combination of a LEFT JOIN and a RIGHT JOIN.

sql

SELECT 
    users.first_name, 
    orders.total_amount
FROM users
FULL OUTER JOIN orders 
    ON users.id = orders.user_id;

CROSS JOIN

A CROSS JOIN produces the Cartesian product of the two tables. It pairs every single row in Table A with every single row in Table B. It does not use an ON clause.

sql

SELECT sizes.name, colors.name
FROM sizes
CROSS JOIN colors;

(If you have 3 sizes and 4 colors, this query returns 12 rows).

WARNING

Cross joining large tables can cause a massive Cartesian explosion, resulting in millions or billions of rows and potentially crashing the database. Use with extreme caution.

Joins ​

INNER JOIN ​

LEFT JOIN (Left Outer Join) ​

RIGHT JOIN (Right Outer Join) ​

FULL OUTER JOIN ​