Recommended Free Tools
Choose a SQL join by asking which rows must survive. INNER JOIN keeps matching pairs; LEFT JOIN keeps every row from the left input; RIGHT JOIN keeps every row from the right; FULL OUTER JOIN keeps unmatched rows from both; and CROSS JOIN creates every possible pair. Each example below uses an explicit ON condition so you can see how the match is defined.
What a SQL join does
A join combines rows from two table expressions according to a condition, commonly a relationship between key columns. The result can contain more rows than either input: if one customer matches two orders, that customer appears in two result rows. A join does not automatically collapse duplicates or aggregate data. PostgreSQL’s table-expression guide and Microsoft’s SQL Server join overview describe this matching behavior.
As an Amazon Associate I earn from qualifying purchases.
Which join should you use?
| Join form | Rows retained | Typical use |
|---|---|---|
INNER JOIN (or JOIN) |
Only row pairs that meet the ON condition. |
Return records only when a related record exists. |
LEFT JOIN |
Every left-side row, plus matching right-side rows. Right-side columns are NULL when there is no match. |
Keep every customer, including customers without orders. |
RIGHT JOIN |
Every right-side row, plus matching left-side rows. Left-side columns are NULL when there is no match. |
Keep every row from the right input. |
FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs. Columns from the missing side are NULL. |
Reconcile two lists while keeping entries found in either one. |
CROSS JOIN |
Every possible combination of a left row and a right row. | Deliberately combine options, such as every size with every color. |
For LEFT and RIGHT joins, the preserved side is the distinction: reversing the input order and switching the join direction expresses the corresponding preservation choice. NULL means a value is missing or unknown; it is not an empty string or zero. PostgreSQL documents the preserved-row behavior, and SQLite’s SELECT documentation describes null-filled columns in outer-join results.
Examples using customers and orders
Assume these illustrative tables. Customer Ada has two orders, Ben and Cy have none, and order 103 refers to customer ID 4, which is absent from the customer table.
#1 Best Overall
| customers: id | name |
|---|---|
| 1 | Ada |
| 2 | Ben |
| 3 | Cy |
| orders: id | customer_id | total |
|---|---|---|
| 101 | 1 | 25 |
| 102 | 1 | 40 |
| 103 | 4 | 12 |
INNER JOIN: only matching pairs
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;The result has two rows for Ada, one for each matching order. Ben and Cy are excluded because they have no orders. Order 103 is excluded because no customer has ID 4.
LEFT JOIN: keep every customer
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 still contributes two rows. Ben and Cy each contribute a row whose order columns are NULL. Order 103 remains excluded because this join preserves the left input, customers, not unmatched rows from orders.
RIGHT JOIN: keep every order
SELECT c.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;This preserves every order, including order 103. For that unmatched order, the customer columns are NULL. A right join is useful when the right input is the one whose unmatched rows must remain; otherwise, reversing the table order and using a left join can express the same preservation choice.
Outdated 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 matchPC 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 & 11FULL OUTER JOIN: keep unmatched rows from both
SELECT c.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;This keeps Ada’s matching order rows, Ben and Cy without orders, and order 103 without a customer. In unmatched rows, columns on the absent side are NULL. Confirm that the database and version you use support this syntax.
CROSS JOIN: create every combination
SELECT s.size, c.color
FROM sizes AS s
CROSS JOIN colors AS c;If sizes has three rows and colors has four, the result has 12 combinations. Use a cross join only when all pairings are intended; unlike the examples above, it has no matching condition.
Filter outer-join matches without losing preserved rows
Suppose the goal is to list every customer and attach only open orders. Put the order-status condition in ON:
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';This still keeps customers with no open order, with NULL in open_order_id. If you put o.status = 'open' in WHERE, rows with no matching order have a NULL status and fail that filter, so those customers disappear. For an outer join, ON determines matches before unmatched rows are added; WHERE filters the resulting rows. See PostgreSQL’s explanation and SQLite’s SELECT reference.
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 →Self-join: join a table to itself
A self-join uses ordinary join syntax to refer to one table twice. Give each reference a different alias so the query can distinguish the two roles. For example, to show employees and their managers:
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 alias e identifies the employee row and m the manager row. The left join keeps employees who have no manager recorded, such as a top-level executive. There is no separate SELF JOIN keyword. PostgreSQL’s join tutorial illustrates the same aliasing principle.
Write readable joins and check dialect support
- Use explicit
JOIN ... ON. It makes the match rule visible and keeps it distinct from later filtering. Microsoft recommends separating join conditions inFROMfrom other search conditions inWHEREin its join guidance. - Qualify columns with aliases. Write
c.idrather thanidwhen both inputs have a column with that name; qualification also makes self-joins understandable. - Use
USINGonly when the shared key name is clear.JOIN orders USING (customer_id)is a concise alternative when both inputs have a column namedcustomer_id.NATURAL JOINinfers its condition from every shared column name, so changes to table columns can change what gets matched. PostgreSQL documentsON,USING, andNATURALin its table-expression reference. - Check the database and version. PostgreSQL 17, the cited SQL Server Transact-SQL references, and current SQLite documentation cover the principal join forms here; that does not establish support in every SQL product or version. SQLite also documents a dialect-specific effect:
CROSS JOINprevents its optimizer from reordering joined tables. Do not treat that behavior as a portable performance technique. See PostgreSQL, SQL Server, and SQLite.
Join type is not a performance algorithm
INNER, LEFT, and the other join forms define which rows the result must include. They do not, by themselves, specify how the database physically produces that result. SQL Server describes physical algorithms including nested loops, merge, and hash joins, with the optimizer choosing among them based on inputs and available information. Performance depends on the query, schema, data, and database plan; there is no general rule that a left join is always slower. For SQL Server’s explanation, see Joins (SQL Server).
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.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Clear out junk files and repair common Windows errors3Fix the driver behind crashes, sound loss and screen glitches
