SQL INNER JOIN: Matching Rows, Keys and Duplicate Results

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

The ON condition defines which pairs match. customers AS c INNER JOIN sales AS s ON s.customer_id=c.id compares each sale’s reference with the customer identity. A correct one-to-many relationship yields one joined row per matching sale, not one per customer. Ana’s two sales create two output rows, while Dev creates none because he has no matching sale. The anonymous sale does not match a customer through ordinary equality.

Qualify shared column names with table aliases. Both customers and sales have id, so c.id and s.id identify different values. Select only the fields the consumer needs, with aliases that expose their roles clearly. SELECT * can produce duplicate output names and expose new fields unexpectedly after a schema change. It also makes it harder to see the intended grain of the result.

Multiple matches are normal when the relationship permits them. They are not necessarily duplicates to remove. A customer summary should aggregate sale rows after the join or aggregate sales first, while a transaction detail should preserve each sale identity. Joining on a nonunique descriptive field, such as city, can create many combinations that are unrelated to the intended customer ownership. Compare the ON clause with the actual key definition rather than choosing columns merely because their values sometimes resemble one another.

WHERE filters the joined rows further. A paid-sale condition there is appropriate when the entire result should contain paid sales only. For an inner join, many simple filters can be written in ON or WHERE with the same logical result, but this should not be generalised to outer joins, where placement affects preserved unmatched rows. Indexes on relationship keys can help large joins, yet the optimiser chooses physical algorithms independently of the order you happened to type table names. Focus first on matching semantics and row counts.

Explicit INNER JOIN ... ON is supported by all four systems. USING is supported by PostgreSQL, MySQL and SQLite but not in the same form in SQL Server, so ON is the shared teaching syntax. NATURAL JOIN can silently change matching columns after schema changes and is unsuitable as a shortcut for a stable application relationship.

Check the engine-specific syntax and version requirements in PostgreSQL joined tables.

Return only paid sales with identified customer matches, showing the customer name and sale id.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT c.name,s.id AS sale_id FROM customers AS c
JOIN sales AS s ON s.customer_id=c.id
WHERE s.status='paid' ORDER BY s.id;

name | sale_id
Ana | 101
Ben | 103
Cara | 105

The inner join removes the anonymous paid sale because it has no customer partner. WHERE then excludes pending matched sales. The three results are transactions, not a distinct customer list; a future customer with multiple paid sales would appear more than once.

Replace the correct relationship with c.id=s.id and predict no rows from the supplied ids. Restore s.customer_id=c.id and count five matches. Then filter to Ana and inspect her two different sale ids. Selecting DISTINCT c.name changes the output to a customer list; explain why that is a different result shape rather than a repair to the original detail query.