SQL Joins and Set Operations Questions

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.

Direct answer. With a chain of LEFT JOINs, a filter placed on the LAST table's column (even one that looks harmless) can retroactively demote an EARLIER join's outer-join semantics if that filter ends up in the WHERE clause rather than the corresponding ON clause, because WHERE evaluates after the whole chain has already produced its NULL-padded rows.

Structured elaboration. The subtlety with three or more chained outer joins is that "reordering" isn't really about which JOIN keyword comes first in the text, it's about where a downstream filter lives relative to the chain. A LEFT JOIN B LEFT JOIN C is evaluated left to right: first A LEFT JOIN B produces a NULL-padded intermediate result, then THAT result is LEFT JOINed to C. If you then filter on a column from C in the WHERE clause, any row where B (and therefore C) had no match gets excluded, even though the FIRST join (A to B) was written specifically to preserve A's unmatched rows. The fix is the same principle as the two-table case: put the filter in C's ON clause, not in WHERE, so it only constrains which C rows are eligible to match, without discarding upstream rows that legitimately have no C match at all.

Worked example. a_tbl(id): 1, 2. b_tbl(id, a_id): (10, a_id=1). c_tbl(id, b_id, flag): (100, b_id=10, flag='X'). Row a_id=2 has no matching b row at all.

-- buggy: filtering c.flag in WHERE demotes the WHOLE chain back toward an inner join
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id
WHERE c.flag = 'X';
-- returns only (1, 10, 100). Row a.id=2 vanishes entirely, even though the
-- LEFT JOIN to b_tbl was written specifically to keep A-rows with no B match.

-- fixed: move the flag condition into c's own ON clause
SELECT a.id, b.id AS b_id, c.id AS c_id
FROM a_tbl a
LEFT JOIN b_tbl b ON a.id = b.a_id
LEFT JOIN c_tbl c ON b.id = c.b_id AND c.flag = 'X'
ORDER BY a.id;
-- returns (1, 10, 100) AND (2, NULL, NULL). Row a.id=2 is preserved, as intended.

Trade-offs and pitfalls. The practical fix for readability, once a chain gets to three or more joins, is often to restructure the later stages as a derived table or CTE that pre-filters C on its own terms BEFORE joining it to the chain, rather than relying on everyone downstream to remember which conditions belong in which ON clause. That also makes the intended semantics explicit at a glance, instead of implicit in clause placement that's easy to get subtly wrong during a later edit.

Direct answer. Either apply a transformation to one or both sides inside the join condition (truncating a timestamp to a date, normalizing case or whitespace, converting a timezone) so the values line up at query time, or normalize the mismatched representation ahead of time (a computed column, a cleaned staging table) so the join itself can stay a plain equality.

Structured elaboration. Joining on an expression (ON DATE_TRUNC('day', a.event_time) = c.day) works correctly but has a real performance cost: most engines can't use a plain index on the untransformed column to satisfy a condition wrapped in a function, since the index is built on the raw stored value, not on the transformed one. Normalizing the data ahead of time (storing a pre-truncated date column, pre-lowercasing an identifier, converting to a single canonical timezone at ingest) avoids that cost by making the join condition a plain equality again, at the cost of doing (and maintaining) that normalization work upstream instead of at query time.

Worked example. app_a(user_id, event_time, kind): (1, '2025-01-01 10:00:00', 'view'). calendar(day): '2025-01-01', '2025-01-02'.

SELECT a.user_id, a.kind, c.day
FROM app_a a
JOIN calendar c ON CAST(a.event_time AS DATE) = c.day;

Result: (1, 'view', '2025-01-01'). The timestamp 2025-01-01 10:00:00 correctly matches the calendar day 2025-01-01 once truncated to a plain date for the comparison.

Trade-offs and pitfalls. Joining on an expression is the right immediate fix and is fine at modest scale, but if this join runs frequently against a large, fast-growing table, the inability to use a plain index on the raw column becomes a real cost; many engines support a functional index (an index built directly on the expression, e.g. CREATE INDEX ON app_a (CAST(event_time AS DATE))) as a middle ground that restores efficient lookups without requiring you to add and maintain a separate normalized column. Timezone mismatches are a sharper version of the same problem: silently comparing a UTC timestamp to a naive local timestamp without an explicit conversion doesn't error, it just quietly matches the wrong rows near day boundaries, which is far more dangerous than the truncation case because the mismatch can be off by hours, not just formatting.

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 self-join treats one table as if it were two: you join employees to itself, once playing the role of the employee and once playing the role of that employee's manager, matching manager_id on one side to employee_id on the other.

Structured elaboration. A LEFT JOIN (not INNER) is essential here, because the top of the org has manager_id = NULL, and an INNER JOIN would silently drop the top-level employee (or anyone else with no manager) since NULL matches nothing. The pattern generalizes: give the table two different aliases in the FROM clause, and join those aliases to each other on whatever relationship the rows encode (here, "reports to").

Worked example. employees(1,'Alice',manager_id=NULL), (2,'Bob',manager_id=1), (3,'Cara',manager_id=1), (4,'Dee',manager_id=2).

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
ORDER BY e.employee_id;

Result: (Alice, NULL), (Bob, Alice), (Cara, Alice), (Dee, Bob).

Extending one level up (manager's manager) is just a second self-join on the same table with a third alias:

SELECT e.name AS employee_name, m.name AS manager_name, gm.name AS manager_of_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN employees gm ON m.manager_id = gm.employee_id
ORDER BY e.employee_id;

Result: (Alice, NULL, NULL), (Bob, Alice, NULL), (Cara, Alice, NULL), (Dee, Bob, Alice). Dee's manager is Bob, and Bob's manager is Alice, so Dee's manager-of-manager column correctly resolves to Alice.

Trade-offs and pitfalls. Chaining self-joins works cleanly for a fixed, small number of levels (one or two), because each level is just one more LEFT JOIN. It stops scaling the moment the number of levels is unknown or the hierarchy can be arbitrarily deep, at which point you need a recursive CTE instead, which walks the hierarchy level by level until it runs out of rows rather than hard-coding a fixed number of self-joins. The self-join approach is also easy to get wrong on the join direction: joining e.manager_id = m.employee_id gets you the employee's manager; joining the other way around gets you the manager's direct reports, which is a different (equally valid) question.