Check joins ​

A join can return more rows than either input because every matching pair contributes. Check that this is the relationship the query intends before comparing its performance.

State the relationship ​

Use ON or USING to name the matching keys. Qualify ambiguous columns and select the fields the result actually needs. A NATURAL join depends on every shared name, so a schema change can change its condition.

When one row is expected per key, enforce or verify uniqueness on that side. LEFT, RIGHT and FULL joins retain unmatched rows with NULL fields.

Example ​

Two matches on the right produce two joined rows for id 1:

CREATE TABLE accounts (id INTEGER);
INSERT INTO accounts VALUES (1), (2);

CREATE TABLE visits (id INTEGER);
INSERT INTO visits VALUES (1), (1);

SELECT accounts.id, count(visits.id) AS matching_visits
FROM accounts
LEFT JOIN visits USING (id)
GROUP BY accounts.id
ORDER BY accounts.id
FORMAT csv;

1,2
2,0

Compare a change ​

Verify the selected rows and counts before comparing EXPLAIN QUERY PLAN and timings after a rewrite or index change. Use the same data and query parameters. No specific join algorithm or speedup is guaranteed by the SQL spelling.

See the join guide and JOIN reference.