JOIN

What is a JOIN and why is it needed?

Data is spread across different tables so as not to be duplicated: an email is stored once in users, not in every transaction. JOIN connects tables by a condition, usually a foreign key equal to a primary key, and lets you get the full picture in one query.

-- transactions with the owner's email
SELECT t.id, t.amount, t.status, u.email
FROM transactions t
JOIN accounts a ON a.id = t.account_id
JOIN users    u ON u.id = a.user_id;

How does INNER JOIN work?

It returns only the rows for which a match was found in both tables. Rows without a match disappear from the result. The word INNER can be omitted: JOIN is inner by default.

A consequence for QA: if an INNER JOIN result "lacks" records, maybe they have no match, and that itself may be a data bug.

-- only users who have at least one account
SELECT u.email, a.currency, a.balance
FROM users u
INNER JOIN accounts a ON a.user_id = u.id;

How does LEFT JOIN work?

It returns all rows from the left table, and from the right only those that found a match. If there is no match, the right table's columns are filled with NULL.

-- all users, even without accounts
SELECT u.email, a.id AS account_id, a.balance
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id;

-- result:
-- anna@test.com | 10 | 500.00
-- ivan@test.com | 11 | 0.00
-- olha@test.com | NULL | NULL     <- no account

What are RIGHT, FULL OUTER and CROSS JOIN?

• RIGHT JOIN is the mirror of LEFT: all rows from the right table. In practice it is almost always replaced with LEFT by swapping the tables, which is more readable.
• FULL OUTER JOIN is all rows from both tables; where there is no match, NULL. Useful for reconciling two data sets. MySQL doesn't have it: it is emulated with a UNION of two LEFT JOINs.
• CROSS JOIN is every row with every row (a Cartesian product). 1000 × 1000 = a million rows. Sometimes useful for generating combinations of test data.

-- reconciliation: our transactions against the provider's statement
SELECT o.id AS our_id, p.external_id AS provider_id
FROM transactions o
FULL OUTER JOIN provider_report p ON p.external_id = o.external_id
WHERE o.id IS NULL OR p.external_id IS NULL;   -- present on one side only

How do you find records that have no match in another table?

The classic technique is an anti-join: a LEFT JOIN and a condition that the right table's key IS NULL. The alternative is NOT EXISTS.

For QA this is one of the most useful queries: "orphaned" records are a frequent sign of a bug.

-- users without a single account
SELECT u.id, u.email
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id
WHERE a.id IS NULL;

-- the same through NOT EXISTS
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM accounts a WHERE a.user_id = u.id);

What is the difference between a condition in ON and in WHERE with a LEFT JOIN?

• A condition in ON affects which rows of the right table are joined. All rows of the left one remain.
• A condition in WHERE filters the ready result. Rows without a match have NULL, and the condition a.status = 'active' gives UNKNOWN for them, so they disappear. The LEFT JOIN quietly turns into an INNER one.

-- all users + their active accounts (if any)
SELECT u.email, a.id
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id AND a.status = 'active';

-- a trap: users without an active account will disappear
SELECT u.email, a.id
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id
WHERE a.status = 'active';

Why did the row count grow after a JOIN and the sum double?

If the join is "one to many", each row on the "one" side is repeated as many times as it has matches. That is normal, but aggregates over columns of the "one" side become wrong after such a JOIN.

Example: a user has 2 accounts and 5 transactions. A JOIN users → accounts → transactions gives 5 rows, and SUM(a.balance) will count each account's balance several times.

How to avoid it: aggregate before the JOIN in a subquery or CTE, or count from the table of the right level.

-- wrong: the account balance is multiplied by the number of its transactions
SELECT u.id, SUM(a.balance), COUNT(t.id)
FROM users u
JOIN accounts a ON a.user_id = u.id
LEFT JOIN transactions t ON t.account_id = a.id
GROUP BY u.id;

-- right: aggregate the transactions separately, then join
WITH tx AS (
  SELECT account_id, COUNT(*) AS tx_cnt FROM transactions GROUP BY account_id
)
SELECT u.id, SUM(a.balance) AS total_balance, COALESCE(SUM(tx.tx_cnt), 0) AS tx_cnt
FROM users u
JOIN accounts a ON a.user_id = u.id
LEFT JOIN tx ON tx.account_id = a.id
GROUP BY u.id;

What is a self join?

Joining a table with itself when rows reference other rows of the same table. The classic is an employee and their manager in one employees table. Different aliases are required.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Types of JOIN and users without orders

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns all rows of the left table and the matches from the right, and where there is no match, NULL. RIGHT JOIN does the same the other way round, FULL JOIN returns all rows from both tables.

CROSS JOIN gives a Cartesian product, every row with every row. SELF JOIN is joining a table with itself, for example an employee and their manager in the same table. The join condition is set with ON.

Users without orders is a classic task for a LEFT JOIN with a NULL check:

SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

The alternative is WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). The NOT IN variant is dangerous: if the subquery has a NULL, it won't return a single row.