Skip to content
Expert O'clock

SQL

SQL joins explained for business analysts

How inner and left joins behave, how to choose the right key, and how to stop row multiplication from inflating a report total.

Talha Ansari··5 min read

Key takeaways

  • Choose the join type from the question, not from habit.
  • Check the key and the expected number of matches before joining.
  • A left join filtered in the WHERE clause behaves like an inner join.
  • Compare row counts and totals after every join.
On this page

Start with the grain and the question

A join combines rows from two tables using a shared value. Before choosing a join type, write down what one row of the result should represent. One row per order, one row per customer, or one row per order line? That statement is the grain, and it decides which table must stay intact.

A question such as total revenue by customer needs one row per customer. A question about late deliveries needs one row per order. The same tables can support both, but the joins and aggregation differ. Deciding the grain first prevents the habit of joining everything and fixing totals afterwards.

Inner joins keep only matches

An inner join returns rows that have a match on both sides. Customers without orders disappear, and orders without a valid customer disappear. That is correct when the report concerns matched pairs, such as revenue for products in the catalogue.

It is wrong when missing rows carry meaning. A customer with no orders disappears from a coverage report, and the total looks fine because nothing is flagged. Ask what the unmatched rows represent before accepting an inner join.

Left joins keep the left table intact

A left join keeps every row from the left table and fills columns from the right table with nulls when there is no match. Put the table you cannot afford to lose on the left: for a customer list with order activity, that is customers.

The most common mistake is adding a condition on the right table to the WHERE clause. That condition removes the null rows and turns the left join into an inner join. Put the condition in the ON clause instead if the intent is to keep unmatched rows.

  • ON controls which right-hand rows match; WHERE filters the result after the join.
  • A left join can multiply rows when the right table has several matches per key.
  • Test with a customer who has no orders; the row should still appear.

Check the key and the cardinality

The join key must have the same meaning and type on both sides. Joining a customer code to a similar-looking customer number will produce either no matches or false matches. Check leading zeros, mixed case, and padding before blaming the join.

Cardinality describes how many rows sit on each side. A customer table should have one row per customer; an orders table may have many. Joining one to many is normal; many to many multiplies both sides and inflates sums.

Common join relationships and what they do to the row count
RelationshipExampleEffect on rowsCheck
One to oneCustomer to customer profileRow count stays the sameBoth keys are unique
One to manyCustomer to ordersOne row per matching orderRight key is not unique
Many to oneOrders to customerRow count stays the sameLeft key may repeat
Many to manyOrders to promotionsRows multiply on both sidesPre-aggregate or use a bridge
No matchOrder to missing productInner drops it; left keeps nullsCount unmatched keys

Pre-aggregate before joining detail

If the right table has several rows per key, aggregate it to the required grain first. A customer with three orders would otherwise repeat the customer row three times, and a later sum of a customer-level column would count it three times.

The query below aggregates orders to one row per customer for the current month, then joins that result to the customer list. The left join keeps customers with no orders, and COALESCE turns the missing values into zeros.

Aggregate orders before joining to a customer listSQL
WITH order_totals AS (
  SELECT
    customer_id,
    COUNT(*) AS orders,
    SUM(order_total) AS revenue
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
  GROUP BY customer_id
)
SELECT
  c.customer_id,
  c.customer_name,
  COALESCE(o.orders, 0) AS orders,
  COALESCE(o.revenue, 0) AS revenue
FROM customers AS c
LEFT JOIN order_totals AS o
  ON o.customer_id = c.customer_id
ORDER BY revenue DESC;

Validate the join before you trust the total

Record the row count before and after the join, and count unmatched keys on each side. If the result has more rows than expected, inspect keys with multiple matches; if fewer, find which rows disappeared.

Reconcile a measure as well as the count. Sum the order total before and after the join. A stable row count with a changed total usually means a duplicated key or a misplaced filter.

  • Count distinct keys on both sides before joining.
  • Compare totals, not only row counts.
  • Keep unmatched rows visible in a separate check.

Frequently asked questions

Is a left join always the right choice?+

No. Use an inner join when only matched rows belong in the result, and a left join when unmatched left-hand rows must remain visible. The question decides.

Why do totals double after adding a join?+

The join probably matched more than one row per key. Aggregate the right table to the correct grain first, or check whether the key is unique.

What is a many-to-many join?+

It is a relationship where both tables can have several rows per key. Joining them directly multiplies rows. Use a bridge table or pre-aggregate one side before joining.

Does join order affect the result?+

For inner joins the final rows are the same, though performance can differ. For outer joins, order and filter placement change which rows are kept.

Need help applying this?

Get help with Data Cleanup & Pipelines.

Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.

Discuss this serviceView service
Free KPI definition template

Related reading