Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A SQL join combines rows from two table expressions according to a matching rule. Choose the join type by deciding which unmatched rows should remain: INNER JOIN keeps matches only, an outer join can preserve unmatched rows from one or both sides, and CROSS JOIN returns every possible pair. The examples and semantics here follow PostgreSQL documentation; details can differ among database systems.
How a join matches rows
A join evaluates a condition for rows from two inputs. For example, a city table might be matched to a weather table by comparing the city name in each. In PostgreSQL, the join condition is commonly written with ON:
SELECT cities.name, weather.temperature
FROM cities
JOIN weather ON weather.city = cities.name;Use table aliases to make references clear, especially when both tables contain columns with the same name. In a self-join, aliases distinguish the two roles played by one table, such as staff and manager:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
SELECT staff.name, manager.name AS manager_name
FROM employee AS staff
JOIN employee AS manager ON staff.manager_id = manager.id;PostgreSQL’s join tutorial demonstrates matching tables and using aliases for a self-join. Qualifying a column as table_alias.column also avoids ambiguous references such as id when both inputs have that column.
#1 Best Overall
Which join type should you use?
The key question is which unmatched rows belong in the result. PostgreSQL’s table-expression documentation and SELECT reference describe these join behaviors.
| Join type | Rows returned | Typical use |
|---|---|---|
INNER JOIN |
Only pairs that satisfy the join condition. | Return records that have a match on both sides. |
LEFT [OUTER] JOIN |
Matching pairs and every unmatched row from the left input. Columns from the right input are NULL for unmatched rows. |
Keep every row from the primary, left-side table even when related data is absent. |
RIGHT [OUTER] JOIN |
Matching pairs and every unmatched row from the right input. Columns from the left input are NULL for unmatched rows. |
Keep every row from the right-side table; this can be rewritten as a left join by swapping the inputs. |
FULL [OUTER] JOIN |
Matching pairs and unmatched rows from both inputs, with missing-side columns set to NULL. |
Retain all rows from both sides, whether or not a match exists. |
CROSS JOIN |
Every possible pair of rows. If the left input has N rows and the right has M, the result has N × M rows. | Generate combinations intentionally rather than match on a key. |
Choose how to express the match
Use ON for an explicit condition
ON accepts a Boolean expression that defines when two rows match. It is the clearest choice when the relationship is more complex than equality or when you want the key relationship visible in the query.
SELECT orders.id, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.id;Use USING for a shared equality key
USING (key) is a concise option when both inputs have a same-named column that should be compared for equality. It returns each listed join column once, rather than showing separate copies from each input.
SELECT *
FROM orders
JOIN customers USING (customer_id);In this example, both inputs must have a customer_id column, and that shared column is the intended equality key. PostgreSQL documents ON and USING in its SELECT reference and table-expression reference.
Be cautious with NATURAL JOIN
NATURAL JOIN matches on every column name shared by the two inputs. That can make a query’s behavior change when a schema gains another same-named column. Prefer ON or USING when you want the intended match to remain explicit and reviewable.
Check row counts and NULL behavior
Joins can multiply rows
A join does not inherently deduplicate. If one row on the left matches three rows on the right, that left row appears in three result pairs. Check whether the key is unique on the side you expect to match once; otherwise, multiple matches can produce more rows than you anticipated.
Keep outer-join rows when filtering
An outer join preserves unmatched rows according to the join’s own condition. A later WHERE condition on a right-side column can reject rows where that column is NULL, removing the unmatched rows you meant to keep. PostgreSQL distinguishes the join condition from later conditions in its table-expression documentation. Decide whether a restriction defines a match or filters the final result before placing it in ON or WHERE.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Use CROSS JOIN deliberately
A cross join produces the Cartesian product: each row on one side pairs with every row on the other. PostgreSQL’s SELECT reference notes that CROSS JOIN is equivalent to INNER JOIN ON (TRUE). Before running one, consider the product of the input row counts and whether that many combinations are intended.
A quick way to decide
- Identify the two table expressions and the columns or condition that should define a match.
- Choose which unmatched rows must survive: neither side for
INNER, the left or right side for a one-sided outer join, both sides forFULL OUTER, or all combinations forCROSS. - Write the relationship with
ON, or useUSINGwhen a same-named equality key expresses the intent. - Qualify shared column names with table aliases and consider whether the key can match multiple rows.
- If using an outer join, review filters on the nullable side so they do not remove unmatched rows unintentionally.
These definitions are documented for PostgreSQL. Other database systems can differ in syntax or support, so check the documentation for the system you use before relying on a particular form.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
