SQL joins let you combine rows from two or more tables using related columns. This cheat sheet helps students compare join types, predict which rows appear, and choose the correct query pattern. It is useful when working with normalized databases where information is split across multiple tables.
A visual reference makes it easier to understand matches, nonmatches, and table relationships.
Key Facts
- An INNER JOIN returns only rows where the join condition matches in both tables.
- A LEFT JOIN returns all rows from the left table and matching rows from the right table, using NULL when no right-side match exists.
- A RIGHT JOIN returns all rows from the right table and matching rows from the left table, using NULL when no left-side match exists.
- A FULL OUTER JOIN returns all rows from both tables, matching rows where possible and using NULL where no match exists.
- A CROSS JOIN returns every combination of rows from two tables, so the result size is rows in table A times rows in table B.
- A self join joins a table to itself, usually by giving the same table two different aliases.
- A join condition is usually written as ON table1.key = table2.key to connect related rows.
- Filtering unmatched rows after a LEFT JOIN often uses WHERE right_table.key IS NULL to find records with no match.
Vocabulary
- JOIN
- A SQL operation that combines rows from two or more tables based on a related column or condition.
- Primary key
- A column or set of columns that uniquely identifies each row in a table.
- Foreign key
- A column in one table that refers to a primary key in another table.
- INNER JOIN
- A join that keeps only rows with matching values in both joined tables.
- OUTER JOIN
- A join that can keep unmatched rows from one or both tables and fills missing values with NULL.
- Alias
- A temporary name given to a table or column to make a SQL query shorter or clearer.
Common Mistakes to Avoid
- Using INNER JOIN when unmatched rows must be kept is wrong because INNER JOIN removes rows that do not have a match in the other table.
- Putting a right-table filter in WHERE after a LEFT JOIN can be wrong because it may remove NULL rows and make the query act like an INNER JOIN.
- Forgetting the ON condition is wrong because it can create a CROSS JOIN that combines every row from one table with every row from the other.
- Joining on columns with similar names but different meanings is wrong because matching values only help when the columns represent the same relationship.
- Not using aliases in a self join is wrong because the database needs separate table names to know which copy of the table each column comes from.
Practice Questions
- 1 A Students table has 30 rows and a Scores table has 24 matching student_id rows. How many rows are returned by an INNER JOIN on student_id if each matching student has exactly one score?
- 2 A Products table has 12 rows and a Categories table has 4 rows. How many rows are produced by Products CROSS JOIN Categories?
- 3 Write a SQL query pattern that returns all customers and any matching orders using CustomerID as the related column.
- 4 A teacher wants to list every student, including students who have not submitted a project yet. Which join type should be used and why?