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
Choose a SQL join by deciding which rows must survive: use INNER JOIN for matches only, LEFT JOIN to keep every row from the left input, RIGHT JOIN to keep every row from the right, FULL JOIN to keep unmatched rows from both, or CROSS JOIN to create every possible pairing. The examples below use PostgreSQL syntax; check your database’s official documentation before relying on dialect-specific behavior.
How to join two tables in PostgreSQL
A join combines rows from two table expressions according to a match condition. In PostgreSQL, a basic inner join looks like this:
SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;The condition after ON determines which pairs of rows match. Bare JOIN means INNER JOIN in PostgreSQL. Aliases such as o and c keep queries concise; qualify columns when names might be ambiguous.
For this example, the intended relationship is that an order’s customer_id identifies its customer. A database can evaluate the condition, but it cannot establish that the chosen columns reflect the intended business relationship. Check that the key columns have compatible types and represent the same concept.
#1 Best Overall
Which join preserves the rows you need?
The key choice is what to do with rows that have no match. PostgreSQL’s table-expression documentation defines these join behaviors.
| Join | Rows retained | What happens without a match |
|---|---|---|
INNER JOIN |
Only pairs that satisfy the condition | Unmatched rows from either input are omitted. |
LEFT JOIN |
Every row from the left input, plus matching right-side data | Right-side columns are NULL. |
RIGHT JOIN |
Every row from the right input, plus matching left-side data | Left-side columns are NULL. |
FULL JOIN |
Every row from both inputs, matched where possible | Columns from the missing side are NULL. |
CROSS JOIN |
Every possible pair of rows | There is no match condition; each row is paired with every row on the other side. |
INNER JOIN: keep matches only
Use an inner join when rows without a counterpart should not appear in the result. If one order matches one customer, the result contains that pair. If one order matches multiple rows on the other side, it appears once for each matching pair.
LEFT JOIN: keep all left-side rows
Use a left join when the left input defines the records that must remain, even if related data is absent. For example, to list every customer and any matching orders:
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id;A customer without an order remains in the output; the order columns are NULL. A customer with several matching orders appears in several result rows, one for each match.
Rank #2
RIGHT JOIN: keep all right-side rows
A right join preserves every row from the right input and fills left-side columns with NULL when there is no match. If the query reads more naturally in the opposite direction, swapping the inputs and using a left join expresses the same preservation goal.
FULL JOIN: keep unmatched rows from both sides
A full join returns matching pairs as well as rows that have no counterpart on either side. For an unmatched row, columns belonging to the other input are NULL. Use it when the result must expose unmatched records from both inputs, rather than choosing one side to preserve.
CROSS JOIN: deliberately create every pairing
A cross join pairs each row on one side with every row on the other. With N rows in one input and M in the other, the result has N × M rows. PostgreSQL documents this product in its PostgreSQL 16 table-expression reference. Use a cross join only when all combinations are intended, and consider the input sizes before running it.
Choose ON, USING, or NATURAL deliberately
ON: state the match condition
ON accepts a Boolean condition, so it works for differently named key columns and for matches that require additional criteria. It is the clearest option when the relationship needs to be explicit:
SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;USING: concise equality on shared column names
When both inputs have the same key-column name and equality is the intended condition, PostgreSQL supports USING:
SELECT customer_id, customer_name, order_id
FROM customers
JOIN orders USING (customer_id);USING is shorthand for equality on the named column and returns one output column for that key, rather than a separate copy from each input. Use ON when the names differ or the condition is more involved. PostgreSQL documents both forms in its table-expression reference.
NATURAL: avoid implicit matches in durable queries
NATURAL JOIN infers its match columns from every column name shared by the inputs. That can make a query’s behavior change when a schema gains another same-named column. PostgreSQL describes this behavior in its PostgreSQL 18 table-expression documentation. Prefer an explicit ON or USING condition when the intended relationship should remain visible and stable.
Why a LEFT JOIN can seem to remove rows
A join condition determines whether rows match; an outer WHERE condition is applied afterward. This distinction matters when filtering the optional, right-side data of a left join.
To keep every customer but attach only orders created on or after January 1, 2026, put the date condition in ON:
SELECT c.customer_id, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.created_at >= DATE '2026-01-01';This lets the join treat older orders as non-matches while preserving the customer. By contrast, adding WHERE o.created_at >= DATE '2026-01-01' filters the completed result. For customers with no qualifying order, o.created_at is NULL; the comparison is not true, so those rows are filtered out. Put a condition in ON when it limits which right-side rows may match; use WHERE when it should filter the final joined rows.
In PostgreSQL, an ON expression must evaluate to true for a pair to match. If it evaluates to false or NULL, that pair does not match. For outer joins, PostgreSQL distinguishes the join’s own condition from later outer conditions in its table-expression documentation.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Why a join returns more rows than expected
Joins produce a row for every pair that satisfies the match condition. They do not promise one output row per input row. If a left-side record matches three right-side records, it produces three joined rows.
- Check key uniqueness. If you expect one-to-one matching, verify that the relevant key is unique on both sides. If one side is intentionally one-to-many, multiple output rows are expected.
- Count matches on each side. Compare input row counts with joined row counts, and look for repeated key values in the side you expected to match once.
- Review the condition. Confirm that it uses the intended keys rather than a broad label that may repeat or vary in spelling.
- Account for NULLs. A comparison involving a
NULLkey does not evaluate to true, so it does not create a match under an ordinary equality condition.
PostgreSQL’s explanation of joined rows and match conditions is in its table-expression reference. Whether a textual label is unique or meaningful is a property of your data model, not something the join operation verifies.
Does the written join order control performance?
Written table order describes the query, not necessarily the order in which the database physically executes it. PostgreSQL notes that explicit inner joins do not constrain join order in the way readers might assume; the optimizer can choose a plan. See the PostgreSQL 18 documentation on explicit joins. Do not reorder tables as a presumed performance fix without checking the target database’s plan and documentation. This behavior is dialect- and configuration-sensitive.
Check the target SQL dialect
The syntax and join semantics described here are grounded in PostgreSQL documentation, including the PostgreSQL 16 and 18 references linked above. Other database engines may differ in supported features or details. If a query must run beyond PostgreSQL, verify the target engine’s official documentation for the join type and syntax you plan to use.
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.
