SQL Joins Explained

A join is how you combine data that lives in more than one table. Your employees table stores a department_id, not a department name — to produce a report that says "Aarti works in Engineering," you must join employees to departments on that shared column. Joins turn a set of separate tables into answers.

Joins are also the single most tested SQL topic in interviews. If you can explain the four main join types and predict their output on a small dataset, you have cleared the bar most fresher interviews set. This tutorial builds on the reading skills from the SELECT statement in SQL.

The two tables we will join

-- employees
emp_id | emp_name | department_id
-------+----------+---------------
101    | Aarti    | 1
102    | Bhaskar  | 1
103    | Chitra   | 2
104    | Devan    | 2
105    | Esha     | NULL      -- not yet assigned

-- departments
department_id | department_name
--------------+----------------
1             | Engineering
2             | Sales
3             | Marketing     -- has no employees yet

Notice two deliberate gaps: Esha has no department, and Marketing has no employees. These edge cases are exactly what reveal the difference between join types.

INNER JOIN: only the matches

An INNER JOIN returns rows only where the join condition is true in both tables. Employees without a department and departments without employees simply disappear from the result.

SELECT e.emp_name, d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Result:

emp_name | department_name
---------+----------------
Aarti    | Engineering
Bhaskar  | Engineering
Chitra   | Sales
Devan    | Sales

Esha vanishes because her department_id is NULL and matches nothing. Marketing vanishes because no employee points to department 3. The e and d are table aliases — short names that keep the query readable and let you say e.emp_name unambiguously.

Pro tip: Always qualify columns with an alias in a join, like e.department_id and d.department_id. When the same column name exists in both tables, the database cannot guess which one you mean, and it will reject the query as ambiguous.

LEFT JOIN: keep everything on the left

A LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table — employees — even when there is no match on the right. Unmatched right-side columns come back as NULL.

SELECT e.emp_name, d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

Result:

emp_name | department_name
---------+----------------
Aarti    | Engineering
Bhaskar  | Engineering
Chitra   | Sales
Devan    | Sales
Esha     | NULL

Now Esha appears, with a NULL department. This is precisely the join you use for questions like "list all employees, including those not yet assigned to a department." A powerful variation finds the unmatched rows:

-- employees with no department at all
SELECT e.emp_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Interview note: "Find rows in table A with no matching row in table B" is a classic. The pattern is a LEFT JOIN plus WHERE b.key IS NULL. Interviewers use it to check that you understand what LEFT JOIN does with unmatched rows.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is the mirror image: it keeps every row from the right table. If you swap the table order, a right join becomes a left join, which is why most developers stick to LEFT JOIN in practice.

-- every department, even ones with no employees
SELECT e.emp_name, d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

This keeps Marketing (with a NULL employee name) because it lives in the right table. A FULL OUTER JOIN combines both behaviors — it keeps unmatched rows from both sides:

SELECT e.emp_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

Here both Esha (no department) and Marketing (no employees) appear. Note that MySQL does not support FULL OUTER JOIN directly; you emulate it by combining a LEFT and RIGHT join with UNION. PostgreSQL supports it natively — one of the many small differences discussed in MySQL vs PostgreSQL.

A visual summary

Everything else about joins is a variation on this table. Once you internalize which side's unmatched rows survive, you can predict any join's output.

The self join

Sometimes the two tables you join are the same table. Suppose employees also stores a manager_id pointing to another employee. To show each person next to their manager's name, you join employees to itself using two aliases:

SELECT e.emp_name   AS employee,
       m.emp_name   AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.emp_id;

e is the employee, m is the manager, and both are the same physical table. The LEFT JOIN ensures that the top-level boss — who has no manager — still appears with a NULL manager. Self joins feel strange at first but are common for hierarchies and comparisons within one table.

Common mistakes with joins

Forgetting the ON condition. If you join two tables without a join condition, you get a Cartesian product — every row of A paired with every row of B. Five employees and three departments produce fifteen rows of nonsense.

Filtering an outer join in the wrong clause. Putting a right-table condition in WHERE instead of the ON clause can silently turn a LEFT JOIN back into an INNER JOIN by discarding the NULL rows you meant to keep.

Common mistake: Writing WHERE d.department_name = 'Sales' on a LEFT JOIN and wondering why the unmatched NULL rows disappeared. Because NULL is never equal to 'Sales', the WHERE clause filters them out. Put such conditions in the ON clause when you need to preserve unmatched rows.

CROSS JOIN and the accidental Cartesian product

A CROSS JOIN deliberately pairs every row of one table with every row of another, producing a Cartesian product. With five employees and three departments you get fifteen rows:

SELECT e.emp_name, d.department_name
FROM employees e
CROSS JOIN departments d;

Intentional cross joins are rare — generating every combination of sizes and colors for a product catalog is one real use. The danger is the accidental cross join: if you forget the ON condition on a regular join, most databases quietly hand you a Cartesian product instead of an error. A query that suddenly returns thousands of rows is almost always this mistake.

Joining three or more tables

Real schemas rarely stop at two tables. You chain joins by adding another JOIN ... ON for each table. Suppose a projects table links to departments; you can walk employee to department to project in one query:

SELECT e.emp_name,
       d.department_name,
       p.project_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id
INNER JOIN projects p
    ON d.department_id = p.department_id;

The database applies the joins left to right, building up the combined row set one table at a time. Each join needs its own ON condition. As you add tables, aliases become essential for keeping the query readable — imagine writing the full table names three times per column.

Pro tip: When a multi-table join returns wrong or duplicated results, comment out the joins one at a time and re-run. Isolating which join changes the row count is the fastest way to find the faulty condition.

Joins and performance

Joins do real work: the database must match rows across tables. When the join columns are indexed, that matching is fast even on millions of rows; without indexes, the engine may scan entire tables. This is why join keys — foreign keys especially — are almost always indexed in production databases. The topic is covered in depth in SQL indexes explained, but the rule of thumb is simple: the columns you join on should be indexed.

Joins with aggregation

Joins pair naturally with grouping. To count employees per department — including the empty Marketing department — combine a RIGHT JOIN (or LEFT JOIN from departments) with GROUP BY:

SELECT d.department_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e
    ON d.department_id = e.department_id
GROUP BY d.department_name;

COUNT(e.emp_id) counts only non-null employee ids, so Marketing correctly shows zero rather than one. The grouping mechanics here are covered fully in GROUP BY in SQL.

Where to go next

You can now connect tables and predict what each join type returns. That unlocks most real-world reporting queries, since business questions almost always span multiple tables.

Practice the four join types until you can predict their output by hand, then push further with correlated logic in subqueries in SQL. When you are ready to be tested, the SQL joins interview questions drill exactly the scenarios interviewers favor.