<aside> 🔗 JOINs combine rows from multiple tables based on a related column between them.

</aside>

Basic JOINs

INNER JOIN — Matching rows only

Returns only rows that have matching values in both tables.

SELECT
    c.id, c.first_name,
    o.order_id, o.sales
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id

LEFT JOIN — All left rows + matches

Returns all rows from the left table, and matched rows from the right. NULL where no match.

SELECT
    c.id, c.first_name,
    o.order_id, o.sales
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id

RIGHT JOIN — All right rows + matches

Returns all rows from the right table, and matched rows from the left.

SELECT
    c.id, c.first_name,
    o.order_id, o.sales
FROM customers AS c
RIGHT JOIN orders AS o
ON c.id = o.customer_id

FULL JOIN — All rows from both tables

Returns all rows from both tables. NULL where there is no match on either side.

SELECT
    c.id, c.first_name,
    o.order_id, o.sales
FROM customers AS c
FULL JOIN orders AS o
ON c.id = o.customer_id

Advanced JOINs (Anti JOINs)

LEFT ANTI JOIN — Customers with NO orders

SELECT *
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL

RIGHT ANTI JOIN — Orders with NO matching customers