SQL joins combine related rows from multiple tables using a match condition. Choose the join type according to which unmatched rows you want to keep: INNER JOIN returns matches only, while LEFT JOIN retains every row from its left input, even when no right-side row matches. The examples below use explicit ON conditions; syntax and support can vary by database.
What a SQL join does
A join pairs rows from two inputs when they satisfy a condition. For example, a customer table might store names and customer IDs while an orders table stores order IDs and the customer IDs associated with each order. Joining the tables lets a query return customer and order details together. PostgreSQL 17 describes the join forms and their behavior in its table expressions documentation.
As an Amazon Associate I earn from qualifying purchases.
The join type controls which pairs and unmatched rows appear. In the table below, “left” and “right” refer to the inputs on either side of the join in the query.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitches| Join type | Rows returned | Useful question |
|---|---|---|
INNER JOIN |
Only row pairs that satisfy the join condition. | Which orders have a matching customer? |
LEFT JOIN |
Every left-side row, with matching right-side values where available. Right-side columns are NULL when no match exists. |
Which customers have orders, and which have none? |
RIGHT JOIN |
Every right-side row, with matching left-side values where available. Left-side columns are NULL when no match exists. |
Which rows in the right-side list have no left-side match? |
FULL OUTER JOIN |
Matching pairs and unmatched rows from both inputs. | Which records occur in either source, including unmatched records? |
CROSS JOIN |
Every possible pair of rows from the two inputs. | What are all possible combinations of products and sizes? |
In PostgreSQL, OUTER is optional in LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN; INNER is the default if no join type is specified. A cross join between inputs containing N and M rows produces N × M pairs. See the PostgreSQL 17 join documentation for the documented semantics.
#1 Best Overall
How to join two tables with a shared key
This query returns every customer and any orders that match that customer’s ID:
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;The ON condition compares the customer ID in each table. Because this is a left join, customers remain in the result even if no order matches; the order columns are NULL for those rows. Change LEFT JOIN to INNER JOIN if you want only customers with a matching order.
The aliases c and o make it clear which table supplies each selected field. Qualifying column names is especially helpful when both tables contain a column called id or another shared name. Microsoft’s SQL Server joins guidance recommends putting join conditions in the FROM clause and explains the use of aliases and qualified columns.
Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Choose the join condition carefully
Use ON for an explicit match
ON expresses the comparison that determines whether rows match, such as o.customer_id = c.customer_id. It is the clearest option when learning joins or when the keys have different names.
Use USING when the matching columns share a name
When both inputs have a same-named key, USING can shorten an equality condition. For example, JOIN orders USING (customer_id) expresses a match on the shared customer_id column. PostgreSQL documents USING as shorthand for equality comparisons on the listed columns; check your database’s documentation for its exact behavior.
Avoid relying on NATURAL JOIN in general-purpose queries
NATURAL JOIN implicitly matches every column name shared by both inputs. That can make a query’s meaning change if a later schema change adds another same-named column. Explicit ON conditions make the intended relationship visible instead.
Keep unmatched rows when filtering an outer join
A condition on the right-side table can have different effects depending on whether it appears in ON or WHERE. With a left join, put a right-side condition in ON when the goal is to match only qualifying right rows but still retain every left row:
Free tools Windows power users keep installed
One-click scans. No signup required.
SELECT *
FROM t1
LEFT JOIN t2
ON t1.num = t2.num
AND t2.value = 'xxx';This keeps each t1 row, including rows for which no t2 row has both the same number and the required value. The unmatched right-side fields are NULL.
By contrast, this query filters after the join:
SELECT *
FROM t1
LEFT JOIN t2
ON t1.num = t2.num
WHERE t2.value = 'xxx';For an unmatched left row, t2.value is NULL, so it does not satisfy the WHERE condition and is removed. PostgreSQL explains this distinction in its discussion of join conditions and filters.
Find left-side rows with no match
To find rows from the left table that have no matching right-side record, use a left join and test a right-side key that cannot be NULL in actual matching rows:
SELECT t1.*
FROM t1
LEFT JOIN t2 ON t2.num = t1.num
WHERE t2.num IS NULL;This is an anti-match pattern: it returns left-side rows for which the join found no right-side row. Choose a right-side column that is non-NULL for real matches; otherwise the test can mistake a matched row with a null value for an unmatched one.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Understand why a join can multiply rows
A join returns a row for every matching pair, not necessarily one result per key. If a key appears twice in one input and three times in the other, matching on that key can produce six pairs. This is expected pairwise behavior, not automatically a duplicate-data error.
- Check whether the columns you believe are keys are actually unique in each input.
- Determine whether the relationship is one-to-one, one-to-many, or many-to-many.
- Compare row counts before and after the join to identify where extra pairs appear.
- Do not use
DISTINCTto hide the increase until you know whether those combinations are valid.
Check your database’s supported syntax
Join behavior is broadly familiar across SQL products, but syntax support is not identical. PostgreSQL 17 documents inner, left, right, full, and cross joins, along with ON and USING. Microsoft documents inner, left, right, full outer, and cross logical joins for SQL Server. The MySQL 8.4 JOIN Clause reference lists inner/cross, left, and right forms but does not list FULL OUTER JOIN. Confirm support in the manual for the database and version you actually use before adopting a query.
What affects join performance
The logical join type in SQL is not the same as the physical algorithm a database uses to execute it. Microsoft Learn states: “SQL Server implements both logical join operations (defined by Transact-SQL syntax) and physical join operations (the actual algorithms used to execute the joins).” SQL Server documents nested loops, merge, hash, and—beginning with SQL Server 2017—adaptive joins. Its optimizer chooses an approach based on factors including table size, indexes, and data distribution, as described in the SQL Server joins documentation.
Do not assume that a left join is inherently slower than an inner join or that one physical algorithm is always best. Check the execution plan produced by your database and test with representative data. The result depends on the engine and version, available indexes, row counts, and data distribution.
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.
