Count Records Produced by All Five SQL JOIN Types

SQL coding challenge · Difficulty: medium · +100 XP

Problem

A senior SQL engineer is creating a "SQL JOINs Cheat Sheet" for the team. Write a single query that demonstrates all 5 JOIN types in one result set — each row shows the join type name and the row count it produces.

Tables

Table: TABLE1

| id |
| --- |
| 1 |
| 1 |
| 1 |
| 2 |
| 3 |
| 3 |
| 3 |

Table: TABLE2

| id |
| --- |
| 1 |
| 1 |
| 2 |
| 2 |
| 4 |
| NULL |

Expected Output (example counts)

| join_type | record_count |
| --- | --- |
| INNER JOIN | 8 |
| LEFT JOIN | 11 |
| RIGHT JOIN | 10 |
| FULL JOIN | 13 |
| CROSS JOIN | 42 |

• Return: join_type, row_count (5 rows total)
• Hint: Write 5 separate COUNT queries and UNION ALL them together
• MySQL note: FULL OUTER JOIN is not supported — simulate with LEFT JOIN UNION RIGHT JOIN