Queries and joins

A query and its result

Our shop has customers, products, orders and order lines. A line is one entry within an order, identified by its order ID and line number. Customer C4, Ada, placed O12 for two Blue mugs at €18 each. Her later order O13 has a mug at €20 and a bowl at €24. The line’s price records the amount agreed for that purchase; the product has a separate current catalogue price.

To show the amounts on O13, we can read its lines directly:

SELECT order_id, line_no, quantity,
       quantity * unit_price AS line_total
FROM order_lines
WHERE order_id = 'O13'
ORDER BY line_no;

FROM names the table. WHERE keeps rows whose condition is true: here, rows whose order_id equals the text value 'O13'. Single quotes delimit text values; the column names are unquoted. SELECT chooses the columns and expressions returned. Multiplying quantity by unit price calculates an amount, and AS line_total gives that result column a name.

ORDER BY line_no puts line 1 before line 2. Without an explicit ordering, SQL does not promise which row arrives first. The final semicolon ends the statement. These clauses describe the answer; they do not prescribe an algorithm that must visit every row in the order the clauses are written.

The fixed value 'O13' makes the example easy to run. In application code, a PostgreSQL parameterised query can instead use WHERE order_id = $1, with the order ID supplied separately through the database driver. Do not concatenate a caller’s value into the SQL text. The driver’s API varies, but the separation keeps a value from becoming part of the command.

This query leaves the stored records alone. It does not create a line_total column on the table. Calculating the expression again on a later read uses the values visible to that read.

To run the SQL yourself, start the shared practice setup. It explains opening PostgreSQL, loading the files and choosing a fresh database for this article.

INSERT INTO customers (customer_id, name)
VALUES ('C5', 'Ben');

-- An order header exists before its lines have been added.
INSERT INTO orders (order_id, customer_id, placed_at)
VALUES ('O14', 'C4', '2026-10-04 11:00:00+00');

CREATE TABLE shipments (
  shipment_id text PRIMARY KEY,
  order_id text NOT NULL REFERENCES orders (order_id)
);
INSERT INTO shipments (shipment_id, order_id)
VALUES ('S1', 'O13'), ('S2', 'O13');

Joins and matching rows

The lines contain product IDs, but a customer needs names. A join combines rows whose values satisfy a condition. We match each line’s product ID to the product with that ID:

SELECT l.order_id, l.line_no, p.name,
       l.quantity, l.quantity * l.unit_price AS line_total
FROM order_lines AS l
JOIN products AS p ON p.product_id = l.product_id
ORDER BY l.order_id, l.line_no;

AS l gives order_lines a short name within this query; AS p does the same for products. The expression p.name means the name from the product side. ON states which pairs match. Plain JOIN means inner join: only matching pairs become result rows.

Line 1 of O12 contains P7. P7 identifies one product, so the line pairs with one Blue mug record. O13’s first line also pairs with P7. The product appears twice in the answer because it was bought on two different lines. Neither the shared product nor either purchase needs to be copied in storage.

The primary key on products.product_id makes the match unique. The required foreign key on the line makes a match exist. Together they explain why this particular join returns exactly one row per line. A join is not inherently one-to-one: its condition and the data’s rules determine how many matches there can be.

It helps to name what identifies a result row. Here it is (order_id, line_no), just as for the source lines. Some people call this the result’s grain. If we join from an order to its lines, the order ID repeats as the result becomes one row per line. Removing the line number from SELECT hides that distinction; it does not remove the result rows.

Missing matches

Now we want a customer list with their order IDs, including customers who have never ordered. Our added customer C5, Ben, has no orders. An inner join would omit him because there is no matching pair. A left join retains every row on its left, supplying SQL NULL for the right-hand fields when nothing matches.

SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Ben’s result does not describe an order whose ID is missing. It describes a customer with no matching order. O14 is different: it is a real order header belonging to Ada, though it has no lines yet. The join cannot infer that an order is complete merely because its row exists.

NULL matters when we filter. Ordinary comparisons with a missing value have an unknown result. WHERE keeps only true, so both false and unknown are excluded. To ask whether a value is missing, use IS NULL, rather than = NULL. For example, WHERE o.order_id IS NULL after this join finds Ben. It works as a missing-match test because real order IDs cannot be null.

Ada’s orders were placed at 09:00 (O12), 10:00 (O13) and 11:00 (O14), all on 4 October 2026 in UTC. Suppose the customer list should show only orders placed at or after 10:00 that day, but still retain customers with no such orders. There are two ways a customer can have no qualifying order: Ben has never ordered, while another customer might have ordered only earlier.

For this comparison, we’ll make separate copies called filter_customers and filter_orders, and add C6, Cara, whose only order is O15 at 08:00. These copies let us explore that case without changing the shop records used elsewhere in the article.

CREATE TEMP TABLE filter_customers AS
SELECT * FROM customers;
CREATE TEMP TABLE filter_orders AS
SELECT * FROM orders;

INSERT INTO filter_customers (customer_id, name)
VALUES ('C6', 'Cara');
INSERT INTO filter_orders (order_id, customer_id, placed_at)
VALUES ('O15', 'C6', '2026-10-04 08:00:00+00');

First put the cutoff in the matching rule:

SELECT c.customer_id, c.name, o.order_id
FROM filter_customers AS c
LEFT JOIN filter_orders AS o
  ON o.customer_id = c.customer_id
 AND o.placed_at >= '2026-10-04 10:00:00+00'
ORDER BY c.customer_id, o.order_id;

Ada now has O13 and O14. The AND requires both the customer match and the time condition. Ben has no orders to match, and Cara’s O15 fails the time condition. The left join supplies each of them with one row containing null order fields.

Moving the same condition to WHERE asks a different question:

SELECT c.customer_id, c.name, o.order_id
FROM filter_customers AS c
LEFT JOIN filter_orders AS o ON o.customer_id = c.customer_id
WHERE o.placed_at >= '2026-10-04 10:00:00+00'
ORDER BY c.customer_id, o.order_id;

This drops both Ben and Cara. Ben’s missing placement time makes the comparison unknown. Cara first matches O15, but its 08:00 placement time makes the comparison false; filtering out that row does not create a replacement null row. The distinction is between deciding which orders match and deciding which resulting rows survive. Choose it from what the customer list is meant to include.

Grouping and totals

A receipt needs individual lines; an order summary needs one total per order. An aggregate combines several input values into one answer. SUM adds them, and COUNT counts rows or present values. GROUP BY specifies which input rows contribute to each group.

SELECT o.order_id,
       COUNT(*) AS joined_rows,
       COUNT(l.line_no) AS line_count,
       SUM(l.quantity * l.unit_price) AS line_total
FROM orders AS o
LEFT JOIN order_lines AS l ON l.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;

O13’s two matched rows enter one group. Its line amounts, €20 and €24, sum to €44. O12 has one line worth €36. The output now has one row per order, so an unaggregated line number would not have one unambiguous value to display for O13.

O14 explains why counting needs care. Its left join produces one row with null line fields. COUNT(*) counts that row; COUNT(l.line_no) counts only present line numbers and returns zero. Real line numbers are non-missing, so that second count measures actual lines. Counting a nullable descriptive field would instead measure how many lines supplied that field.

SUM ignores null inputs, and returns null when there are no non-null inputs to add. If our report defines an empty order’s displayed subtotal as zero, COALESCE(SUM(l.quantity * l.unit_price), 0) uses zero when the sum is null. COALESCE picks the first non-null argument. That display decision still does not make O14 a completed purchase.

A condition on a group belongs in HAVING. To find orders whose lines total at least €40:

SELECT order_id, SUM(quantity * unit_price) AS line_total
FROM order_lines
GROUP BY order_id
HAVING SUM(quantity * unit_price) >= 40
ORDER BY order_id;

This returns O13 with €44. A WHERE condition filters individual input rows before grouping; HAVING filters the groups. If we first kept only lines worth at least €24, O13’s €20 mug would disappear before the sum, and we would be calculating a different total.

When a join inflates a total

The shop also records shipments. O13 has two, S1 and S2; each shipment row refers to the order. A report needs the order’s amount and its shipment count. We might start by joining both collections on their order ID:

SELECT l.line_no, s.shipment_id,
       l.quantity * l.unit_price AS line_total
FROM order_lines AS l
JOIN shipments AS s ON s.order_id = l.order_id
WHERE l.order_id = 'O13'
ORDER BY l.line_no, s.shipment_id;

The query has answered “which line and shipment pairs belong to O13?” There are four. Summing its line amounts gives €88, even though Ada bought €44 of goods. No constraint has failed. The result contains all the combinations requested by the join.

DISTINCT removes equal output rows, but it cannot decide what the report intended to count. The four pairs are distinct because their line numbers or shipment IDs differ. SUM(DISTINCT amount) would happen to give €44 here, but would count two different €20 lines only once. Equal prices do not identify the same purchase.

For one row per order, calculate each collection’s summary at that level before combining them:

WITH line_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS amount
  FROM order_lines
  GROUP BY order_id
), shipment_counts AS (
  SELECT order_id, COUNT(*) AS shipment_count
  FROM shipments
  GROUP BY order_id
)
SELECT o.order_id, t.amount, s.shipment_count
FROM orders AS o
JOIN line_totals AS t ON t.order_id = o.order_id
JOIN shipment_counts AS s ON s.order_id = o.order_id
WHERE o.order_id = 'O13';

WITH gives a name to a query result used by the rest of the statement. Here, line_totals has at most one row per order because it groups by the order ID. So does shipment_counts. Their join can now pair O13’s one amount with its one shipment count without multiplying either. These names do not create permanent tables.

This example deliberately selects an order that has both lines and shipments. To list all order headers, use left joins to the summaries, then decide what absent totals and counts should mean. The choice to preserve empty orders is separate from the choice to prevent multiplication.

Checking whether a match exists

Sometimes we do not need anything from the matching records. We only want orders that contain P7, the Blue mug. An EXISTS condition asks whether a query finds at least one row:

SELECT o.order_id
FROM orders AS o
WHERE EXISTS (
  SELECT 1
  FROM order_lines AS l
  WHERE l.order_id = o.order_id
    AND l.product_id = 'P7'
)
ORDER BY o.order_id;

The inner query refers to the outer order through o.order_id. For each candidate order, it asks whether a line for P7 belongs to it. SELECT 1 supplies a constant; its value does not matter to EXISTS, only the presence of a result does. The answer contains O12 and O13, once each.

If O13 later gets another mug line, it still appears once. The outer query selects orders, and the condition makes a yes-or-no decision about each one. We never create one output row for every matching mug line. A join is appropriate when we need those lines in the answer; an existence test fits when we only need to qualify the order.

The opposite question finds order headers that still have no lines:

SELECT o.order_id
FROM orders AS o
WHERE NOT EXISTS (
  SELECT 1
  FROM order_lines AS l
  WHERE l.order_id = o.order_id
)
ORDER BY o.order_id;

This returns O14. The query is useful as a diagnostic, but it cannot tell us whether O14 is legitimately being assembled or was abandoned after a failure. Interpreting that state requires the application’s rules and transaction boundary.

Checking an answer

When a result looks wrong, expose the identities before hiding them in a total. In the shipment query, selecting the line number and shipment ID made the four combinations visible. Looking only at €88 would have left the multiplication unexplained.

Then follow one familiar record. Does it have no match, one match, or several? Does a filter intentionally remove it? After grouping, which original rows contribute to its total? Small examples with an empty order, two lines, and two related shipments reveal mistakes that a large set of ordinary orders can conceal.

This establishes what the query means. How the engine finds those rows belongs to execution plans and indexing; how we read a large answer in pieces adds another concern. Even a correct result can lose or repeat records when we paginate it while the underlying data changes.