Question bank — Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

Usually but not guaranteed. INNER is a subset of the Cartesian product, so it can never have more rows — but if the ON condition matches every pair, INNER equals CROSS (same count). If one table is empty, both are 0.

False. They preserve different tables. A LEFT JOIN B keeps all of A padding B with NULL; the reverse keeps all of B. They agree only when every row on both sides has a match (i.e. the join is effectively INNER).

Almost: it is the union (LEFT ∪ RIGHT), not a plain concatenation. The matched rows appear in both LEFT and RIGHT results, so a naive concat would double-count the INNER rows; UNION removes that overlap.

False. NULL means "unknown", and NULL = NULL evaluates to unknown, not true — so Cara matches nothing and INNER drops her. Only outer joins can retain a NULL-keyed row.

True. A RIGHT JOIN B ≡ B LEFT JOIN A — you just swap the table order. This is why many style guides ban RIGHT joins: every query can read left-to-right.

False. There is no SELF JOIN keyword. It is an ordinary join where the same table appears twice under two different aliases (Employees a JOIN Employees b).

True. Each extra AND condition is an additional filter on the Cartesian product; a stricter predicate can never let more rows survive.

False. You can add a WHERE afterward — and a CROSS JOIN followed by a WHERE matching condition is identical to an INNER JOIN. The distinction is syntactic, not semantic.

No, it silently becomes INNER. Padded rows have D.dept_name = NULL, and NULL = 'Sales' is not true, so WHERE deletes every unmatched left row. Move the condition into ON to keep LEFT behaviour.

An accidental CROSS JOIN (Cartesian explosion). The old comma syntax lets you forget the join condition; you get $m\times n$ rows instead of the matched few. Prefer explicit JOIN ... ON.

==Missing a.emp_id < b.emp_id==. Without it each pair appears twice (A,B and B,A) and every employee pairs with themselves (A,A). The strict inequality yields each unordered distinct pair once.

Backwards. This finds unmatched employees (the padded NULLs), i.e. the anti-join. To find matched employees you'd test D.dept_id IS NOT NULL or use INNER JOIN.

No. INNER drops employees with no matching department (Bilal, Cara), so the count is too low. To count all employees, COUNT(*) a LEFT JOIN from Employees, or just count Employees directly.

Risky. The OR ... IS NULL makes every NULL-dept row match every department row, inflating results. To simply retain Cara once, use a LEFT JOIN instead.

ON filters before padding; WHERE filters after. ON decides which right rows are eligible to match; unmatched left rows still get NULL-padded and kept. WHERE runs on the finished result, and a NULL fails the equality test, so the padded row is removed.

Because NULL means unknown value, and comparing two unknowns yields unknown (three-valued logic), which is not TRUE. Join conditions keep only rows where the predicate is TRUE, so NULLs never join. See NULL semantics in SQL.

Because normalization splits data to avoid duplication and update anomalies; a department's name is stored once, not on every employee row. Joins reconnect that split-apart data at query time. See Normalization.

The relational-algebra meaning is ${\sigma }_{\theta }(E\times D)$ — filter the product. The optimizer uses indexes and hash tables to compute the same answer efficiently, but the semantics are defined by the product-then-filter. See Relational Algebra.

A foreign key guarantees every child value exists in the parent, so no child row is ever unmatched. Joining child→parent, INNER and LEFT return the same rows (nothing to pad). The reverse direction can still have orphan parents.

INNER can drop rows early and reorder the tables freely to use the smaller side or best index. LEFT must preserve every left row, constraining the optimizer's freedom and forcing NULL-padding work.

Without an index, matching rows means scanning the whole other table per row (nested loop, $O(mn)$). An index on the join key lets the engine look up matches directly, turning the search into roughly $O(m\log n)$ or a hash probe.

All employee rows, every right column NULL. LEFT preserves the left table regardless; with no possible matches, every row is padded. INNER, by contrast, would return zero rows.

Zero rows. The Cartesian product with an empty table is empty ($m\times 0=0$), and filtering an empty set stays empty. Same for CROSS.

Two rows. Joins are many-to-one/many-to-many: each matching pair is emitted. One department row matched by two employees yields two output rows — the count multiplies, it doesn't collapse.

No. Her key is NULL, and NULL = NULL is unknown, so she matches no row — not even her own. NULL-keyed rows silently disappear from equality self-joins.

Four rows: Asha–Sales (matched), Bilal–NULL, Cara–NULL (left orphans, right padded), NULL–Legal (right orphan, left padded). It is LEFT ∪ RIGHT with the single overlap counted once.

Every physical left row is paired with every right row — duplicates are preserved and multiplied. CROSS operates on rows, not distinct values, so 2 identical left rows × 3 right rows still gives 6 output rows.

The NULL-padded rows still participate in grouping, but aggregates like COUNT(right_column) skip NULLs while COUNT(*) counts the padded row. This distinction is a classic trap — see GROUP BY and Aggregation.