Joins: Inner, Left, Right, and Full
Explore the four main types of SQL joins — inner, left, right, and full outer joins — to understand how they combine data from multiple tables. Learn to predict which rows survive each join type and how to preserve unmatched records when required. The lesson teaches proper use of join keys, filtering within ON versus WHERE clauses, and methods to handle missing data with functions like COALESCE. You'll gain practical skills to create accurate and complete reports, avoiding common pitfalls like losing unmatched rows that can skew results.
A customer lifetime value report can look complete while being wrong. If customers contains four customers but an INNER JOIN returns only the three customers with orders, the report may claim every customer has purchased. The missing customer should have customer lifetime value (CLV) of $0, not no row at all.
To answer “What is CLV by customer, including customers who have never purchased?” we must combine customers and orders. A join combines rows from two tables when values match. The join key is the matching rule, here customers.customer_id = orders.customer_id. The join type decides what happens when a customer has no matching order or an order has no matching customer.
Predicting which rows survive
Before writing an aggregate, we need to predict the rows the join creates. In the small example in the diagram below, with three customer rows and two matching order rows, an INNER JOIN keeps only the two matched customer-order pairs. Customer 3, who has no order, disappears.
A LEFT JOIN preserves every row from the table on the left side of LEFT JOIN. When customer 3 has no matching order, the query returns customer 3 with NULL in the order columns. NULL means the value is missing or unknown. It does not mean zero.
This diagram shows the row-keeping rule directly. An INNER JOIN returns two rows because two customers match orders. A LEFT JOIN returns three rows because it keeps all three customer rows, including the unmatched customer.
Row check: Before running a join, name the preserved table and predict whether unmatched rows should remain. If every customer must appear,...