SQL Joins and Set Operations Questions

Direct answer. A RIGHT JOIN keeps every row from the right-hand table and NULL-pads the left side where there's no match, the exact mirror of a LEFT JOIN; swapping which table is written first and changing RIGHT JOIN to LEFT JOIN produces an identical result, which is why many teams simply avoid RIGHT JOIN and standardize on LEFT JOIN for everything.

Structured elaboration. Because RIGHT JOIN and LEFT JOIN are true mirrors of each other, any RIGHT JOIN can be rewritten by physically swapping the two table references and changing the keyword, with no change in meaning at all. Teams that ban RIGHT JOIN do it for pure readability and consistency: a codebase where every join is either INNER or LEFT is easier to scan (a reader always knows "the important, must-appear table comes first"), and RIGHT JOIN's rarity means readers spend an extra beat parsing it every time it shows up, even though it's perfectly valid SQL.

Worked example. employees(employee_id, name, dept_id): (1, 'Alice', 10). departments(dept_id, dept_name): (10, 'Eng'), (20, 'Sales').

-- RIGHT JOIN: keep every department, even ones with no employees
SELECT e.name, d.dept_name
FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

-- identical result via LEFT JOIN, with the tables swapped
SELECT e.name, d.dept_name
FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id;
-- returns (Alice, Eng), (NULL, Sales)

Both queries return the identical two rows: Sales has no employees, so it appears with a NULL name either way.

Trade-offs and pitfalls. There's no correctness or performance difference between the two forms, so this is purely a house-style convention, not a technical constraint; some optimizers may even normalize a RIGHT JOIN into the equivalent LEFT JOIN internally before planning it. The main practical risk with RIGHT JOIN isn't the join itself, it's that a query mixing LEFT and RIGHT JOINs across a longer chain becomes genuinely hard for a reader to trace, since they can no longer assume "the must-preserve table is always written first"; standardizing on LEFT JOIN everywhere removes that ambiguity by convention rather than by any property of the SQL itself.

Direct answer. A FULL OUTER JOIN keeps every row from both sides, matched where possible and NULL-padded where not, which makes it the natural tool for reconciliation: it surfaces rows that exist on only one side just as readily as ones that mismatch on both.

Structured elaboration. Build the reconciliation with COALESCE to pick a display key regardless of which side it came from, then classify each row with a CASE expression that checks, in order: is the key missing from one side entirely (only_a / only_b), and if present on both, do the values actually match (match / mismatch).

Worked example. sales_a(date, revenue): ('2025-01-01', 100), ('2025-01-02', 80). sales_b(date, revenue): ('2025-01-01', 100), ('2025-01-03', 60).

SELECT COALESCE(a.date, b.date) AS date,
       a.revenue AS revenue_a, b.revenue AS revenue_b,
       COALESCE(a.revenue,0) - COALESCE(b.revenue,0) AS delta,
       CASE
         WHEN a.date IS NULL THEN 'only_b'
         WHEN b.date IS NULL THEN 'only_a'
         WHEN a.revenue = b.revenue THEN 'match'
         ELSE 'mismatch'
       END AS reconciliation_status
FROM sales_a a
FULL OUTER JOIN sales_b b ON a.date = b.date
ORDER BY date;

Result: ('2025-01-01', 100, 100, 0, 'match'), ('2025-01-02', 80, NULL, 80, 'only_a'), ('2025-01-03', NULL, 60, -60, 'only_b'). Every discrepancy type is visible in one pass: the shared date matches exactly, and each system's unique date is flagged distinctly rather than silently dropped.

Trade-offs and pitfalls. Not every engine supports FULL OUTER JOIN natively (older MySQL versions don't); you can emulate it as a LEFT JOIN UNION a RIGHT JOIN (or, more simply, a LEFT JOIN UNION an anti-joined right-side-only query), which produces the identical result at the cost of scanning each side twice. The pattern also extends past two sources: reconciling three or four systems at once is usually cleaner as a chain of FULL OUTER JOINs (A FULL OUTER JOIN B FULL OUTER JOIN C, using COALESCE to unify the key at each step) rather than trying to write one giant CASE expression across all combinations by hand, since the number of "only in X" cases grows combinatorially with the number of sources.

Direct answer. Replace the comma-separated FROM list and its WHERE-clause join condition with an explicit JOIN ... ON, which makes the join relationship visible at the FROM clause itself rather than buried among unrelated filter conditions in WHERE.

Structured elaboration. The old comma style puts the tables to combine and the condition that relates them in two completely different places (FROM lists the tables, WHERE happens to contain the predicate that relates them), and SQL has no way to tell "this WHERE condition is the join key" apart from "this WHERE condition is an ordinary filter." If someone editing the query later removes or mis-edits that one WHERE clause (thinking it's just a filter, since nothing marks it as load-bearing), the query silently becomes a full Cartesian product between the two tables instead of erroring.

Worked example. customers(customer_id, name): (1, 'Alice'). orders(order_id, customer_id, amount): (100, 1, 150).

-- old style: the join condition is indistinguishable from an ordinary filter
SELECT c.customer_id, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id AND o.amount > 100;

-- explicit JOIN: the relationship is visible in the FROM clause itself
SELECT c.customer_id, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.amount > 100;

Both return the identical result here: (1, 100). The explicit version makes it obvious, just from reading FROM/JOIN, which condition is the relationship between the tables and which (the amount filter) is an independent business rule.

Trade-offs and pitfalls. The silent-Cartesian-product risk is the main practical reason most style guides ban the comma style outright, not just a stylistic preference: with an explicit JOIN, forgetting the ON clause entirely is at minimum a jarring omission that stands out on review (and, in most engines, doesn't even parse without one, unless you deliberately wrote CROSS JOIN), whereas losing one WHERE condition among several others reads as a small, easy-to-miss diff. The two forms are logically equivalent when written correctly, so this is entirely a defensive-coding argument, not a performance or correctness difference between the two syntaxes as such.

Direct answer. All three give the same logical answer for "rows in the first set that have no counterpart in the second," but they differ in NULL-safety and how explicitly they handle duplicate rows: NOT EXISTS is the safest default, the LEFT JOIN anti-join is usually just as correct and often the most readable, and EXCEPT is the most concise but silently dedups its output, which is a behavior you need to want.

Structured elaboration.

• LEFT JOIN anti-join: LEFT JOIN ... WHERE right.key IS NULL is NULL-safe with respect to the join KEY itself (a NULL key on the right side simply never matches, which is what you want), and it reads clearly to anyone familiar with joins.
• NOT EXISTS: correlated, checks row-by-row whether a match exists, and is immune by construction to the NOT IN + NULL trap because it never compares against a literal list.
• EXCEPT: compares whole rows between two result sets and automatically deduplicates its output; if the left set has the same row twice, EXCEPT returns it at most once, which the other two approaches do NOT do (they'd preserve however many times it appeared on the left).

Worked example. q1(customer_id): 1, 2, 3 (this month's active customers). q2(customer_id): 2, 3 (customers who also had activity last month). The question: which customers are new to q1 this month, i.e., in q1 but not q2?

-- (a) LEFT JOIN anti-join
SELECT q1.customer_id FROM q1
LEFT JOIN q2 ON q1.customer_id = q2.customer_id
WHERE q2.customer_id IS NULL;
-- returns 1

-- (b) NOT EXISTS
SELECT customer_id FROM q1
WHERE NOT EXISTS (SELECT 1 FROM q2 WHERE q2.customer_id = q1.customer_id);
-- returns 1

-- (c) EXCEPT
SELECT customer_id FROM q1
EXCEPT
SELECT customer_id FROM q2;
-- returns 1

All three agree here. The divergence shows up once q1 has a duplicate: if q1 also contained a second row for customer_id = 1, (a) and (b) would both return TWO rows for customer 1, while (c) would still return exactly one, because EXCEPT dedups.

Trade-offs and pitfalls. Reach for NOT EXISTS as the safe general default, especially when you're not certain the tables are duplicate-free. Reach for EXCEPT when you specifically want a deduplicated result and the two sides are naturally same-shaped snapshots (comparing two full exports of the same entity). Avoid NOT IN against a subquery entirely unless you can guarantee the subquery's column is never NULL. Performance-wise, all three typically compile to similar plans on well-indexed columns, so correctness and readability, not raw speed, should usually decide between them.

Direct answer. JOIN ... USING(column) requires the join column to have the identical name on both sides and produces a SINGLE, unqualified column of that name in the output, whereas JOIN ... ON a.column = b.column keeps both sides' columns separately in the result, each still needing its table qualifier (or an alias) to reference unambiguously afterward.

Structured elaboration. USING is really syntactic sugar for the common case where both tables happen to name their shared key identically; it saves you writing out the equality condition, and as a bonus it collapses the two now-redundant columns into one in the output, which you can then reference by its bare name in SELECT, WHERE, or ORDER BY without any table prefix at all. ON is more general (the columns being compared don't need matching names, or even a single column, since ON can express arbitrarily complex conditions) but leaves both sides' columns present and separately addressable in the result.

Worked example. orders(product_id, order_id): (100, 1). products(product_id, name): (100, 'Widget').

SELECT product_id, name
FROM orders JOIN products USING (product_id)
ORDER BY product_id;

Result: (100, 'Widget'). Note the SELECT and ORDER BY both reference product_id unqualified; with USING, there's exactly one product_id column in the result, not two, so no table-alias qualification is needed or even permitted for that specific column.

Trade-offs and pitfalls. USING only helps when the column names genuinely match, which won't be true if the two tables were designed independently (a foreign key column named differently from the primary key it references is common, and USING simply can't apply there; you're back to ON). Support and exact behavior for NATURAL JOIN (an even more implicit cousin of USING that joins on every identically-named column automatically) varies more across dialects, and most style guides discourage it for the same reason the comma-style join is discouraged: it's implicit about what it's doing, and a schema change (someone adds a new identically-named column to either table) can silently change which columns a NATURAL JOIN uses without anyone touching the query itself.