Displaying Data from Multiple Tables: SQL Joins Explained

Every join answers the same question differently: what happens when a row on one side has no matching partner on the other?

• INNER JOIN — the row is dropped. Only matched rows survive.
• LEFT JOIN — the left-side row survives, with NULLs filling the right-side columns.
• RIGHT JOIN — the right-side row survives, with NULLs filling the left-side columns.
• FULL OUTER JOIN — both sides survive. Matched rows appear once, unmatched rows appear with NULLs on the missing side.

-- Only customers who have orders
SELECT c.customer_name, o.order_id
FROM   customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- Every customer, with their orders where they exist
SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

On our data, the first query returns seven rows (all the orders), the second returns ten rows (all the customers, with NULLs for the three who have no orders).

The four joins are supported the same way in SQL Server, PostgreSQL, Oracle, and every other major database, with one exception: MySQL does not have a native FULL OUTER JOIN. You emulate it there by combining a LEFT JOIN and a RIGHT JOIN with UNION.

NULL means "unknown." That single fact explains everything about how NULLs behave in a join. The expression NULL = NULL is not true — it is unknown, and the database treats unknown as false when filtering. So a join condition that involves NULL in the join column never matches.

The practical consequence depends on the join type:

• An INNER JOIN silently drops any row whose join column is NULL, because nothing matches against it.
• A LEFT JOIN keeps the left row and fills the right columns with NULL.
• A RIGHT JOIN does the mirror.
• A FULL OUTER JOIN keeps everything, with NULLs on whichever side did not match.

SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Customers who have no orders come back with o.order_id as NULL. That NULL is not because the orders table has NULL order ids — it is because the join had no matching order to attach.

If you want to replace those NULLs with something readable, wrap the column in COALESCE(o.order_id, 0). That keeps the row count the same but makes the output cleaner.

A self-join connects a table to itself. You use two different aliases so the database treats the one table as if it were two, and you can then compare rows within that single table.

SELECT c1.customer_name AS customer,
       c2.customer_name AS referred_by
FROM   customers c1
LEFT JOIN customers c2 ON c1.referred_by_id = c2.customer_id;

Each customer row is paired with the customer who referred them. The aliases c1 and c2 refer to the same table but in different roles. Without them, the query would not run — the database could not tell which customer_name you meant.

Self-joins are how you model any hierarchy stored in a single table. Employees and their managers. Categories and their parent categories. Threads and the post they reply to. The two most common uses are finding the parent of each row, and finding the rows that have no parent (the root nodes).

The pattern for finding roots is a LEFT JOIN plus a NULL check on the parent side:

SELECT c1.customer_name
FROM   customers c1
LEFT JOIN customers c2 ON c1.referred_by_id = c2.customer_id
WHERE  c2.customer_id IS NULL;

That returns every customer who was not referred by anyone — the ones at the top of the referral chain.

A CROSS JOIN pairs every row from the first table with every row from the second. It has no ON clause because there is no matching rule — every combination is valid by definition.

SELECT c.customer_name, o.order_id
FROM   customers c
CROSS JOIN orders o;

Seven customers times seven orders equals forty-nine rows. Every customer paired with every order, including pairs that do not correspond to any real transaction.

The other joins all have a matching condition. INNER JOIN keeps only matched pairs. LEFT JOIN keeps all left rows. RIGHT JOIN keeps all right rows. FULL OUTER JOIN keeps everything. CROSS JOIN has no filter at all — it is the cartesian product, which is what joins conceptually start from before any filtering.

You almost never write a CROSS JOIN in a business query. The two legitimate uses are generating test data (creating a grid of every combination) and producing a report where every entity needs to be checked against every other entity. On large tables, a CROSS JOIN can produce an unmanageable number of rows — a thousand-row table joined with itself produces a million rows.

None. They are exactly the same operation. OUTER is an optional keyword that some developers include for clarity and others leave off.

-- These two queries are identical
SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

SELECT c.customer_name, o.order_id
FROM   customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id;

Every major database — MySQL, PostgreSQL, SQL Server, Oracle — treats the two spellings the same way. The OUTER keyword exists in the SQL standard for historical reasons, but it does not change the behaviour.

On a team, pick one spelling and stick with it. Mixed styles in the same codebase are a small readability tax that adds up over time. Both are fine; the important thing is that everyone knows what the style is.

Join first, then group. The join produces the working set of rows, and GROUP BY collapses those rows into categories with an aggregate function applied to each.

SELECT c.customer_name,
       COUNT(o.order_id)          AS order_count,
       SUM(o.order_amount)        AS total_spend
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spend DESC NULLS LAST;

One row per customer, with their order count and total spend. Using LEFT JOIN here means customers with no orders still appear — their count is 0 and their total is NULL (or 0 if you wrap it in COALESCE). Using INNER JOIN instead would drop the customers with no orders entirely.

The result on our data: Alice Corp has 3 orders totalling 28,500. Bob Traders has 2 totalling 7,700. Charlie Ltd and Diana Retail each have one. Ethan Supplies, Fiona Mart, and George Wholesale come back with zero orders.

This pattern is the backbone of almost every customer summary report. The join brings the customer's name and orders together, and the aggregation collapses each customer's orders into a single line.

NATURAL JOIN figures out the join condition on its own. It looks at both tables, finds every column that has the same name and a compatible type, and joins on all of them.

SELECT *
FROM   customers
NATURAL JOIN orders;

On our two tables, the only shared column name is customer_id, so this behaves like an INNER JOIN on that column. The query returns seven rows — the same as an inner join.

The nice part is that you do not have to write the ON condition. The dangerous part is that the join condition is invisible. If the tables happen to share a column name that is not actually the join key — say both have a created_on column for unrelated reasons — the NATURAL JOIN will use it anyway and silently produce the wrong result.

The consensus in most teams is: skip NATURAL JOIN in production. The readability gain is small, and the surprise factor when it goes wrong is large. An INNER JOIN with an explicit ON clause is barely more typing and much more predictable. If you want to save keystrokes, USING is the safer shortcut.

USING is a shorthand for the ON clause when the join column has the same name in both tables.

SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o USING (customer_id);

That is the same as writing ON c.customer_id = o.customer_id, just shorter. The column name is mentioned once instead of twice.

The behavior is identical to an ON clause with one small difference: the joined column appears only once in the output. With ON, you would see both c.customer_id and o.customer_id in the result. With USING, you get one. That cleaner output is one of the small advantages — no need to pick which one to show when both are identical.

USING is safer than NATURAL JOIN because you still explicitly name the column. It saves the typing of writing the condition twice without hiding the join key from the reader. Every major database supports it.

RIGHT OUTER JOIN keeps every row from the right table and fills in NULLs on the left side when there is no match. It is the mirror of LEFT OUTER JOIN.

SELECT c.customer_name, o.order_id
FROM   customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Every order comes back, with its customer where one exists. If an order had no valid customer, the customer columns would be NULL. On our data every order has a customer, so all seven rows match.

Functionally, RIGHT JOIN with the tables in one order is identical to LEFT JOIN with the tables swapped:

-- Same result as the RIGHT JOIN above
SELECT c.customer_name, o.order_id
FROM   orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Because of that symmetry, most teams standardize on LEFT JOIN and reorder their tables when needed. It keeps the join logic reading consistently top-to-bottom and reduces the mental tax of switching between LEFT and RIGHT.

Pair a join with GROUP BY and an aggregate function, and you get per-group summaries that span multiple tables. This is the shape of almost every reporting query.

SELECT c.region,
       COUNT(o.order_id)     AS orders,
       SUM(o.order_amount)   AS revenue,
       AVG(o.order_amount)   AS avg_order,
       MAX(o.order_amount)   AS largest_order
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.region
ORDER BY revenue DESC;

One row per region, with the total orders, revenue, average order value, and largest single order. The join brings customer region and order amount into the same working set, and the aggregate functions summarise it per region.

On our data: North region has 3 customers who placed 5 orders between them. South has 2 customers with 2 orders. West has 2 customers with 1 order.

The choice between JOIN and LEFT JOIN matters here. With a plain JOIN, regions that have customers but no orders would not appear at all. With LEFT JOIN, they would appear with zero counts. Which one you want depends on whether "no orders" is a meaningful answer or not.

Most joins match on equality, but the ON clause accepts any boolean condition. Using >, <, >=, or <= is called a non-equi join, and it lets you connect rows based on ranges or comparisons.

SELECT c.customer_name,
       o.order_id,
       o.order_amount
FROM   customers c
JOIN   orders o
       ON c.customer_id = o.customer_id
      AND o.order_amount > c.credit_limit;

That query returns the orders whose amount exceeds the customer's credit limit. It is a business-realistic example of what non-equi joins are for — flagging cases where one value has exceeded another.

On our data, order 1004 (22,000) exceeds Charlie Ltd's credit limit of 75,000? No. Order 1004 is 22,000 and Charlie's limit is 75,000, so it does not match. What does match is orders over the credit limit. Only C002 with a limit of 25,000 has any order exceeding it — order 1002 at 4,500 is under, order 1005 at 3,200 is under. So on our data the non-equi join returns zero rows, which is a valid answer and a reminder that sometimes a join legitimately returns nothing.

The other common use is temporal joins — pairing an event with the state of some other table at the time of the event. A price history table joined to an orders table on order_date BETWEEN price_start AND price_end is a classic example.

They do completely different things, and confusing them leads to queries that run without error but produce the wrong shape of data.

• JOIN combines columns from two tables side by side. The result is wider — more columns.
• UNION stacks rows from two queries on top of each other. The result is taller — more rows.

-- JOIN: side by side, wider result
SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

-- UNION: stacked, taller result
SELECT customer_name FROM customers
UNION
SELECT customer_name FROM suppliers;

The JOIN produces one row per order with two columns — customer name and order id. The UNION produces a single column containing every distinct name from both tables, one name per row.

UNION has one subtlety worth knowing: it removes duplicates by default. If a name appears in both tables, UNION returns it once. UNION ALL keeps every occurrence. UNION ALL is faster because the database does not have to sort and deduplicate, so it is worth using when you know the data has no duplicates or you specifically want them.

One more thing: for UNION to work, both SELECT statements must return the same number of columns with compatible types. Column names come from the first SELECT. If you violate either rule, the query fails.

Duplicates after a join happen when one side of the join has multiple matching rows. If a customer has three orders, joining that customer to the orders table produces three rows for them. That is expected behaviour, not a bug — the join is doing exactly what you asked.

But if you only want one row per customer, you have two tools. The first is DISTINCT:

SELECT DISTINCT c.customer_name, c.region
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

That returns one row per unique combination of customer name and region. The join still produces three rows for Alice Corp, but DISTINCT collapses them into one because the two selected columns are the same across all three.

The second tool is GROUP BY, which is better when you want to actually summarise the duplicates rather than just hide them:

SELECT c.customer_name, COUNT(*) AS orders
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name;

That returns one row per customer, with the count of their orders instead of the individual order rows. Same number of rows, but the duplicates have been converted into a useful summary.

One thing to know: DISTINCT is not free. The database has to sort or hash the result to find the duplicates, which costs time on large tables. Reach for it when you actually need it, not as a reflex.

EXISTS is designed for a specific job: checking whether a matching row exists. It does not retrieve data. The moment it finds one match, it stops searching. That makes it potentially much faster than a join for existence checks on large tables.

SELECT c.customer_name
FROM   customers c
WHERE  EXISTS (
    SELECT 1
    FROM   orders o
    WHERE  o.customer_id = c.customer_id
);

That returns every customer who has placed at least one order. But it does not return any columns from the orders table — the subquery produces a single value per customer (true or false), and the outer query decides whether to keep the row.

Compare that to a join for the same purpose:

SELECT DISTINCT c.customer_name
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

Same output, but the join has to actually find every matching order, combine the columns, and then DISTINCT has to deduplicate the result. EXISTS gets away with finding just one order per customer and stopping.

The rule of thumb: use EXISTS when you need to know whether something exists. Use a JOIN when you need to see data from the other table. They are not interchangeable — they answer different questions.

You chain the joins. Each additional JOIN clause connects to a table that is already part of the working set, through a condition that references a table already in scope.

SELECT c.customer_name,
       o.order_id,
       p.payment_method
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
JOIN   payments  p ON o.order_id    = p.order_id;

Three tables, joined through two conditions. The first join pairs customers with their orders. The second pairs those order rows with their payments. Each join sees the result of the previous one, which is why the second ON clause can reference o.order_id — orders is already in the working set.

The number of tables is not the limit. You can join as many as you need, and the syntax scales cleanly. What scales less cleanly is the performance and the cognitive load — a five-table join is harder to read than a two-table join and much harder to debug when the output is wrong.

Two things to watch for as joins accumulate:

• Row multiplication. If any join in the chain produces multiple matches per row, the result grows. A three-table join where each level doubles the row count produces eight rows from a starting point of one.
• Alias discipline. With many tables, columns with the same name become ambiguous. Prefixing every column with its table alias is not optional — it is how you keep the query understandable.

It depends on what the WHERE clause checks. If it references the right-hand table's columns with a normal comparison, the LEFT JOIN quietly becomes an INNER JOIN. If it checks for NULL on the right-hand side, it becomes a way to find unmatched rows.

-- Case A: WHERE kills the LEFT JOIN
SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE  o.order_status = 'Delivered';

That query returns only four rows — the customers who actually have delivered orders. The three customers without any orders had NULL in o.order_status after the join, and NULL fails the equality test. So they were filtered out. The LEFT JOIN kept them initially, but the WHERE clause removed them afterwards.

-- Case B: WHERE finds unmatched rows
SELECT c.customer_name
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE  o.customer_id IS NULL;

That returns three customers — Ethan Supplies, Fiona Mart, and George Wholesale. The IS NULL check is specifically looking for rows the join could not match, which is the whole point of a LEFT JOIN combined with this filter.

The lesson: WHERE runs after the join. Any condition that would exclude NULLs will undo the LEFT JOIN's work. If you want to keep the LEFT JOIN behaviour while still filtering on the right-hand table, move the condition into the ON clause.

The answer depends on what you want to happen to those rows. If you are fine with them disappearing, use INNER JOIN. If you need to see them, use an outer join.

-- Every customer, matched or not
SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Unmatched customers appear with NULL in the order columns. If you want to make the output cleaner, wrap the columns in COALESCE:

SELECT c.customer_name,
       COALESCE(o.order_id, 0)          AS order_id,
       COALESCE(o.order_status, 'None') AS status
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Now instead of NULL, order id is 0 and status is the literal string "None". The report reads more naturally, and downstream code that consumes the output does not have to special-case NULLs.

The choice of LEFT versus RIGHT is really a question of which table is the "primary" one for your report. If customers are the primary entity, use LEFT JOIN with customers on the left. If orders are, flip the tables and either use LEFT JOIN again or RIGHT JOIN. Functionally equivalent; pick one style for consistency.

Joins are usually the most expensive operation in a query, and on large tables they are almost always where the time goes. Three factors decide how much they cost.

Indexes on the join columns are the biggest lever. Without an index on orders.customer_id, a join from customers to orders has to scan every order row to find matches. With an index, the database looks up the matching orders directly. On a table with a hundred million orders, that is the difference between a query that runs in a second and one that runs in a minute.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Join type matters. INNER JOIN is generally faster than OUTER JOIN because it does not have to track unmatched rows and fill them with NULLs. If you do not need the unmatched rows, do not use an outer join.

Query complexity compounds. Each additional join multiplies the potential row count at each step. A four-table join where every level triples the row count ends up with eighty-one rows for every starting row. Reducing the number of tables in the query, or filtering the working set with WHERE before joining, keeps the intermediate results small.

How to know if your join is actually the problem: run EXPLAIN in front of the query. A plan that says "sequential scan" on a large table is usually a missing index. A plan that says "nested loop" with an unexpectedly high row count is usually a signal that the optimizer underestimated the intermediate result.

Four habits, and they are the same on every database.

Define the join condition precisely. Use the correct columns — usually the primary key on one side and the foreign key on the other. Make sure the data types are compatible. A join between a text column and an integer column either fails or silently coerces, and both outcomes are worse than fixing the schema.

Test against rows that have no match. On our data, C005, C006, and C007 have no orders. Run any join against those specific customers first and check the output. A LEFT JOIN should keep them with NULLs; an INNER JOIN should drop them. If the behaviour surprises you, the join is not doing what you thought.

Check for unexpected row multiplication. If a join produces more rows than either input, that is because one side has multiple matches per row. If you did not expect that, your join condition is wrong or your data has duplicates you did not account for. Either way, the fix is to understand why, not to slap DISTINCT on the result.

Verify the count. Before you trust a join, compare the row count to what you expect. Seven customers joined to seven orders should not produce fourteen rows unless one side has multiple matches. Numbers that do not add up are the fastest signal that something is off.

You simply reference the two different column names in the ON clause. There is no requirement that the columns share a name — only that they hold related values.

SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

That example happens to use the same name on both sides, but the following is just as valid:

SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders o ON c.cust_ref = o.customer_key;

Different column names, same result — as long as cust_ref and customer_key hold matching values. The database does not care about the names; it compares the values.

One consequence of different column names: USING and NATURAL JOIN will not work, because both of those shortcuts depend on the column names matching. You are stuck writing the explicit ON clause, which is honestly the better habit anyway. Explicit beats implicit whenever the two are equally easy to write.

It is worth noting that different names for related columns usually mean the tables came from different sources or have been maintained by different teams. That is normal in enterprise data, and it is one of the reasons the schema-on-read approach (as opposed to schema-on-write) is so common in analytics.

Same pattern as joining with any other aggregation: join the tables, then GROUP BY the entity you want to summarise and apply the aggregate functions.

SELECT c.customer_name,
       COUNT(o.order_id)      AS orders,
       SUM(o.order_amount)    AS total_spend,
       AVG(o.order_amount)    AS avg_order,
       MIN(o.order_amount)    AS smallest,
       MAX(o.order_amount)    AS largest
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name
ORDER BY total_spend DESC NULLS LAST;

One row per customer with six summary columns. On our data, Alice Corp shows three orders, a total of 28,500, an average of 9,500, a smallest order of 7,500, and a largest of 12,000. Ethan Supplies shows zero orders and NULL for all the amount columns, because LEFT JOIN kept the customer row but there were no orders to summarise.

The one thing to be deliberate about: when you use LEFT JOIN, customers with no orders get NULL for the aggregate columns, not 0. If you want 0, wrap the aggregate in COALESCE(SUM(o.order_amount), 0). Otherwise the report shows blanks where a reader might expect "zero" and the difference matters.

Aliases are short nicknames you give to tables within a query. They do not change the table's actual name in the database — they exist only for the duration of the query and only for your readability.

SELECT c.customer_name, o.order_id, o.order_amount
FROM   customers c
JOIN   orders o ON c.customer_id = o.customer_id;

Without aliases, the same query would be:

SELECT customers.customer_name, orders.order_id, orders.order_amount
FROM   customers
JOIN   orders ON customers.customer_id = orders.customer_id;

Same result, twice the typing, and it gets worse as more tables join in. With three or four tables and long names, the aliases stop being a convenience and start being the only way to keep the query readable.

Aliases are also mandatory when you self-join. Two references to the same table need two different aliases, or the database cannot tell which column you mean. That is not optional style — it is a syntax requirement.

The convention I use: single letters for tables you reference often in the query, and short descriptive names for anything longer. c for customers, o for orders. Two or three letters when there are many tables and single letters get confusing. The important thing is consistency within a query — do not mix conventions in the same statement.

INNER JOIN is usually faster than any outer join, and the reason is bookkeeping. An inner join only has to find matches. An outer join also has to track which rows did not match and emit them separately with NULLs.

On a small table, that difference is invisible. On a table with millions of rows, the extra work shows up clearly. An OUTER JOIN may need to sort one or both sides to track unmatched rows, and sorting is expensive.

The gap is smallest for LEFT JOIN and RIGHT JOIN, and largest for FULL OUTER JOIN. A full outer join has to track unmatched rows on both sides, so it is the most expensive of the four.

Three things to remember when tuning:

• Indexes reduce the cost of every join type. A well-indexed outer join can beat a poorly-indexed inner join.
• Filtering early reduces the working set for everything downstream. A tight WHERE clause before the join removes rows from both sides.
• Do not use an outer join unless you need it. If your WHERE clause immediately filters out the NULLs the outer join produced, you have done extra work for nothing. Switch to an inner join.

The best way to know which is faster on your data is to measure. Run EXPLAIN ANALYZE (or the equivalent on your database) on both versions and compare the plans. Intuition about join performance is often wrong; the plan is always right.

Outer joins produce NULLs to represent "no matching row on the other side." That is correct data, but it is not always good presentation. Two things you can do about it.

Substitute a display value with COALESCE. This replaces the NULL with something readable in the output without changing the underlying data.

SELECT c.customer_name,
       COALESCE(o.order_id, 0)          AS order_id,
       COALESCE(o.order_status, 'None') AS status
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Customers with no orders now show 0 and "None" instead of NULL. The report reads more naturally, and downstream code does not have to interpret NULL.

Filter out the NULLs if they should not appear. If a customer with no orders should not be in the report at all, use a plain INNER JOIN instead of filtering with WHERE x IS NOT NULL. The inner join drops them at the source, which is both clearer and faster.

Two database-specific alternatives to COALESCE that you will see in existing code: NVL in Oracle and ISNULL in SQL Server. Both do the same thing as COALESCE for the two-argument case. If you are writing new code, use COALESCE — it is standard and works everywhere.

You do not do anything special. The database handles it consistently: NULL in a join column never matches anything. The row is either dropped (INNER JOIN) or kept with NULLs on the other side (LEFT or FULL OUTER).

SELECT c.customer_name, o.order_id
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

If a customer row had NULL in customer_id (unusual, but possible in a staging table), that row would appear once with NULL in every order column. No order would ever join to it, because NULL never equals anything.

When you are debugging and a join produces fewer rows than expected, NULLs in the join column are one of the first things to check. Run a query that counts them:

SELECT COUNT(*)             AS total,
       COUNT(customer_id)   AS with_value,
       COUNT(*) - COUNT(customer_id) AS null_count
FROM   orders;

If null_count is greater than zero, those orders will never match a customer. Whether that is correct or a data quality issue depends on the business rules. Sometimes an order really does not have a customer yet, and the NULL is accurate. Sometimes it means data is broken and a join is quietly hiding the problem.

If you need to join NULLs to each other — which is unusual but occasionally wanted — you cannot do it with =. Instead write the condition as ON (a.col = b.col OR (a.col IS NULL AND b.col IS NULL)). The explicit NULL check makes the intent clear, and both sides are compared as matching when neither has a value. Most databases also support this through a null-safe equality operator (IS NOT DISTINCT FROM in PostgreSQL, <=> in MySQL), but the OR version works everywhere.