RIGHT JOIN vs FULL OUTER JOIN

RIGHT JOIN is the mirror of LEFT JOIN: it preserves every row from the right table and fills the left-hand columns with NULLs where there is no match. FULL OUTER JOIN preserves the rows from both sides — it is the union of the LEFT JOIN and the RIGHT JOIN behaviours, so unmatched rows appear with NULLs on the opposite side.

Support is the interesting part. LEFT JOIN has been in the SQL standard and in every major engine for decades. RIGHT JOIN is standard too, but its implementation arrived late in some engines — SQLite only added it in version 3.39, and the playground these examples run on is 3.36, so a RIGHT JOIN will not parse there. FULL OUTER JOIN is standard SQL and MySQL, PostgreSQL, Oracle and SQL Server all have it, but SQLite still does not, as of 3.39.

The good news is that neither keyword is necessary. A RIGHT JOIN is always expressible as a LEFT JOIN with the tables swapped, and a FULL OUTER JOIN is always expressible as the union of a LEFT JOIN and an anti-join. Since the LEFT JOIN forms run everywhere, that is the portable answer, and it also makes the semantics explicit rather than implicit.

One more practical note about FULL OUTER JOIN: it is genuinely rare in application queries. A report that says "show me every customer and every order" is usually better served by two separate queries or by a UNION of a LEFT JOIN and an anti-join, because the FULL JOIN makes it easy to forget which columns are nullable. But when you do need it, and the engine has it, it is the clearest possible statement of the requirement.

Swapping the two tables around the LEFT JOIN gives exactly the RIGHT JOIN semantics: every row of the right table survives, and unmatched rows get NULLs for the left table's columns.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 99, 60.00);

-- RIGHT JOIN written as a LEFT JOIN with the tables swapped.
SELECT o.id AS order_id, o.customer_id, o.amount, c.name
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.id;

-- The RIGHT JOIN spelling, for engines that support it:
-- SELECT o.id AS order_id, o.customer_id, o.amount, c.name
-- FROM customers AS c
-- RIGHT JOIN orders AS o ON o.customer_id = c.id
-- ORDER BY o.id;

Four rows come back: orders 1 and 2 with the name Ada Lovelace, order 3 with Grace Hopper, and order 4 with a NULL name — because that order references customer 99, who is not in the customers table.

Note what the LEFT JOIN version guarantees: every row of orders appears, exactly once or more, and the customers table is the one that can lose rows. That is the same guarantee the RIGHT JOIN gives, and it is why the rewrite is exact rather than approximate.

The commented-out version is the same statement written with the keyword. It is identical in result on MySQL, PostgreSQL and SQL Server, and it works in SQLite only from version 3.39 onwards.

For an interview, the useful framing is that LEFT JOIN is the primitive and the others are conveniences built on top of it. Once you can mentally swap a RIGHT JOIN into a LEFT JOIN, you can also see why a FULL OUTER JOIN is a LEFT JOIN plus an anti-join — the unmatched rows from the right side are exactly the rows the LEFT JOIN could not match, and a second branch picks them up.

SQLite has no FULL OUTER JOIN, so you build it from two LEFT JOINs. The second branch must be filtered to the rows the first branch could not match, otherwise the matched rows appear twice.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25),
  (4, 99, 60.00);

-- The correct emulation: the second branch only contributes unmatched rows.
SELECT c.id AS customer_id, c.name, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
UNION ALL
SELECT c.id, c.name, o.id, o.amount
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL
ORDER BY customer_id, order_id;

Four rows come back: customer 1 with orders 1 and 2, customer 2 with order 3, and customer 99 with a NULL name and order 4. Every customer and every order is represented exactly once.

The WHERE clause in the second branch is the whole trick. Without it, the second branch returns every order again — including the three that matched a customer — and the UNION ALL stacks them on top of the first branch's rows, giving seven rows instead of four. The matched rows would appear twice, with identical values, because the first branch found them from the customer side and the second branch found them from the order side.

The guard WHERE c.id IS NULL identifies exactly the rows the first branch could not produce: orders whose customer does not exist. Because the customer_id column is declared NOT NULL here, a NULL customer_id would not be possible; if it were nullable, an order with a NULL customer_id would also be unmatched, and the IS NULL test on the joined customer id would still catch it correctly.

One more thing to note: the output has nullable columns on both sides, and that is inherent to a full outer join. Any consumer of this result needs to handle NULL in customer_id and NULL in name. If you find yourself needing those nulls frequently, two separate queries are usually easier to work with than one wide, nullable result set.