What are SQL joins, and how do inner, left, right and full joins differ?

In this article

1. Before you start
2. Why interviewers ask this
3. SQL joins from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page belongs to the SQL interview questions guide. Sorting join results is covered in SQL order by. Joins work on relational databases, and conditional expressions (called case expressions), which label or bucket rows, often follow a join in the same query, as in conditional statements.

Why interviewers ask this

A wrong join gives a plausible-looking result with the wrong rows, and nothing warns you. That is why interviewers use joins to test whether a candidate can reason about rows rather than recite syntax. SQL interview questions on joins, reported by candidates, often ask for the customers with no orders, the difference between inner and left, or why a count grew after a join.

A weak answer draws two overlapping circles and stops. A strong answer says that a join pairs rows by a condition, names what happens to unmatched rows, and notices that repeated keys multiply rows. A Staff-level answer also checks the grain of each table (what one row means) before joining, and knows where a filter belongs.

SQL joins from first principles

Join

A join is a query operation that builds a result table from two source tables by pairing rows according to a rule, and then placing the columns of each paired row side by side. It is how a relational database reads data that is stored in separate tables.

Join condition

A join condition is a boolean expression, usually a comparison of one column from each table, that decides which pairs of rows count as a match. Only pairs for which the condition is true are treated as matched, and pairs where it is false or unknown, as with a NULL key, are not.

In plain words

Think of two paper lists: staff with a department number, and departments with a name. To label each person you take a person, find the department with the same number, and write the name beside them. A join does this for every row. The analogy breaks in one place: a person may match several rows, or none, and the database must decide what to do. That decision is the join type.

In one precise sentence, a join takes two tables and a condition and forms pairs of rows. It keeps the pairs where the condition is true and applies the join type's rule to rows left without a partner. The PostgreSQL tutorial defines join queries as queries that combine rows from one table with rows from a second table, with an expression saying which rows are paired. The examples below use the sample tables of the Wikipedia article on joins: six employees and four departments.¹ One employee, Williams, has a NULL department. One department, Marketing, has no employees.

Step 1: An inner join keeps only the matching pairs

An inner join is a join that returns only the pairs of rows for which the join condition is true. Input: two tables and a condition. Output: one row per matching pair.

SELECT e.last_name, d.department_name
FROM employee e
INNER JOIN department d ON e.department_id = d.department_id;

On the sample tables this returns 5 rows.¹ Williams is missing, because a NULL key never satisfies an equality comparison, and Marketing is missing, because no employee points to it. The PostgreSQL tutorial shows the same effect: its inner join returns no row for Hayward, because that city has no match, so unmatched rows are ignored. The word inner is optional, and per the PostgreSQL documentation on joined tables it is the default. You now know the default: matched pairs only.

Step 2: A left join keeps every row of one side

A left join, called a left outer join in full, returns every row of the left table at least once; where no right row matches, the right columns are filled with NULL. The right table contributes only rows that match some left row.

Input: two tables, with one side marked to be preserved. Output: all of its rows. With the employee table on the left, the sample query returns 6 rows: Williams appears with a NULL department name.¹ A right join is the mirror image; it returns 6 rows, five matches plus Marketing with a NULL employee.¹ A full join keeps both sides and returns 7 rows.¹ The words left, right and full imply an outer join, so the word outer can be dropped. Note "at least once": a left row with several matches appears several times.

Step 3: A right-table filter in WHERE can turn a left join into an inner join

A condition on a right-table column in the where clause that cannot be true for NULL (an equality, a comparison, like, in) removes the NULL-filled rows the left join created, so the result behaves like an inner join. The exception is is null, which is how you find unmatched rows. Input: a left join plus such a condition on the right table. Output: only rows where the condition is true.

-- filter in WHERE: the NULL rows fail the test and vanish
SELECT e.last_name, d.department_name
FROM employee e
LEFT JOIN department d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

-- filter in ON: every employee stays, only Engineering is attached
SELECT e.last_name, d.department_name
FROM employee e
LEFT JOIN department d
  ON e.department_id = d.department_id
 AND d.department_name = 'Engineering';

The first query returns 2 rows, Jones and Heisenberg. The second returns 6 rows, with NULL for everyone outside Engineering.¹ The PostgreSQL documentation explains why: the join condition of an outer join is not equivalent to a where condition, because the join condition can add rows for unmatched input rows, while the where condition can only remove rows. Put a condition that decides which rows match in the join condition; put a condition that removes finished rows in the where clause.

Step 4: A cross join pairs every row with every row

A cross join is a join with no condition that returns every possible pair of rows, so its row count is the product of the two table sizes. For the sample tables that is 6 times 4, or 24 rows.¹ The PostgreSQL documentation states that a cross join of two tables is equivalent to an inner join on TRUE, and also to listing the two tables with a comma in the from list. With three or more tables the comma form is not exactly equivalent, because the join keyword binds more tightly than a comma. In MySQL, per the MySQL 8.4 manual, the cross and inner join keywords can replace each other, while in standard SQL they are not equivalent.

1 · Matched pairs2 · Unmatched rows3 · Filter in WHERE4 · Cross joinReplay

Step-by-step description

1. Five pairs match: an inner join returns 5 rows.
2. Williams and Marketing: left 6, right 6, full 7.
3. Only Engineering stays: 2 rows.
4. Every pair: 6 x 4 = 24 rows.

• An inner join returns 5 rows.
• Williams has a NULL department and Marketing has no employees.
• The first query returns 2 rows, Jones and Heisenberg.
• A cross join is 6 times 4, or 24 rows.

Worked example

The counts below were computed on the six-employee, four-department sample.¹

Why does a join return more rows than the table I started with?

Repeated keys multiply rows. If a key appears 3 times on one side and 2 times on the other, the join returns 3 times 2, or 6 rows, for that key. In an illustrative pair of four-row tables, with left keys 1, 1, 1, 2 and right keys 1, 1, 2, 3, an inner join returns 7 rows, a left join also 7, and a full join 8.¹ This is the reason a count or a total changes after a join, as in how a conditional total works in SQL.

Text description of the diagram

• An inner join of the two four-row tables returns 7 rows.
• A full join returns 8 rows.

SQL joins practice: four exercises

Try each before reading the answers below the list.

1. List employees with no department.
2. List departments with no employees.
3. Show every employee and every department, matched where possible (PostgreSQL; in MySQL use the union form).
4. Find pairs of employees in the same department.

Answers:

1. Left join from employee to department and keep rows where the department key on the right is NULL: 1 row, Williams.¹
2. Left join from department to employee and keep rows where the employee key is NULL: 1 row, Marketing.¹
3. A full join on the department key: 7 rows.¹
4. A self join, which is a join of a table with itself and needs aliases such as e1 and e2. With the department condition and e1.last_name < e2.last_name it returns 2 pairs.¹ The PostgreSQL tutorial introduces this form.

SELECT e1.last_name, e2.last_name
FROM employee e1
JOIN employee e2
  ON e1.department_id = e2.department_id
 AND e1.last_name < e2.last_name;

Trade-offs and edge cases

The join type is a choice about which rows to lose. The table below summarises it.

Text description of the diagram

• An inner join drops unmatched rows.
• A full join keeps unmatched rows from both sides.
• A cross join pairs every row with every row.

Edge cases that cost points:

• NULL keys. A NULL key never matches, not even another NULL, so such rows drop out of an inner join. Williams is the example above.
• Joins nest left to right. Per the PostgreSQL documentation, in the absence of parentheses, join clauses nest left to right. Left joins are not associative, so parentheses can change the result, as the MySQL manual's nested-join discussion shows.
• Several columns. Join on multiple columns by combining conditions: a.x = b.x AND a.y = b.y in the join condition, or the shorthand USING (x, y). A natural join is a shorthand that uses every column name the two tables share; it silently changes meaning when a column is added, so avoid it in production code.
• Dialects differ. MySQL (8.4) has no full outer join. Its join grammar lists only left [outer] and right [outer], so a full join is written as a left join union the right join's unmatched rows.
• Engines do not compare every pair. The "pair every row with every row" picture is, in the PostgreSQL documentation's words, a conceptual model; real engines use more efficient join algorithms.

How to answer it in an interview

The spoken structure: define the join, name what each type does to unmatched rows, then give the two traps.

Common mistakes

• "A join is two overlapping circles." (wrong) Joins pair rows and place their columns side by side; they are not set operations, and the picture fails for cross joins and repeated keys. (right) "A join pairs rows by a condition; unmatched rows are kept or dropped by the join type."
• "An inner join returns all the rows." (wrong) Unmatched rows are silently dropped. (right) "An inner join returns only the matching pairs."
• "A left join returns one row per left row." (wrong) A left row with several matches appears several times. (right) "A left join returns each left row at least once."
• "I must write the words inner and outer." (wrong) Both words are optional, and inner is the default. (right) "A plain join means inner; a left join means left outer."
• "I compare a right-table column in the where clause after a left join." (wrong) A condition that is false for NULL removes the unmatched rows; only is null keeps them. (right) "I put the match filter in the join condition."

Follow-up questions

What is the difference between an inner join and a left join?

An inner join returns only the pairs that satisfy the condition. A left join also returns every unmatched row of the left table, with NULL in the right columns. On the sample tables that is 5 rows against 6.¹

How do you join on multiple columns?

Combine the conditions with AND in the join condition, or list the shared column names in USING (x, y). Both forms appear in the grammar of the PostgreSQL and MySQL documentation.

Why is the Venn diagram of SQL joins misleading?

It treats tables as sets of items, but a join pairs rows and can return one row several times. Repeated keys give 7 rows from two four-row tables, which no overlap picture shows.¹ The cross join has no overlap at all.

How do you find rows with no match?

Use a left join and keep the rows where a right-table column that cannot be NULL, such as its key, is NULL. This returns Williams in the sample.¹ The same result can come from not exists. Avoid not in here: if the subquery returns a NULL, not in yields null rather than true, so no rows come back, while not exists behaves as expected, as the PostgreSQL subquery documentation describes.

How does a database actually execute a join?

The PostgreSQL planner documentation lists three join methods: nested loop, merge and hash. A nested loop checks each row of one side against the other and suits a small side or an indexed key, a hash join probes a hash table built from one side and suits large unsorted equality joins, and a merge join walks two inputs already sorted on the key. More questions are in the SQL guide.

Sources

1. PostgreSQL Documentation: 2.6 Joins Between Tables — PostgreSQL Global Development Group, 26 Oct 2026
2. PostgreSQL Documentation: 7.2 Table Expressions, Joined Tables — PostgreSQL Global Development Group, 26 Oct 2026
3. MySQL 8.4 Reference Manual: JOIN Clause — Oracle, 26 Oct 2026
4. MySQL 8.4 Reference Manual: Nested Join Optimization — Oracle, 26 Oct 2026
5. PostgreSQL Documentation: Planner/Optimizer — PostgreSQL Global Development Group, 26 Oct 2026
6. PostgreSQL Documentation: Subquery Expressions — PostgreSQL Global Development Group, 26 Oct 2026
7. Join (SQL) — Wikipedia, 26 Oct 2026