What are the different types of SQL JOINs, and when would you use each one?

Question

Accepted Answer

SQL JOINs are used to combine rows from two or more tables based on a related column between them. Understanding the different JOIN types is fundamental for any database administrator because choosing the right JOIN directly affects query correctness and performance.

An INNER JOIN returns only the rows where there is a matching value in both tables. This is the most commonly used JOIN type. For example, if you join a customers table with an orders table on customer_id, the result includes only customers who have placed orders. Customers without orders and orders without valid customers are excluded. Use INNER JOIN when you need data that exists in both tables.

A LEFT JOIN, also called LEFT OUTER JOIN, returns all rows from the left table and the matching rows from the right table. Where there is no match, NULL values fill in the columns from the right table. This is useful when you want to see all records from the primary table regardless of whether related data exists. For example, listing all customers and their orders, including customers who have never placed an order.

A RIGHT JOIN is the mirror of a LEFT JOIN. It returns all rows from the right table and matching rows from the left table. In practice, most developers prefer rewriting RIGHT JOINs as LEFT JOINs by swapping the table order, which tends to be more readable.

A FULL OUTER JOIN returns all rows from both tables, filling in NULLs where there is no match on either side. This is useful for finding mismatches between two datasets, such as identifying orphaned records. For example, finding customers without orders and orders without valid customers in a single query.

A CROSS JOIN produces a Cartesian product, combining every row from the first table with every row from the second table. This is rarely used in day-to-day work but can be helpful for generating test data or producing all possible combinations, such as pairing every product with every store location.

A SELF JOIN joins a table to itself, which is useful for hierarchical data. For example, an employees table where each employee has a manager_id that references another employee_id in the same table. By self-joining, you can list each employee alongside their manager name.

As a junior DBA, you should also know that JOIN performance depends heavily on proper indexing. The columns used in JOIN conditions should typically be indexed. When reviewing slow queries, checking for missing indexes on JOIN columns is one of the first things to investigate.