A SQL join combines rows from two table expressions by matching them against a condition. Choose the join type by deciding which unmatched rows should remain: only matches, unmatched rows from one side, unmatched rows from both sides, or every possible pairing.
How a join matches rows
A join creates output rows from rows in two inputs. Its join condition determines which pairs count as matches. For example, a city table and a weather table might be joined by comparing the city name in one table with the city field in the other.
As an Amazon Associate I earn from qualifying purchases.
In PostgreSQL, the join condition is commonly written with ON. Give columns their table names or aliases when both inputs have columns with the same name, such as id; this makes it clear which column the query uses. PostgreSQL’s join tutorial introduces joins and aliases.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Which join type keeps the rows you need?
| Join type | Rows returned |
|---|---|
INNER JOIN |
Only row pairs that satisfy the join condition. |
LEFT JOIN or LEFT OUTER JOIN |
Matching pairs and every row from the left input. For a left row without a match, right-side columns are NULL. |
RIGHT JOIN or RIGHT OUTER JOIN |
Matching pairs and every row from the right input. For a right row without a match, left-side columns are NULL. You can express the same preservation by swapping the inputs and using a left join. |
FULL JOIN or FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs, with NULL in columns from the side with no match. |
CROSS JOIN |
Every possible pair of input rows. With N rows on one side and M on the other, it produces N × M pairs. |
These definitions follow the PostgreSQL SELECT reference and PostgreSQL 13 table-expression reference. SQL implementations can differ, so check the documentation for your database when portability matters.
#1 Best Overall
Choose the condition syntax deliberately
Use ON for an explicit match rule
ON accepts a Boolean expression describing when rows match. It is the clearest choice when the related columns have different names or when the relationship is more specific than equality between same-named keys.
SELECT city.name, weather.temperature
FROM city
JOIN weather ON weather.city = city.name;Use USING for a shared equality key
When both inputs have a same-named column that should match by equality, USING (key) is a concise alternative. PostgreSQL returns the listed join column once, rather than showing one copy from each input.
SELECT *
FROM orders
JOIN customers USING (customer_id);Be cautious with NATURAL
NATURAL JOIN matches on every column name shared by the two inputs. That can make a query’s behavior change if a schema later gains another same-named column. Prefer an explicit ON or USING condition when the intended relationship should be visible and stable.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Understand row counts and outer-join filters
One row can produce several matches
A join does not automatically deduplicate. If one row on the left matches three rows on the right, it produces three joined rows. Check whether the join key is unique on the side you expect to match once; otherwise, the additional pairs may be correct or may reveal a data or query assumption to investigate.
A filter can remove rows an outer join preserved
An outer join first determines matches using its join condition and supplies NULL values for the unmatched side. A later WHERE condition on a right-side column can reject those null-extended rows, making the result behave like an inner join for that condition. PostgreSQL documents the distinction between the join condition and conditions applied afterward in its SELECT reference.
A cross join multiplies the inputs
Use CROSS JOIN only when all combinations are intended. Its output count is the product of the input row counts, so even moderate inputs can produce a large result.
Join a table to itself with aliases
A self-join uses one table twice in different roles. Aliases distinguish those roles—for example, an employee row and that employee’s manager in an employee table.
Recommended Free Tools
SELECT staff.name AS employee, manager.name AS manager
FROM employee AS staff
LEFT JOIN employee AS manager
ON staff.manager_id = manager.id;Here, staff and manager refer to separate instances of the same table. A left join keeps employees even when no manager row matches.
Quick Recap
A practical check before relying on a join
- Confirm which unmatched rows must remain, then choose the join type.
- Make the intended relationship explicit with
ON, or useUSINGfor a deliberate same-named equality key. - Check whether the match key is unique where you expect one-to-one matches; multiple matches multiply result rows.
- For an outer join, inspect later
WHEREconditions that refer to the nullable side. - Qualify shared column names with aliases, and avoid implicit matching when a schema change could alter the common columns.
- Verify syntax and behavior against your database’s documentation; the examples and cited semantics here are PostgreSQL-based.
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.
