SQL › Beyond the Basics

FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN and Self Join

Where we left off

An INNER JOIN keeps only matching rows. LEFT and RIGHT JOIN add the unmatched rows of one side.

This lesson covers the remaining joins: FULL, CROSS, NATURAL and the self join.

Order of execution: FROM + JOINs → WHERE → SELECT → ORDER BY, as for the other joins.

FULL JOIN

A full join is the inner join plus the unmatched left rows plus the unmatched right rows. Each unmatched row has NULLs for the other table's columns.

Either side's key can be NULL, so show the key with COALESCE.

MySQL has no FULL JOIN. There, UNION a LEFT JOIN with a RIGHT JOIN to get the same rows.

Let's imagine finance wants every customer and every wallet side by side, matched where they belong together:

Code
SELECT COALESCE(c.customer_id, a.customer_id) AS customer_id, c.full_name, a.balance
FROM customers c
FULL JOIN accounts a ON a.customer_id = c.customer_id
ORDER BY customer_id;                    -- customers without a wallet get NULLs on the right

Now imagine only wallets over ₹9,000 count as a match, so unmatched rows appear on both sides:

Code
SELECT c.customer_id, a.customer_id AS wallet_owner, a.balance
FROM customers c
FULL JOIN accounts a ON a.customer_id = c.customer_id AND a.balance > 9000
ORDER BY c.customer_id, a.customer_id;

OUTER is optional

The word OUTER changes nothing. In the same way, INNER can be left out of INNER JOIN.

CROSS JOIN

A cross join pairs every left row with every right row, also called a Cartesian product. It needs no join condition.

The result has left rows × right rows. For example, 29 customers and 13 categories make 377 pairs.

A common use is a one-row table whose values every row needs. After other joins, the cross join pairs that row with everything built so far.

Let's imagine the shop prints its own name and city on each Chennai customer's wallet statement:

Code
CREATE TABLE store (store_name VARCHAR(60), store_city VARCHAR(60));
INSERT INTO store VALUES ('ShopKart', 'Bengaluru');
 
SELECT c.full_name, a.balance, s.store_name, s.store_city
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id
CROSS JOIN store s
WHERE c.city = 'Chennai'
ORDER BY c.full_name;                    -- the one store row sits next to every customer

Comma join

Listing tables with commas is an older way to write a cross join. Filter it in WHERE to keep only the pairs that belong together.

Let's imagine you need the wallet numbers of the Patna customers:

Code
SELECT c.full_name, a.account_no
FROM customers c, accounts a
WHERE a.customer_id = c.customer_id AND c.city = 'Patna';

NATURAL JOIN and USING

A natural join has no ON. SQL picks the join columns itself: every column whose name appears in both tables.

If several names are shared, it joins on all of them. If none are shared, it becomes a cross join.

A column renamed or added later silently changes the result, so avoid it. Write ON, or USING to name a shared column yourself; that column then appears once. SQL Server has neither NATURAL JOIN nor USING.

Let's imagine you want the Goa customers' wallet balances, joined on the one column you choose:

Code
SELECT customer_id, full_name, balance
FROM customers
JOIN accounts USING (customer_id)
WHERE city = 'Goa';

Now imagine a natural join between reviews and products, which also share rating and created_at:

Code
SELECT COUNT(*) AS natural_rows FROM reviews NATURAL JOIN products;          -- no review matches
SELECT COUNT(*) AS on_rows
FROM reviews r JOIN products p ON p.product_id = r.product_id;               -- every review finds its product

categories and monthly_targets share no column names at all. For example, their natural join pairs 13 categories with 30 months:

Code
SELECT COUNT(*) AS natural_rows FROM categories NATURAL JOIN monthly_targets;   -- a cross join

Self join

A self join joins a table to itself. Use it when rows point to other rows of the same table. For example, categories.parent_category_id holds another category's id.

There's no SELF JOIN keyword: use any join, and give the table two aliases, one per role.

Let's imagine the catalogue team wants each sub-category of Lifestyle:

Code
SELECT child.category_name AS sub_category, parent.category_name AS belongs_to
FROM categories child
JOIN categories parent ON parent.category_id = child.parent_category_id
WHERE parent.category_name = 'Lifestyle'
ORDER BY sub_category;

Keeping rows without a parent

The top row of a tree has no parent, so an inner self join drops it. A LEFT self join keeps it, with NULL for the parent.

Let's imagine you check categories 9 and 10, where 9 is the top of the tree:

Code
SELECT child.category_id, parent.category_id AS parent_id
FROM categories child
LEFT JOIN categories parent ON parent.category_id = child.parent_category_id
WHERE child.category_id IN (9, 10);          -- 9 has no parent, so parent_id is NULL

Pairs from one table

A self join can also pair rows that share a value. > on the key keeps each pair once and never pairs a row with itself.

Let's imagine the Delhi team wants every pair of Delhi customers for a refer-a-friend offer:

Code
SELECT a.full_name AS customer, b.full_name AS neighbour
FROM customers a
JOIN customers b ON b.city = a.city AND b.customer_id > a.customer_id
WHERE a.city = 'Delhi'
ORDER BY customer, neighbour;            -- each pair once

Remember

• FULL JOIN = inner join + both sides' unmatched rows; use COALESCE for the key (MySQL has no FULL JOIN).
• A CROSS JOIN has no condition and multiplies row counts; 1,000 × 1,000 is a million rows.
• Avoid NATURAL JOIN: it joins on every shared column name. Use ON or USING.
• A self join needs two aliases, one for each role.
• The top of a hierarchy has no parent, so an inner self join drops it.

Practice

1. Toy Offers for Kochi Customers (Easy)
2. Each Category and Its Parent (Easy)
3. Targets vs Actual Sales (Medium)