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?
Understanding SQL Joins Visual Reference
A join works best when the related columns represent the same kind of identifier. A student ID should be matched with another student ID, not with a student name or a course code. Names can be misspelled, repeated, or changed.
IDs are meant to be stable. In a well-designed database, a primary key identifies one row in its own table. A foreign key stores that identifier in another table.
For example, an Enrollments table may store a student ID and a class ID. Joins use those values to connect enrollment records to student details and class details. This design avoids copying the same name, email address, or class title into many rows.
The number of rows in a result deserves careful attention. A join does not automatically produce one row per person or one row per item. If one customer has three orders, joining Customers to Orders produces three rows for that customer.
If the matching column has duplicates on both sides, the result can grow much faster. Two matching rows on one side and three on the other create six result rows for that shared value. This is normal database behavior, not an error in SQL.
Before writing a query, decide whether each relationship is one to one, one to many, or many to many. Many to many relationships usually need a linking table, such as Enrollments between Students and Classes.
The placement of conditions changes the meaning of a query. The ON part describes how rows relate during the join. A WHERE part filters rows after the joined result has been formed.
This matters most when keeping unmatched rows from one table. Suppose a school wants every student, including students with no club membership. A condition that limits clubs should often stay in the ON part.
If that condition is placed in WHERE, rows containing NULL club values can be removed. The result may then behave like a match-only query. NULL means that a value is missing or unknown.
It is not equal to zero, an empty string, or another NULL. SQL uses IS NULL when checking for it.
Table aliases make longer queries readable and are essential when one table has two roles. An Employees table can contain an employee ID plus a manager ID. One copy of the table represents each employee.
A second copy represents that employee's manager. Aliases give each role a clear name, so the query can show an employee beside their manager. Students see the same pattern in family trees, prerequisite chains, folder structures, and social networks.
When reading a join diagram, trace one sample row at a time. Mark its key value, find every matching row, then note what happens if no match exists.
This slow method catches most mistakes before the query is run. In large real databases, indexes on frequently matched key columns can make joins much faster, though correct results still depend on choosing the right relationship and condition.