Choose a SQL join by deciding which rows must survive: INNER JOIN keeps matches, LEFT JOIN keeps every row from the left input, RIGHT JOIN keeps every row from the right, FULL JOIN keeps unmatched rows from both, and CROSS JOIN pairs every row with every other row. A join’s match condition determines which pairs qualify; it does not guarantee one output row per input row.
How do you choose the right SQL join?
The table below describes PostgreSQL behavior. “Left” and “right” mean the inputs as written in the query.
As an Amazon Associate I earn from qualifying purchases.
| Join | Rows preserved | What happens when there is no match? |
|---|---|---|
INNER JOIN |
Only row pairs that satisfy the match condition | Unmatched rows from both inputs are omitted. |
LEFT JOIN |
Every row from the left input, plus matching right-side rows | The left row remains; right-side columns are NULL. |
RIGHT JOIN |
Every row from the right input, plus matching left-side rows | The right row remains; left-side columns are NULL. |
FULL JOIN |
Every row from both inputs | Unmatched rows remain, with the other input’s columns set to NULL. |
CROSS JOIN |
Every possible row combination | There is no match condition; all combinations are produced. |
PostgreSQL documents these join forms in its PostgreSQL 16 table-expression reference. The same syntax or behavior should not be assumed across every SQL engine; check the official documentation for the database you use.
How do you join two tables with a match condition?
Use JOIN with an explicit ON condition. In PostgreSQL, bare JOIN means INNER JOIN.
#1 Best Overall
SELECT o.order_id, c.customer_name
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;This PostgreSQL query returns a row for each pair of an order and customer whose IDs match. Aliases make long queries easier to read; qualify columns with their table alias when a name could occur in both inputs. The condition says which pairs count as matches. If one customer has several orders, that customer’s name appears in several output rows.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns matches only
An inner join omits a row from either input if it has no partner that satisfies the condition. It is appropriate when the result should contain only related pairs, such as orders that have a matching customer record.
LEFT JOIN preserves the left input
A left join keeps every row from the table expression on the left, even when there is no qualifying right-side row. In those unmatched output rows, right-side columns are NULL.
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;In this PostgreSQL example, customers without orders still appear, with NULL in order_id. A customer with multiple orders appears once for each matching order.
RIGHT JOIN and FULL JOIN preserve the other rows
RIGHT JOIN applies the same preservation idea to the right input. If the query is easier to understand with a left join, swap the inputs and use LEFT JOIN instead. FULL JOIN preserves unmatched rows from both inputs as well as returning matched pairs; columns from the missing side are NULL for each unmatched row.
When should you use CROSS JOIN?
Use a cross join only when every combination is intended. If the left input has N rows and the right has M, the result has N × M rows. For example, 12 rows paired with 8 rows produce 96 combinations. PostgreSQL’s documentation describes this as a Cartesian product: each output row contains the columns from a row of the first input followed by those from a row of the second.
This multiplication can grow quickly, so check input sizes and the expected output before running a cross join. If you meant to match related records, use an appropriate condition instead.
PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchHow do ON, USING, and NATURAL JOIN differ?
ON gives you an explicit condition
ON accepts a Boolean condition, so it works when the key columns have different names or when the match needs additional rules. A match occurs only when the condition evaluates to true; false and NULL do not qualify.
USING is shorthand for shared-name equality
When both inputs have the same key-column name and equality is the intended match, PostgreSQL supports USING:
SELECT customer_id, customer_name, order_id
FROM customers
JOIN orders USING (customer_id);USING (customer_id) means the values of that named column must be equal. The joined output contains one copy of the customer_id column, rather than a separate copy from each input. Use ON when the columns have different names or the condition is more involved.
NATURAL JOIN infers the keys from column names
NATURAL JOIN implicitly uses every column name shared by the two inputs as a match column. That can make a query’s meaning change when someone adds a same-named column to either input. For instructional and production queries, an explicit ON or USING condition makes the intended relationship easier to see and less dependent on schema changes. See PostgreSQL’s PostgreSQL 18 table-expression reference.
Why can a LEFT JOIN still remove rows?
A left join determines matches using its join condition, then applies outer conditions such as WHERE. That order matters when filtering an optional right-side table.
To keep all customers while attaching only orders created on or after January 1, 2026, put the date restriction in the join condition:
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';A customer without a qualifying order remains, with NULL order columns. If instead you put o.created_at >= DATE '2026-01-01' in the WHERE clause, rows with no matching order have NULL for that value and fail the predicate. Those customers are removed. Put a condition in ON when it defines which right-side rows can match; use WHERE when it should filter the completed result.
Why is a SQL join duplicating rows?
Joins produce one output row for each matching pair, not necessarily one row per row in either input. If a left-side record matches three right-side records, it appears three times. This is expected behavior when the relationship is one-to-many; it may reveal a data or query issue if you expected one-to-one.
Recommended Free Tools
- Check whether the intended key is unique in each input. A repeated key on either side can multiply matches.
- Compare row counts before and after each join so you can identify where the expansion occurs.
- Confirm that the join uses the intended key and compatible values, rather than a label that may be repeated or inconsistent.
- Decide whether multiple matches belong in the result. If not, define the intended rule for choosing or aggregating records rather than assuming the join will choose one.
PostgreSQL’s join behavior is based on the number of qualifying row pairs; the database does not infer the business meaning or uniqueness of a key for you.
Common SQL join mistakes to check
- Using the wrong preservation behavior: Choose the join based on which unmatched rows the result must retain.
- Filtering a nullable side in WHERE: A predicate on a right-side column can reject the
NULL-extended rows from a left join. - Expecting NULL to match NULL: An
ONcondition that evaluates toNULLis not true, so it does not create a match. - Assuming a key is unique: Verify the data and intended relationship before treating output as one row per source row.
- Using NATURAL JOIN without accounting for schema changes: New shared column names can silently become extra match conditions.
- Using CROSS JOIN unintentionally: Its row count is the product of the input counts.
- Reordering tables as a presumed performance fix: SQL result semantics are distinct from physical execution plans. PostgreSQL notes that explicit inner joins do not constrain join order; use the target engine’s plan and documentation for performance decisions.
The syntax and behavior in the examples are grounded in PostgreSQL documentation, including its PostgreSQL 16 discussion of explicit joins. Other database engines may differ in supported syntax and details.
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.
