SQL Joins - Part 2

RIGHT JOIN, and Why You Rarely See One

RIGHT JOIN is the mirror of LEFT JOIN. It keeps every row of the table named after the JOIN keyword and pads the left side with NULLs wherever nothing matched. On the data from Part 1, a right join from students to orders keeps all four orders — including the guest order whose student_id is NULL — and shows a blank name beside that one.

Everything you learned about LEFT JOIN applies here with the sides swapped, and that includes the bug. A condition on the left table sitting in the WHERE clause of a RIGHT JOIN converts it back into an inner join, for precisely the same reason: the padded rows fail the test and get filtered away.

In practice you will hardly ever write one, and it is worth being explicit about why. Any right join can be rewritten as a left join by swapping the two table names, and left joins read better because the table you care about appears first, at the top of the query where your eye already is. A query that mixes left and right joins forces the reader to keep track of which side is being preserved at each step. Most teams simply standardise on LEFT JOIN and never think about it again.

Recognise it anyway. RIGHT JOIN turns up in generated SQL, in code ported from other tools, and in interview questions, where the answer expected is usually just a clear statement of the difference.

-- All four orders, including the guest order with no student
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
RIGHT JOIN orders o ON s.id = o.student_id;

-- The identical result, written as a LEFT JOIN with the tables swapped
SELECT s.name, o.id AS order_id, o.total_amount
FROM orders o
LEFT JOIN students s ON s.id = o.student_id;

-- The same trap as before, mirrored: this WHERE re-inners the join
-- SELECT s.name, o.id
-- FROM students s
-- RIGHT JOIN orders o ON s.id = o.student_id
-- WHERE s.branch = 'CSE';

FULL OUTER JOIN, and the MySQL Gap

A FULL OUTER JOIN keeps everything: the matched pairs, the unmatched left rows padded on the right, and the unmatched right rows padded on the left. On our sample data it returns Ananya's two orders, Rahul's order, Meera and Karan with empty order columns, and the guest order with an empty student — every row from both tables in a single result.

It earns its place in reconciliation work: comparing two lists that ought to agree and finding what appears in one but not the other. Matching a bank statement against your own payment records is the classic case, and "what is on each side that the other does not have" is exactly one query.

PostgreSQL, SQL Server and Oracle support it directly. MySQL does not, at any version. This is one of the sharper gaps in MySQL's SQL support, and the standard workaround is to UNION a left join with a right join.

Two details about that workaround matter. UNION removes duplicate rows, and that is exactly what makes the two halves combine cleanly — the matched rows are produced by both halves and you want them once. UNION ALL would keep both copies. The deduplication costs a sort, so on large tables this emulation is noticeably slower than a real full outer join. The second detail is that both halves must select the same number of columns, in the same order, with compatible types, and the column names of the first half become the names of the whole result.

There is a faster variant. Use UNION ALL and restrict the second half to the rows the first half could not possibly produce — the ones with no match on the left. Nothing is duplicated, so no deduplication pass is needed at all.

-- PostgreSQL, SQL Server and Oracle support this directly
-- SELECT s.name, o.id AS order_id, o.total_amount
-- FROM students s
-- FULL OUTER JOIN orders o ON s.id = o.student_id;

-- MySQL has no FULL OUTER JOIN. Emulate it with UNION.
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
UNION
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
RIGHT JOIN orders o ON s.id = o.student_id;

-- Faster variant: UNION ALL, with the second half restricted to
-- the rows the first half cannot produce
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
LEFT JOIN orders o ON s.id = o.student_id
UNION ALL
SELECT s.name, o.id AS order_id, o.total_amount
FROM students s
RIGHT JOIN orders o ON s.id = o.student_id
WHERE s.id IS NULL;

CROSS JOIN: Every Row With Every Row

A CROSS JOIN pairs every row of one table with every row of the other. It has no ON clause because there is no condition — the result is a Cartesian product. Four students crossed with three months gives twelve rows, and the count is always simply one table's row count multiplied by the other's.

Most people meet it by accident before they meet it on purpose. Forget the ON clause in an ordinary join, or use the old comma syntax and leave the matching condition out of WHERE, and a cross join is what you get. On practice tables you spot it instantly because the numbers are absurd. On real tables it is a query that never returns: a hundred thousand rows crossed with a hundred thousand rows is ten billion, which will exhaust the server's memory and temporary disk space long before it produces output.

The legitimate use is generating combinations that do not yet exist in the data — and it happens to solve the missing-months problem from the GROUP BY lesson. Cross join a list of branches with a list of months and you have every branch-and-month pair, whether or not any order was placed in it. LEFT JOIN your real figures onto that grid and the empty cells come back as zeros instead of disappearing.

That pattern is worth committing to memory: build the complete grid first, then left join the sparse data onto it. It is how a reporting query produces a row for every month rather than only for the months that happened to be busy.

-- A small lookup table of the months you want reported
CREATE TABLE report_months (month_label CHAR(7) PRIMARY KEY);
INSERT INTO report_months (month_label) VALUES
    ('2026-07'), ('2026-08'), ('2026-09');

-- Every branch paired with every month, data or no data
SELECT b.branch, m.month_label
FROM (SELECT DISTINCT branch FROM students) b
CROSS JOIN report_months m
ORDER BY b.branch, m.month_label;

-- The complete grid with real figures left-joined onto it,
-- so a quiet month shows 0 rather than vanishing
SELECT
    b.branch,
    m.month_label,
    COALESCE(SUM(o.total_amount), 0) AS revenue
FROM (SELECT DISTINCT branch FROM students) b
CROSS JOIN report_months m
LEFT JOIN students s ON s.branch = b.branch
LEFT JOIN orders o
       ON o.student_id = s.id
      AND DATE_FORMAT(o.ordered_at, '%Y-%m') = m.month_label
GROUP BY b.branch, m.month_label
ORDER BY b.branch, m.month_label;

-- Accidental: no ON clause, so every possible pairing is produced
-- SELECT s.name, o.id FROM students s JOIN orders o;

Self Joins: One Table, Joined to Itself

A self join is an ordinary join in which both sides happen to be the same table. Nothing special happens inside the engine. The only real requirement is that you give the two copies different aliases, so that the query has a way to say which copy it means.

The standard example is hierarchy. A single employees table where each row carries a manager_id pointing at another row in the same table can be joined to itself to put the manager's name beside the employee's. Use a LEFT JOIN for this: the person at the top of the tree has a NULL manager, and an inner join would quietly drop exactly the row you most wanted to see.

The other common use is comparing rows within one table — pairs of students in the same branch, products at the same price, duplicate email addresses. A plain self join gives you each pair twice, once in each direction, and additionally pairs every row with itself. Adding a.id < b.id to the join condition keeps exactly one copy of each pair and removes the self-pairings in the same stroke. It is a small trick and it comes up in interviews far more often than you would expect.

Self joins can be chained: join the table to itself twice and you get an employee, their manager, and their manager's manager. That works, but each level costs another join, so for a hierarchy of unknown depth you eventually want a recursive common table expression — WITH RECURSIVE, supported by MySQL 8.0 and later, PostgreSQL, SQL Server and Oracle — rather than a fixed stack of self joins.

CREATE TABLE employees (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

INSERT INTO employees (name, manager_id) VALUES
    ('Sunita Rao',   NULL),   -- id 1, top of the tree
    ('Vikram Joshi', 1),      -- id 2
    ('Neha Gupta',   1),      -- id 3
    ('Aditya Roy',   3),      -- id 4
    ('Simran Kaur',  3);      -- id 5

-- Employee beside their manager; LEFT JOIN keeps the person at the top
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

-- Two levels up: employee, manager, manager's manager
SELECT e.name AS employee, m.name AS manager, mm.name AS skip_level
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees mm ON m.manager_id = mm.id;

-- Pairs of students in the same branch, each pair listed once
SELECT a.name AS student_a, b.name AS student_b, a.branch
FROM students a
JOIN students b ON a.branch = b.branch AND a.id < b.id
ORDER BY a.branch;

-- Finding duplicate email addresses in one table
SELECT a.id AS first_row, b.id AS second_row, a.email
FROM students a
JOIN students b ON a.email = b.email AND a.id < b.id;

Joining Three or More Tables

Structurally, nothing changes when you add a third table. Each JOIN takes the result built so far and attaches the next table to it, so a chain reads from top to bottom: orders, then the items on each order, then the product each item points at.

Read the ON conditions as a path through your schema. o.id = oi.order_id walks from an order down to its items; oi.product_id = p.id walks from an item across to the product. If you can describe that path in words before you start typing, the query nearly writes itself. If you cannot, no amount of SQL syntax is going to rescue it — go back and draw the tables.

For inner joins, the order in which you list the tables does not change the result; the optimiser reorders them anyway based on what it estimates to be cheapest. For outer joins it changes the result completely, and that is the gotcha in this section. A LEFT JOIN followed by an INNER JOIN onto the right-hand table undoes the outer join, because the padded rows have nothing for the inner join to match and get dropped. As a rule: once a chain goes LEFT, everything downstream of it has to stay LEFT.

Formatting stops being cosmetic at this size. Keep the aliases short and meaningful, put each join on its own line, and line up the ON conditions. A five-table join written as one long line is genuinely unreadable; the same join laid out down the page can be followed by someone who has never seen the schema.

-- Three tables: orders -> order_items -> products
SELECT
    o.id         AS order_id,
    o.ordered_at,
    p.name       AS product,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id        = oi.product_id
ORDER BY o.id, p.name;

-- Four tables, adding the student who placed the order
SELECT s.name AS student, o.id AS order_id, p.name AS product, oi.quantity
FROM students    s
JOIN orders      o  ON o.student_id = s.id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products    p  ON p.id         = oi.product_id
WHERE o.status <> 'cancelled'
ORDER BY s.name, o.id;

-- TRAP: the LEFT JOIN is undone by the INNER JOIN that follows it,
-- so students with no orders are dropped after all
-- SELECT s.name, p.name
-- FROM students s
-- LEFT JOIN orders o   ON o.student_id = s.id
-- JOIN order_items oi  ON oi.order_id  = o.id
-- JOIN products p      ON p.id         = oi.product_id;

-- Fixed: once you go LEFT, stay LEFT
SELECT s.name AS student, p.name AS product
FROM students s
LEFT JOIN orders      o  ON o.student_id = s.id
LEFT JOIN order_items oi ON oi.order_id  = o.id
LEFT JOIN products    p  ON p.id         = oi.product_id
ORDER BY s.name;

Choosing a Join, and Making It Fast

With six join types on the table, the choice is usually decided by one question: which rows must survive even when they have no partner? Answer that and the join type follows, as the list below sets out.

Two things dominate join performance and neither is exotic. The first is that the columns you join on should be indexed. A primary key is indexed automatically, but a foreign key generally is not. MySQL's InnoDB is a partial exception — it creates an index for you when you declare a foreign key constraint — while PostgreSQL leaves it to you entirely. If orders.student_id has no index, every join back to students reads the whole orders table.

The second is that the types on both sides of the ON condition should match. Joining an INT to a VARCHAR works, because the database quietly converts one side, but the conversion usually prevents the index from being used and a query that should take milliseconds takes minutes. The same applies to joining two text columns that have different character sets or collations, which is a real problem when two tables were created years apart.

Two syntax shortcuts are worth knowing about, mostly so you can decide not to use them. USING (order_id) replaces ON a.order_id = b.order_id when the column is named identically in both tables; MySQL, PostgreSQL, SQLite and Oracle accept it, SQL Server does not. NATURAL JOIN goes further and joins on every identically named column automatically. That sounds convenient and is a genuine hazard: add a created_at column to both tables next year and the join condition changes underneath a query nobody edited. Write the ON clause out.

• INNER JOIN — you want only the rows that exist on both sides
• LEFT JOIN — you want everything from the main table, matched or not
• RIGHT JOIN — the same thing backwards; prefer swapping the tables and writing LEFT
• FULL OUTER JOIN — reconciling two lists; unavailable in MySQL, emulate with UNION
• CROSS JOIN — deliberately building a complete grid of combinations
• Self join — comparing rows inside one table, or walking a hierarchy

-- Index the foreign keys: without these, every join scans a whole table
CREATE INDEX idx_orders_student_id     ON orders(student_id);
CREATE INDEX idx_order_items_order_id  ON order_items(order_id);

-- Ask the database what it is planning to do before blaming the query
EXPLAIN
SELECT s.name, COUNT(o.id) AS orders
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
GROUP BY s.id, s.name;

-- USING works only when the column has the SAME name in both tables
-- (MySQL, PostgreSQL, SQLite, Oracle; not SQL Server)
SELECT oi.id, sh.shipped_on
FROM order_items oi
JOIN shipments sh USING (order_id);

-- The portable equivalent, which always works
SELECT oi.id, sh.shipped_on
FROM order_items oi
JOIN shipments sh ON sh.order_id = oi.order_id;

-- NATURAL JOIN silently joins on every same-named column. Avoid it.
-- SELECT * FROM order_items NATURAL JOIN shipments;