Free tools Windows power users keep installed
One-click scans. No signup required.
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
Use INNER JOIN when you only want product–sale pairs that match. Use LEFT JOIN when every product must remain in the result, even when no sale exists. In the second case PostgreSQL fills the sales columns with NULL for a product without a match. That difference determines whether your report answers “what sold?” or “show the entire product catalog and any sales.”
The examples below use an illustrative supermarket schema with products and sales tables. They show the PostgreSQL rules documented in the PostgreSQL 18 tutorial, including how the ON and WHERE clauses affect an outer join.
What is the difference between INNER JOIN and LEFT JOIN?
An INNER JOIN returns only pairs of rows for which the join condition is true. A LEFT JOIN returns every row from its left input, adding matching rows from the right input and using NULL for right-side columns when no match exists.
| Question | Typical query | Rows that survive | Meaning of unmatched data |
|---|---|---|---|
| Which product and sale records have a match? | products INNER JOIN sales ON ... |
Only matching combinations | Products without a sale disappear |
| Which products have a sale, including products with none? | products LEFT JOIN sales ON ... |
Every product; matching sales where available | Sales columns are NULL for a product with no match |
| Which products have no matching sale? | products LEFT JOIN sales ON ... WHERE sales.product_id IS NULL |
Only products with no match | The right-side key is NULL because the join extended the row |
Neither join is inherently better. The correct choice is the side of the relationship your question requires you to preserve.
#1 Best Overall
How does PostgreSQL decide whether two rows match?
The expression after ON is a boolean test applied to candidate row pairs. PostgreSQL emits a joined row for each pair for which that expression evaluates to true. If one product has five matching sales, an inner or left join can produce five result rows for that product; joins do not automatically collapse a one-to-many relationship.
Make the relationship explicit with ON
SELECT p.product_id, p.product_name, s.sale_id
FROM products AS p
JOIN sales AS s
ON s.product_id = p.product_id;Writing JOIN ... ON keeps the relationship visible to readers. In this example, s.product_id = p.product_id defines a match; changing that expression changes the result.
When USING is appropriate
USING (product_id) is shorthand for equality between same-named columns:
SELECT p.product_name, s.sale_id
FROM products AS p
JOIN sales AS s
USING (product_id);PostgreSQL outputs one merged product_id column instead of separate copies from each table. Use it only when the shared column names and equality relationship are exactly what you intend.
Why NATURAL JOIN is risky
NATURAL JOIN infers its condition from every same-named column. Adding an unrelated column with the same name to either table can therefore change the join without changing the query text. Naming the key in ON or USING makes schema changes less surprising.
What supermarket tables are these examples modeling?
The schema is illustrative rather than a report of a verified production database. Assume products contains one row per catalog item and a non-null product_id, while sales contains one row per sale with sale_id, product_id, and sale_date. A product can have many sales, or none.
That one-to-many shape is useful because it exposes the practical distinction: an inner join focuses on existing transactions, whereas a left join starts with the catalog and asks what transaction information can be attached.
Recommended Free Tools
When should I use INNER JOIN for supermarket data?
Use an inner join when a row is useful only if its counterpart exists. A sales report that lists sold products is the straightforward example.
Rank #2
SELECT p.product_id,
p.product_name,
s.sale_id,
s.sale_date
FROM products AS p
INNER JOIN sales AS s
ON s.product_id = p.product_id;The keyword INNER is optional in PostgreSQL, so JOIN means the same thing here. Every output row represents a matching product–sale combination. Products with no sale are absent, and a product with several sales appears once per matching sale.
Restricting an inner join to a period
If the question itself is limited to sales from 2026 onward, a right-side condition in WHERE is natural because unmatched products are not supposed to survive:
SELECT p.product_name, s.sale_id, s.sale_date
FROM products AS p
JOIN sales AS s
ON s.product_id = p.product_id
WHERE s.sale_date >= DATE '2026-01-01';Only matching sales meeting the date condition remain. This is different from the “all products, with 2026 sales when present” requirement shown later.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →How does LEFT JOIN preserve products with no sales?
Put the table whose rows must survive on the left side of the join. For a product catalog, that usually means starting with products.
SELECT p.product_id,
p.product_name,
s.sale_id,
s.sale_date
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id;Every product row is emitted at least once. If one or more sales match, PostgreSQL attaches the corresponding sale columns. If none match, s.sale_id, s.product_id, and s.sale_date are NULL in the extended result row.
A NULL in those right-side columns means “no right-side row matched this join,” not a stored text value such as “none.” To test that state, use IS NULL; comparisons such as s.sale_date = DATE '2026-01-01' do not evaluate to true when s.sale_date is null.
How do I find products with no sales?
Use a left join and then identify rows whose right-side key is null:
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →SELECT p.product_id, p.product_name
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
WHERE s.product_id IS NULL;This is the classic anti-join pattern. It is reliable when sales.product_id is non-null for every real sale, as it normally should be when it references a product. If the right-side key itself is allowed to be null in stored sales rows, test a non-nullable column such as s.sale_id instead:
SELECT p.product_id, p.product_name
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
WHERE s.sale_id IS NULL;The condition is evaluated after the join, so only the null-extended rows remain.
Why did my LEFT JOIN remove rows?
A right-side filter in WHERE runs after the join. For a product with no sale, the right-side value is null, and a comparison with that null is not true; the WHERE clause therefore removes the row.
The filtering mistake
SELECT p.product_name, s.sale_id
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
WHERE s.sale_date >= DATE '2026-01-01';This query keeps products that have a sale on or after January 1, 2026, but it drops products with no sale. Although the query contains LEFT JOIN, the final result behaves like an inner join for this condition.
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 matchPut the right-side restriction in ON when products must remain
SELECT p.product_name, s.sale_id, s.sale_date
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
AND s.sale_date >= DATE '2026-01-01';Now the date is part of the match test. Older sales do not match, but the product still appears with null sales columns; products with no sales also remain. This is the pattern for “all products, with qualifying sales when present.”
Filtering the preserved table
A condition on the left table can stay in WHERE when you want to limit which products are preserved:
SELECT p.product_name, s.sale_id
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
WHERE p.category = 'Bakery';Only bakery products are eligible to survive, and each still retains the left-join behavior for its sales.
Which join should I choose for a specific question?
Translate the business question into a preservation rule before writing SQL. The following patterns cover the common supermarket cases.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
| Business question | Preserved input | Join and filter pattern | Expected result |
|---|---|---|---|
| Which products were sold? | Only matching pairs matter | products JOIN sales ON ... |
Products with no sale are omitted |
| Show the entire catalog and any sales | All products | products LEFT JOIN sales ON ... |
Products without sales have null sale columns |
| Show every product and only sales from 2026 onward | All products | Put the date predicate in ON |
Nonqualifying or absent sales produce null sale columns |
| Find products that have never matched a sale | All products initially | Left join, then WHERE s.sale_id IS NULL |
Only unmatched products remain |
| List products sold during a date range | Only qualifying matches | Inner join with the date predicate in WHERE or ON |
Only products with a qualifying sale appear |
The same predicate can have different placement depending on whether null-extended left rows are part of the answer.
Are INNER JOIN, JOIN, LEFT JOIN and LEFT OUTER JOIN different?
INNER JOIN and JOIN are equivalent in PostgreSQL because INNER is the default. LEFT JOIN and LEFT OUTER JOIN are also equivalent because OUTER is optional.
FROM products AS p
INNER JOIN sales AS s ON s.product_id = p.product_id
FROM products AS p
JOIN sales AS s ON s.product_id = p.product_id
FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.product_id
FROM products AS p
LEFT OUTER JOIN sales AS s ON s.product_id = p.product_idFor teaching and maintenance, keeping the intended type visible can help readers. Use aliases consistently so it is clear which table owns each column, especially when both tables contain a similarly named field.
How do duplicate sales affect a LEFT JOIN?
Joins preserve matching combinations, not a single summary row per product. If a product has three matching sales, the joined result has three rows for that product. This matters when you count, total, or export the result.
Count sales without counting the null-extended row
SELECT p.product_id,
p.product_name,
COUNT(s.sale_id) AS sale_count
FROM products AS p
LEFT JOIN sales AS s
ON s.product_id = p.product_id
GROUP BY p.product_id, p.product_name;COUNT(s.sale_id) returns zero for a product whose joined sale id is null, while counting a non-null-preserved expression such as COUNT(*) would count the left-join row itself. Choose the expression that represents the thing you intend to count.
Keep detail rows when detail is the goal
If the report needs one line per sale, do not aggregate the join. If it needs one line per product, aggregate after joining and group by the product columns, as in the count query above.
What should I check when a join returns unexpected rows?
Work through the join in small, observable steps rather than changing join types at random.
- Verify the key. Confirm that the
ONexpression links the intended columns and uses the correct data types and direction. - Check the preserved side. In a left join, the table written immediately after
FROMis the table whose rows must survive. - Inspect nulls. Select the right-side key and a descriptive column. A null key indicates that no right row matched, while a non-null key indicates an actual match.
- Review every WHERE predicate. A condition on a right-side column can eliminate null-extended rows. Move it into
ONwhen those left rows belong in the result. - Look for one-to-many multiplication. Multiple sales per product legitimately create multiple joined rows. Aggregate only if the report requires one row per product.
- Test the unmatched case. Include a product known to have no sale and confirm whether the question calls for it to appear.
- Avoid implicit relationships. Prefer explicit
ONor carefully scopedUSINGoverNATURAL JOIN, whose inferred columns can change after a schema modification.
Can I combine joins while still preserving products?
Yes, but every later join can change which rows survive. For example, a left join from products to sales can preserve products with no sale, but an inner join from that intermediate result to another table may remove those rows if the intermediate row lacks a match in the new table.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problemsWhen chaining joins, decide the preservation requirement at each step and parenthesize or structure the query so the intended outer join is not accidentally negated by a later filter or inner join.
What does this supermarket example not prove?
The table names, columns, dates, and queries here are teaching examples. They do not describe a verified database schema, row count, execution plan, or measured performance result. Apply the same preservation rules to your actual keys and constraints, and inspect your own data when a result seems surprising.
Frequently asked questions
Can I write a LEFT JOIN in the opposite direction instead of using RIGHT JOIN?
Usually, yes: swap the table order and use a left join so the table you need to preserve is written first. Many teams prefer this form because it keeps the preserved side visually consistent at the start of the query.
What happens when both join keys are NULL?
Equality does not treat one null as equal to another, so ON a.key = b.key does not match two null keys. If null-to-null matching is genuinely part of the business rule, you need an explicit null-safe condition rather than assuming ordinary equality will do it.
Can a left join preserve rows if I filter in a subquery?
Yes. You can pre-filter the right table in a derived table or common table expression and then left join that reduced set. The filter runs before matching, which keeps the same preservation behavior as placing a right-side restriction in ON.
When should I use EXISTS instead of a join?
Use EXISTS when you only need to know whether at least one related row exists and do not need sale columns. It expresses a yes-or-no test and avoids producing multiple detail rows when a product has several matching sales.
Frequently Asked Questions
Can I write a LEFT JOIN in the opposite direction instead of using RIGHT JOIN?
Usually, yes: swap the table order and use a left join so the table you need to preserve is written first. Many teams prefer this form because it keeps the preserved side visually consistent at the start of the query.
What happens when both join keys are NULL?
Equality does not treat one null as equal to another, so ON a.key = b.key does not match two null keys. If null-to-null matching is genuinely part of the business rule, you need an explicit null-safe condition rather than assuming ordinary equality will do it.
Can a left join preserve rows if I filter in a subquery?
Yes. You can pre-filter the right table in a derived table or common table expression and then left join that reduced set. The filter runs before matching, which keeps the same preservation behavior as placing a right-side restriction in ON.
When should I use EXISTS instead of a join?
Use EXISTS when you only need to know whether at least one related row exists and do not need sale columns. It expresses a yes-or-no test and avoids producing multiple detail rows when a product has several matching sales.
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.
