Start with two tables and a matching key
A SQL join combines rows from two tables according to a condition. The most common condition matches a primary key in one table to a foreign key in another. Consider a customers table with three rows: (1, Ava), (2, Bo), and (3, Cy), where the first value is customer_id and the second is name. An orders table has four rows: (101, 1), (102, 1), (103, 2), and (104, NULL), where the values are order_id and customer_id. Order 104 has not been assigned to a customer.
The relationship is one-to-many: Ava has two orders, Bo has one, and Cy has none. A join does not automatically produce one row per customer. It produces a row for each pair that satisfies the join condition. We will use customers.customer_id = orders.customer_id throughout. A NULL customer_id does not satisfy that equality, even though the column exists in both tables.
The examples use short table aliases: c for customers and o for orders. Qualifying column names, as in c.customer_id, makes queries easier to read and avoids ambiguity when both tables contain a column with the same name.
INNER JOIN: keep only matching pairs
An INNER JOIN returns rows that match on both sides. Here is a query that lists customers alongside their orders: SELECT c.name, o.order_id FROM customers AS c INNER JOIN orders AS o ON c.customer_id = o.customer_id;
The result contains three rows: (Ava, 101), (Ava, 102), and (Bo, 103). Ava appears twice because two order rows match her customer ID. Cy is absent because there is no matching order, and order 104 is absent because its customer_id is NULL.
Use an inner join when the question requires a match, such as “Which customers have placed an order?” If you need each matching customer only once, select customer columns with DISTINCT, or group the rows. Do not assume that an inner join removes duplicates: repeated output may reflect valid one-to-many relationships.
LEFT JOIN: retain every row from the left table
A LEFT JOIN, also called a LEFT OUTER JOIN, keeps every row from the table named before the join. It attaches matching rows from the right table and fills right-table columns with NULL when no match exists: SELECT c.name, o.order_id FROM customers AS c LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
This query returns four rows: (Ava, 101), (Ava, 102), (Bo, 103), and (Cy, NULL). The NULL beside Cy means the join found no order for that customer. It does not represent an order whose ID is NULL. Order 104 remains absent because a left join preserves customers, not every order.
A left join is useful for reporting on all customers, including those with no orders. To count orders per customer, group by the customer's ID and name and use COUNT(o.order_id). COUNT(o.order_id) returns zero for Cy because it ignores NULL. COUNT(*) would return one for Cy: the left join still creates a row for her.
RIGHT JOIN and FULL OUTER JOIN: change which rows survive
A RIGHT JOIN preserves every row from the table after the join. Using customers on the left and orders on the right, it returns the three matched orders plus (NULL, 104) when selecting c.name and o.order_id. The NULL name shows that order 104 has no matching customer. You can often express the same request more naturally by swapping the table order and writing orders LEFT JOIN customers.
A FULL OUTER JOIN preserves rows from both tables. With this example, it returns five rows: the three matched pairs, (Cy, NULL), and (NULL, 104). This is useful when comparing two sets and identifying records that appear on only one side. SQL database products differ in join support; for example, some do not offer FULL OUTER JOIN directly, so check your database's syntax before using it.
For an outer join, “left” and “right” refer to table positions in the query, not to which table is more important. Before writing one, ask which table's unmatched rows must remain visible.
Avoid filter and NULL mistakes
A common trap is filtering the right-hand table in the WHERE clause of a LEFT JOIN. For example, WHERE o.order_id > 101 removes Cy's row because NULL does not pass that test. If the goal is to keep every customer while attaching only qualifying orders, put the condition in the ON clause instead: ON c.customer_id = o.customer_id AND o.order_id > 101. Cy will then remain in the result with a NULL order ID.
To find customers with no orders, use a left join followed by WHERE o.order_id IS NULL, assuming order_id cannot be NULL for a real order. This returns Cy in the example. To find unassigned orders, start with orders and left join customers, then test WHERE c.customer_id IS NULL; that returns order 104.
Finally, inspect the keys and expected row counts before trusting a join's output. A missing or overly broad ON condition can multiply rows dramatically, while a restrictive condition can silently exclude useful records. When performance matters, indexes on the joined keys may help, but the best approach depends on table sizes, filters, and the database's query plan.
