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 →A SQL join combines rows from two inputs according to a condition. Choose the join by deciding which unmatched rows must remain: INNER JOIN keeps only matches, LEFT JOIN keeps every row on the left, RIGHT JOIN keeps every row on the right, and FULL OUTER JOIN keeps unmatched rows from both sides. A CROSS JOIN instead produces every possible pair.
What does a SQL join do?
A join pairs rows from tables or other query results. For a conditional join, the ON clause states which pairs count as matches. The join type determines what happens to rows that have no match.
Consider two tables: customers(customer_id, name) and orders(order_id, customer_id). A customer can have no orders, one order, or several. Joining these tables can therefore preserve customers, show only customers with orders, or reveal unmatched orders depending on the join type and which table is kept.
These are logical results, not instructions to use a particular physical algorithm. SQL Server documentation distinguishes logical join operations from execution methods such as nested loops, merge, hash, and adaptive joins; the optimizer selects a method using factors including table size, indexes, and data distribution. The join type alone does not establish which query will be faster. Microsoft Learn: Joins (SQL Server).
#1 Best Overall
Which join type should you use?
| Join type | Rows preserved | When it fits |
|---|---|---|
INNER JOIN |
Only row pairs that satisfy the join condition; unmatched rows from both inputs are excluded. | When the result should include only entities with a related row on both sides. |
LEFT JOIN or LEFT OUTER JOIN |
Every left-side row and any matching right-side values. Right-side columns are NULL when there is no match. | When the left input is required and related details are optional. |
RIGHT JOIN or RIGHT OUTER JOIN |
Every right-side row and any matching left-side values. Left-side columns are NULL when there is no match. | When the right input is the side whose unmatched rows must remain. |
FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs; columns from the missing side are NULL. | When reconciling two sets and retaining records found in either one. |
CROSS JOIN |
Every possible pair of rows from the two inputs. | When combinations are intended, such as pairing each item with each category. |
Outer-join behavior is also described in the PostgreSQL table-expressions manual mirror. That URL hosts older PostgreSQL documentation, so consult the documentation for your database and version for syntax and version-specific guidance. SQLite likewise describes joins in terms of Cartesian products and documents its join syntax and left-join behavior in its SELECT documentation.
How does INNER JOIN differ from LEFT JOIN?
INNER JOIN: return only matches
An inner join discards a customer if no order has the same customer ID. It also excludes an order if its customer ID does not match a customer row. In practical terms, this is useful when the report is specifically about customer-order pairs, not all customers.
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON o.customer_id = c.customer_id;LEFT JOIN: retain every left row
A left join keeps each customer whether or not an order matches. If a customer has no matching order, the selected order column is NULL in the output row.
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id;Use this when the customer list is the required foundation of the result and order data is optional. The left and right labels refer to the inputs’ positions in the query: in this example, customers is left because it appears before the join.
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 glitchesRIGHT and FULL OUTER JOIN: preserve the other side or both
A right join applies the same preservation rule to the right input. If you find a right join hard to read, you can often swap the table order and use a left join instead, while adjusting the selected columns and any other query logic.
A full outer join retains unmatched customers and unmatched orders as well as matching pairs. It is useful for reconciliation, where records present in either input matter. For an unmatched row, the columns belonging to the absent side are NULL. Confirm that your database supports the syntax you need before relying on a particular join type.
CROSS JOIN: make all combinations
A cross join returns each row from one input paired with every row from the other. If one input has m rows and the other has n, the result has m × n pairs. This can be intentional—for example, generating every product-and-region combination—but can become unexpectedly large if a matching condition was omitted from a join that needed one.
Why did my join return repeated rows?
A join does not guarantee one output row per input row. If one customer matches three order rows, the result contains three customer/order pairs, so the customer’s name and ID appear three times. That is the expected result for a one-to-many relationship, not necessarily a data error.
Before treating repeated values as duplicates, check the relationship you expect and whether the join key is unique on either side. If both sides can contain multiple rows for the same key, each qualifying combination can appear. To diagnose counts, inspect the matching rows and the key’s uniqueness rather than assuming the join should return one row per customer.
Why does a LEFT JOIN return NULLs?
With a left join, NULLs in right-side output columns can mean there was no matching right row; the join fills in those missing-side values with NULL. But a source row may also contain NULL in a column even when it matched. SQL Server documentation explains that NULL values do not match one another in join comparisons and notes that outer joins can introduce NULLs for absent matches. Microsoft Learn: Joins (SQL Server).
To distinguish an unmatched row from a matched row whose optional field is itself NULL, test a right-side identifier that is guaranteed non-NULL for real rows. For example, if order_id identifies an order and cannot be NULL, this query finds customers with no matching order:
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;The test works because a missing order receives NULL for o.order_id. If the chosen test column is allowed to be NULL in an actual order, it cannot reliably distinguish that order from a missing match.
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 →Should a condition go in ON or WHERE?
ON determines which right-side rows qualify as matches. WHERE filters the joined result afterward. With an outer join, moving a right-side condition from ON to WHERE can remove preserved rows that have no qualifying right-side match.
Keep every customer, but attach only qualifying orders
Put the order condition in ON when you want every customer, while including only orders that meet the condition. In this example, customers without a paid order remain, with NULL order columns.
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid';Keep only customers with a qualifying order
Put the condition in WHERE when the result should exclude customers without a paid order. The NULL-extended rows do not satisfy the equality condition, so they are filtered out.
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.status = 'paid';The right choice depends on which rows the result must preserve. This is about logical query behavior; an optimizer may implement an equivalent query differently.
Quick Recap
How to troubleshoot a join result
- Rows are missing: Check whether an inner join is excluding unmatched records, whether the join condition is too restrictive, and whether a later
WHEREpredicate filters rows you meant to preserve. - Rows appear more than once: Check whether a key matches multiple rows and whether that one-to-many or many-to-many relationship is intended.
- Columns contain NULL: Determine whether the NULL comes from the source data or from an unmatched side of an outer join. Test a non-NULL identifier when checking for a missing match.
- The result is unexpectedly huge: Check for an unintended cross join or a condition that does not limit matches as expected. A cross join produces every possible pair.
- The query is slow: Do not assume a different logical join type is automatically faster. Review the execution plan and relevant data, indexes, and distribution for the database engine in use.
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.
