SQL Join Types Explained With Examples

1What Are SQL Joins?

A SQL join combines rows from two or more tables based on a related column, typically a shared key such as a customer ID or order ID. Joins are how relational databases answer questions that span more than one table.

The five main join types are INNER, LEFT, RIGHT, FULL OUTER, and CROSS, and each one differs in exactly which rows survive when a match is not found on one side.

2INNER JOIN: Only Matching Rows

INNER JOIN returns only the rows where the join condition matches in both tables, discarding any row from either table that has no corresponding match on the other side.

It is the default and most commonly used join because most reporting questions only care about records that exist in both tables, such as customers who have actually placed an order.

• SELECT customers.name, orders.order_id
• FROM customers
• INNER JOIN orders ON customers.id = orders.customer_id;
• -- Returns only customers who have at least one matching order.

3LEFT JOIN: Everything From the Left Table

LEFT JOIN, also called LEFT OUTER JOIN, keeps every row from the left table regardless of whether it has a match, filling in null values for any right-table columns when no match exists.

This is the join to use when you want to see all records from one table, plus whatever related data exists, such as listing every customer along with their orders, including customers who have never ordered anything.

• SELECT customers.name, orders.order_id
• FROM customers
• LEFT JOIN orders ON customers.id = orders.customer_id;
• -- Returns all customers; order_id is null for customers with no orders.

4RIGHT JOIN: Everything From the Right Table

RIGHT JOIN works exactly like LEFT JOIN but keeps every row from the right table instead, filling in nulls for any unmatched left-table columns.

In practice, RIGHT JOIN is used less often than LEFT JOIN because most people write the query so the table they want fully preserved is on the left, but the two joins are functionally mirror images of each other.

• SELECT customers.name, orders.order_id
• FROM customers
• RIGHT JOIN orders ON customers.id = orders.customer_id;
• -- Returns all orders; name is null for orders with no matching customer record.

5FULL OUTER JOIN: Everything From Both Tables

FULL OUTER JOIN combines the behavior of LEFT and RIGHT JOIN, keeping every row from both tables and filling in nulls on whichever side has no match.

This is useful for finding mismatches between two datasets, such as identifying customers with no orders and orders with no valid customer record, all in a single query.

• SELECT customers.name, orders.order_id
• FROM customers
• FULL OUTER JOIN orders ON customers.id = orders.customer_id;
• -- Returns all customers and all orders, matched where possible.

6CROSS JOIN: Every Combination of Rows

CROSS JOIN pairs every row in one table with every row in another table, producing a result set whose size is the product of both table sizes, with no matching condition involved at all.

It is used far less often than the other join types, mostly for generating combinations, such as pairing every product with every size option, rather than for typical relational reporting.

• SELECT sizes.size_name, colors.color_name
• FROM sizes
• CROSS JOIN colors;
• -- Returns every possible size and color combination.

7How to Choose the Right Join for a Query

Start by asking which table's rows you must keep in full: if it is only rows present in both tables, use INNER JOIN; if you need every row from one particular table regardless of a match, use LEFT or RIGHT JOIN accordingly.

Reach for FULL OUTER JOIN specifically when you need to audit differences between two datasets, and reserve CROSS JOIN for the rare case where you genuinely want every possible pairing of two sets.

8Practicing SQL Joins

The fastest way to internalize join behavior is to run each join type against the same two small tables and compare the row counts and null patterns directly, rather than memorizing definitions alone.

SkillVeris's SQL for Data Analytics learning path walks through joins alongside real query exercises, and is a natural next step if you are preparing for a data analyst interview that covers database questions.