SQL JOINs Explained: INNER, LEFT, RIGHT, and FULL with Practical Examples

The Setup: Two Example Tables

Throughout this guide, we will use two simple tables. The Employees table has columns: emp_id, name, and dept_id. The Departments table has columns: dept_id and dept_name.

Employees contains: (1, 'Alice', 10), (2, 'Bob', 20), (3, 'Charlie', 30), (4, 'Diana', NULL). Departments contains: (10, 'Engineering'), (20, 'Marketing'), (40, 'Finance').

Notice intentionally: Diana has no department (NULL dept_id), Charlie references dept_id 30 which does not exist in Departments, and Finance (dept_id 40) has no employees. These edge cases illustrate exactly how different JOINs behave.

INNER JOIN: Only Matching Rows

INNER JOIN returns only rows where the join condition is satisfied in both tables. It is the most common JOIN type and the default when you simply write JOIN without a qualifier.

Query: SELECT e.name, d.dept_name FROM Employees e INNER JOIN Departments d ON e.dept_id = d.dept_id;

Result: Alice-Engineering, Bob-Marketing. Only two rows are returned because: Charlie's dept_id (30) has no match in Departments, Diana's dept_id is NULL (NULL never equals anything, not even another NULL), and Finance (dept_id 40) has no matching employee.

Use INNER JOIN when you only care about records that have valid relationships in both tables. It is the safest default — you will never get unexpected NULLs in your result set from the join itself.

LEFT JOIN (LEFT OUTER JOIN): All Left, Matching Right

LEFT JOIN returns all rows from the left table (the one after FROM), plus matching rows from the right table. If a left-table row has no match, the right-table columns are filled with NULL.

Query: SELECT e.name, d.dept_name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Result: Alice-Engineering, Bob-Marketing, Charlie-NULL, Diana-NULL. All four employees appear regardless of whether their department exists. Charlie and Diana have NULL for dept_name because their dept_id has no match.

Use LEFT JOIN when you need all records from your primary table and want to optionally enrich them with data from a related table. Common examples: showing all customers (even those with no orders), all products (even those never purchased), or all employees (even those not yet assigned to a department).

A critical interview question: 'Find employees without a department.' The pattern is: SELECT e.name FROM Employees e LEFT JOIN Departments d ON e.dept_id = d.dept_id WHERE d.dept_id IS NULL; This returns Charlie and Diana — the WHERE clause filters to only the unmatched rows.

RIGHT JOIN (RIGHT OUTER JOIN): All Right, Matching Left

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, plus matching rows from the left table. Unmatched right-table rows have NULL for left-table columns.

Query: SELECT e.name, d.dept_name FROM Employees e RIGHT JOIN Departments d ON e.dept_id = d.dept_id;

Result: Alice-Engineering, Bob-Marketing, NULL-Finance. The Finance department appears even though no employee is assigned to it. Charlie and Diana are excluded because their dept_ids do not match any department.

In practice, RIGHT JOIN is rarely used because you can always rewrite it as a LEFT JOIN by swapping the table order. Most style guides prefer LEFT JOIN for readability. However, you should still understand RIGHT JOIN for assessment questions that specifically test it.

FULL OUTER JOIN: All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables. Where a match exists, columns are populated from both sides. Where no match exists, the missing side gets NULL values.

Query: SELECT e.name, d.dept_name FROM Employees e FULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;

Result: Alice-Engineering, Bob-Marketing, Charlie-NULL, Diana-NULL, NULL-Finance. Every record from both tables appears. This is the union of LEFT JOIN and RIGHT JOIN results.

FULL OUTER JOIN is less common in production queries but useful for data reconciliation — finding orphaned records on either side of a relationship. Note that MySQL does not natively support FULL OUTER JOIN; you must simulate it with UNION of LEFT JOIN and RIGHT JOIN.

CROSS JOIN: Cartesian Product

CROSS JOIN produces every possible combination of rows from both tables — the Cartesian product. If table A has 4 rows and table B has 3 rows, the result has 12 rows.

This is rarely useful in production but appears in assessments. Practical uses include generating all combinations (e.g., all products in all store locations) or creating date dimensions in reporting.

Be careful: accidentally omitting a JOIN condition produces a cross join, which can generate millions of rows from moderately-sized tables and is a common query performance bug.

Common JOIN Mistakes in Assessments

Mistake 1: Confusing which table is 'left' and 'right.' The left table is always the one mentioned first (after FROM or before the JOIN keyword). Table order matters for LEFT/RIGHT JOIN but not for INNER JOIN.

Mistake 2: Assuming NULL matches NULL. In SQL, NULL = NULL evaluates to UNKNOWN, not TRUE. JOINs never match NULL to NULL. This is why Diana (dept_id NULL) never appears in INNER JOIN results even if Departments had a NULL dept_id row.

Mistake 3: Using WHERE instead of ON for outer join conditions. Placing a filter in WHERE converts an outer join to an inner join because it eliminates the NULL rows. If you want to filter the right table but keep all left rows, put the condition in the ON clause.

Mistake 4: Not considering duplicate matches. If one department has multiple employees, a JOIN produces one row per employee-department pair. This is correct behavior, not a bug, but candidates sometimes expect a single row per department.

Performance Considerations

For large tables, JOIN performance depends heavily on indexes. Always index the columns used in JOIN conditions (the ON clause). Without indexes, the database must perform a full table scan for each row — O(n*m) complexity.

INNER JOINs are generally faster than OUTER JOINs because the optimizer has more freedom to reorder tables. With INNER JOIN, table order does not affect the result, so the optimizer can start with the smaller table. With OUTER JOIN, the table order is fixed.

When joining multiple tables, the join order matters for performance. Most modern optimizers handle this automatically, but for complex queries with 5+ tables, you may need to provide hints or restructure the query.

For analytical queries that join large fact tables to dimension tables, consider whether you actually need all columns from both tables. Selecting only the needed columns reduces I/O and can enable covering index usage.

Frequently Asked Questions