Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →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: an INNER JOIN keeps matches, outer joins preserve unmatched rows from one or both inputs, and a CROSS JOIN creates every possible pair. The examples below use customers and orders to show what each choice returns, how to write its condition, and how to avoid a common outer-join filtering mistake.
How to choose a SQL join
A join combines rows from two table expressions, usually by matching related columns such as a customer ID. First ask whether a matching row is required; then decide whether unmatched rows from the left input, right input, or both must remain. Use a cross join only when you intend every combination. In the examples, the left input is customers and the right input is orders.
These definitions are documented in PostgreSQL 17’s table expressions reference and Microsoft’s Transact-SQL FROM and JOIN reference.
| Join form | Rows retained | Typical use |
|---|---|---|
INNER JOIN or JOIN |
Only pairs that satisfy the join condition. | Return records that have a related record. |
LEFT JOIN |
Every row from the left input, plus matching right-side rows. Right-side columns are NULL when no match exists. |
Keep all customers, including those with no orders. |
RIGHT JOIN |
Every row from the right input, plus matching left-side rows. Left-side columns are NULL when no match exists. |
Keep every order, including one with no matching customer. |
FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs; missing-side columns are NULL. |
Reconcile two lists without dropping entries found in only one. |
CROSS JOIN |
Every possible pair of one left row and one right row. | Combine every size with every color, for example. |
| Self-join pattern | Depends on the join operator; one table is referenced twice. | Relate rows in the same table, such as employees and their managers. |
RIGHT JOIN does not use a different matching principle from LEFT JOIN: reversing the input order expresses the corresponding preservation choice. PostgreSQL treats INNER as the default, so bare JOIN means an inner join there; LEFT, RIGHT, and FULL imply outer joins. Check your database’s documentation for version-specific compatibility.
#1 Best Overall
Use INNER JOIN when only matches should appear
Suppose the tables contain these rows:
| customers: id | customers: name |
|---|---|
| 1 | Ada |
| 2 | Ben |
| 3 | Cy |
| orders: id | orders: customer_id | orders: total |
|---|---|---|
| 101 | 1 | 25 |
| 102 | 1 | 40 |
| 103 | 4 | 12 |
An inner join returns rows where orders.customer_id equals customers.id:
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.id;Ada appears twice because she has two matching orders. Ben and Cy have none, so they are excluded; order 103 is also excluded because customer 4 is not in the customer table. Joins return matching pairs, so multiple matches create multiple result rows rather than being automatically collapsed. See the matching-pair definitions in the PostgreSQL table expressions reference and Microsoft’s FROM and JOIN reference.
Use LEFT JOIN to keep every row from the left input
To list all customers whether or not they have orders, use a left join:
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id;Ada appears twice, once per order. Ben and Cy each appear once with NULL for the order columns. The unmatched order for customer 4 is not included, because the query preserves customers, not orders. The PostgreSQL join tutorial describes this left-join behavior.
NULL indicates a missing or unknown value; it is not the same as an empty string or zero. That distinction matters when you display results or write conditions against the joined columns. See SQLite’s SELECT documentation.
Use RIGHT JOIN to keep every row from the right input
A right join preserves every row from orders, including orders without a matching customer:
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total
FROM customers AS c
RIGHT JOIN orders AS o
ON o.customer_id = c.id;Order 103 remains in the result, with NULL in the customer columns. If a right join feels harder to read, swap the table order and use a left join instead:
Recommended Free Tools
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total
FROM orders AS o
LEFT JOIN customers AS c
ON c.id = o.customer_id;Use FULL OUTER JOIN to keep unmatched rows from both sides
A full outer join includes matches, customers without orders, and orders without customers:
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.total
FROM customers AS c
FULL OUTER JOIN orders AS o
ON o.customer_id = c.id;Ada appears for each matching order; Ben and Cy remain with NULL order columns; order 103 remains with NULL customer columns. This is useful when reconciling two lists and neither side’s unmatched records can be discarded. The form is documented in the Microsoft FROM and JOIN reference and SQLite SELECT documentation; verify support for your database and version before using it.
Use CROSS JOIN when every combination is intended
A cross join pairs every row on one side with every row on the other. For example, if sizes contains four rows and colors contains three, the result has twelve combinations:
SELECT s.size_name, c.color_name
FROM sizes AS s
CROSS JOIN colors AS c;This join has no ON condition because it does not match rows by a relationship. Use it deliberately: the number of output pairs grows with both inputs. Microsoft documents the cross-product behavior in its FROM and JOIN reference. SQLite also assigns CROSS JOIN an optimizer-specific effect that prevents join reordering, so do not treat it as a general-purpose tuning hint; see SQLite SELECT documentation.
Use a self-join to relate rows in the same table
A self-join is an ordinary join that references one table twice under different aliases; SQL has no separate SELF JOIN keyword. For an employee table with a manager_id that points to another employee’s id, use:
SELECT e.name AS employee, m.name AS manager
FROM employee AS e
LEFT JOIN employee AS m
ON e.manager_id = m.id;The aliases e and m distinguish the employee row from the manager row. The left join keeps employees who have no manager recorded, such as a top-level executive. Qualify columns with their aliases when names could be ambiguous. The PostgreSQL join tutorial demonstrates joining a table to itself with aliases.
Put match rules in ON and understand WHERE filters
When a left join must keep customers even if they have no open orders, put the status requirement in the join condition:
SELECT c.id, c.name, o.id AS open_order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'open';Putting o.status = 'open' in WHERE instead removes customers whose joined order columns are NULL, because those rows do not satisfy the filter. In an outer join, unmatched rows are added after ON processing and before WHERE processing; this order is described by SQLite, and PostgreSQL notes that an outer join’s ON condition is not equivalent to a WHERE condition in its table expressions reference. Use WHERE when you intentionally want to filter the final result, including removing some preserved-side rows.
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 →Write readable joins and check dialect differences
Prefer explicit join conditions
Write JOIN ... ON ... so the relationship between tables is visible and separate from later result filters. Microsoft recommends separating join conditions in FROM from other search conditions. PostgreSQL also supports USING (column_name) when both inputs share the join-column name; it is a concise alternative to an equality condition in ON. NATURAL JOIN infers its condition from every same-named column, which can make the join depend on column names beyond the relationship you intended. For clear examples and safer maintenance, prefer explicit ON or carefully chosen USING. See Microsoft’s joins overview and PostgreSQL table expressions.
Check the database and version
The syntax discussed here is documented in PostgreSQL 17, SQL Server’s cited Transact-SQL references, and current SQLite documentation. That does not establish compatibility across every SQL product or version. Consult the reference for the database that will run your query, especially before relying on less common join forms.
Join type does not determine the physical execution method
INNER, LEFT, and the other join types describe which rows belong in the logical result; they do not prescribe one physical algorithm. SQL Server documents algorithms including nested loops, merge, and hash joins, with the optimizer choosing based on the inputs and available information. A left join is not inherently slower than an inner join, and no fixed row-count rule can predict performance for every schema and query. For a specific SQL Server query, see Microsoft’s joins overview.
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.
