Quiz: SQL JOIN Types and UNION with Examples
Study the lesson first: SQL JOIN Types and UNION with Examples
Total: 6 questions
1. What types of relationships exist between tables, and how does the relationship type affect the number of rows a JOIN returns?
What types of relationships exist between tables, and how does the relationship type affect the number of rows a JOIN returns?
There are three types of relationships: one-to-one (1:1) — each row of table A matches at most one row of table B; one-to-many (1:N) — a row of A can match zero, one or several rows of B, while a row of B belongs to only one row of A; many-to-many (M:N) — rows can match several rows on the other side in both directions.
With a one-to-many link, a row of the parent table is repeated in the JOIN result once for every child row it has. For example, the fruits category has two products (apple and pear), so it appears twice in the INNER JOIN result.
2. What is an INNER JOIN, and how is it different from listing the tables in FROM with the join condition in WHERE?
What is an INNER JOIN, and how is it different from listing the tables in FROM with the join condition in WHERE?
An INNER JOIN returns only the rows for which the ON condition is true in both tables; rows without a match do not make it into the result. The word INNER is optional: a plain JOIN is the same INNER JOIN.
SELECT p.name, c.name
FROM products p INNER JOIN categories c ON p.category_id = c.id;
SELECT p.name, c.name
FROM products p, categories c
WHERE p.category_id = c.id;
Both queries return the same 3 rows, and the MySQL optimizer builds the same plan for them. The explicit JOIN ... ON is still better: the join condition sits next to the table and is harder to forget, while a comma-separated query without a condition silently turns into a Cartesian product.
3. What is the difference between LEFT JOIN and RIGHT JOIN, and why is RIGHT JOIN rarely used in practice?
What is the difference between LEFT JOIN and RIGHT JOIN, and why is RIGHT JOIN rarely used in practice?
LEFT JOIN (LEFT OUTER JOIN) returns all rows of the left table and the matching rows of the right one; where there is no match, the columns of the right table are NULL. RIGHT JOIN does the same, but takes all rows from the right table. The word OUTER is optional.
On the lesson tables, products LEFT JOIN categories returns 4 rows, including “sausage — NULL”, and products RIGHT JOIN categories also returns 4, but instead of the sausage the result has “NULL — berries”.
A RIGHT JOIN B is equivalent to B LEFT JOIN A, so most developers write LEFT JOIN only and reorder the tables, which keeps all queries in one style.
4. How do you get a FULL OUTER JOIN result in MySQL?
How do you get a FULL OUTER JOIN result in MySQL?
A FULL OUTER JOIN returns all rows of both tables and fills the missing side with NULL. MySQL (including 8.4 and 9.x) has no such join, so you combine a LEFT JOIN and a RIGHT JOIN with UNION:
SELECT p.name AS product, c.name AS category
FROM products p LEFT JOIN categories c ON p.category_id = c.id
UNION
SELECT p.name, c.name
FROM products p RIGHT JOIN categories c ON p.category_id = c.id;
On the lesson tables this gives 5 rows: the LEFT JOIN returned 4, the RIGHT JOIN returned 4, and UNION kept one copy of each of the three matching pairs. But UNION also removes real duplicates, so the accurate version stacks the queries with UNION ALL and keeps only the unmatched rows in the second one — WHERE p.id IS NULL. It is also faster.
FULL OUTER JOIN in MySQL fails with error 1064, and the short FULL JOIN is more dangerous: FULL is read as a table alias, and the query runs as an INNER JOIN.
5. What is a CROSS JOIN (Cartesian product), and when is it used?
What is a CROSS JOIN (Cartesian product), and when is it used?
A CROSS JOIN is a join without a condition in which every row of the first table is paired with every row of the second: tables with N and M rows give N × M rows. You write it explicitly (FROM products p CROSS JOIN categories c) or by listing the tables with a comma in FROM without a condition. For tables of 4 and 3 rows the result is 12 rows.
Add WHERE p.category_id = c.id and 3 of the 12 rows remain — exactly the INNER JOIN result. A deliberate CROSS JOIN generates combinations (every size × every color, every employee × every day of a schedule), while an accidental one caused by a forgotten condition is a common reason for a query that “hangs”.
In standard SQL a CROSS JOIN is written without ON (PostgreSQL rejects CROSS JOIN ... ON with a syntax error); in MySQL JOIN, INNER JOIN and CROSS JOIN are interchangeable.
6. What is the difference between UNION and UNION ALL, and what rules must hold to combine queries with them?
What is the difference between UNION and UNION ALL, and what rules must hold to combine queries with them?
UNION combines the results of several SELECT queries into one set and removes duplicate rows. UNION ALL keeps every row, duplicates included, and runs faster because it does not have to look for duplicates.
Rules:
- every SELECT returns the same number of columns, otherwise ERROR 1222 (21000);
- the column types in the same positions are compatible (MySQL converts them to a common type, PostgreSQL raises an error);
- the result column names come from the first SELECT, and one ORDER BY goes at the very end and sorts the whole result.
Unlike a JOIN, which adds the columns of another table to a row, UNION stacks the rows of one query under the rows of another.