SQL Joins and Set Operations Questions

Direct answer. An equi-join matches rows using equality on the join columns (a.key = b.key); a non-equi join matches using any other comparison, inequality, range membership, or BETWEEN, most commonly to place a value into the bucket or tier it falls within.

Structured elaboration. The clearest real-world case is tiering: given a transaction amount and a table of tiers each defined by a min and max bound, you're not looking for an EXACT match on amount, you're looking for the tier whose range CONTAINS that amount, which is inherently a range (non-equi) condition rather than an equality.

Worked example. pricing_tiers(tier_name, min_amount, max_amount): ('bronze', 0, 100), ('silver', 100, 500), ('gold', 500, 999999999).

SELECT t.amount, tier.tier_name
FROM transactions t
JOIN pricing_tiers tier
  ON t.amount >= tier.min_amount AND t.amount < tier.max_amount;

A transaction of amount=250 matches the 'silver' row because 250 falls between 100 and 500; there's no single equality condition that could express this relationship.

Trade-offs and pitfalls. Equi-joins are what B-tree indexes are built for: an index on the join column supports fast equality lookups directly. A non-equi (range) join can't use a simple equality lookup the same way; the optimizer typically has to do more work, for example scanning or index-range-scanning the smaller side of the comparison per row of the larger side, which scales worse than an equi-join as both tables grow. For a small, mostly-static lookup table like pricing tiers this rarely matters in practice, but the same non-equi pattern applied to two large, fast-growing tables (matching every event to the marketing campaign whose date range it falls within, say) is exactly where you'd want to think carefully about indexing strategy for the range columns, which is its own deeper topic.

Direct answer. UNION ALL simply stacks the rows from both queries, including duplicates. UNION does the same stacking and then performs an implicit dedup pass over the combined result, which costs extra work and is only worth paying for when you actually need it.

Structured elaboration. Reach for UNION ALL by default. Only add the dedup pass (plain UNION) when duplicate rows would genuinely be wrong for what you're building, for example combining exports from two systems that are known to sometimes report the exact same event twice. The cost of the dedup isn't just conceptual: on a large combined result, UNION effectively does a sort or hash pass over every column to find duplicates, whereas UNION ALL does none of that.

Worked example. jan_sales(customer_id, amount): (1,100), (2,50). feb_sales(customer_id, amount): (2,50), (3,80). Note customer 2 appears in both months with the identical amount (say, a reporting overlap).

SELECT * FROM jan_sales
UNION ALL
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 4 rows: (1,100), (2,50), (2,50), (3,80). Customer 2 appears twice.

SELECT * FROM jan_sales
UNION
SELECT * FROM feb_sales
ORDER BY customer_id;
-- returns 3 rows: (1,100), (2,50), (3,80). The duplicate (2,50) row got collapsed to one.

Trade-offs and pitfalls. The risk with defaulting to UNION ALL is that if the two overlapping sources genuinely CAN produce the exact same logical row twice (as in this example), you'll silently double-count it in any downstream SUM. The risk with defaulting to UNION is the performance cost at scale, and a subtler correctness trap: UNION's dedup works on the WHOLE row, so two rows that are conceptually the same event but differ in even one column (a slightly different timestamp, an extra trailing space) won't be deduplicated at all, giving a false sense of safety. If you need dedup on a specific business key rather than the whole row, UNION ALL followed by an explicit GROUP BY or ROW_NUMBER() is usually the more precise tool.

Direct answer. Use an INNER JOIN: it naturally keeps exactly the employees who have a matching row in departments, and drops the rest, which is exactly "only employees with a department assigned."

Structured elaboration. The choice of join type should follow directly from the business rule, not the other way around. Here the rule is "only show employees who have a department," which is precisely an INNER JOIN's default behavior: it requires a match on both sides. If the rule had instead been "show every employee, and their department if they have one," a LEFT JOIN would be the correct choice, and you'd typically wrap the department column in COALESCE to show something like 'Unassigned' instead of a bare NULL.

Worked example. employees(1,'Alice',10), (2,'Bob',20), (3,'Cara',NULL). departments(10,'Engineering'), (20,'Sales').

SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.name;

Result: (Alice, Engineering), (Bob, Sales). Cara is excluded because her dept_id is NULL and NULL never equals anything, including another NULL, so she has no match to join against.

Trade-offs and pitfalls. The pitfall is doing this the other way around: writing a LEFT JOIN and then adding WHERE d.dept_id IS NOT NULL to filter out the unassigned employees. That works, but it's needless indirection: an INNER JOIN already expresses the intent directly and lets the optimizer reason about it more simply. Watch for the reverse mistake too: if a later requirement changes to "show all employees including unassigned ones," swapping to LEFT JOIN is a one-line change, but forgetting to also swap any WHERE clause that assumed non-NULL department columns is a classic way to silently reintroduce the INNER JOIN behavior.

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. 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.