Joining Multiple Tables Flashcards

Which set of rows does the difference between a LEFT JOIN and an INNER JOIN consist of?

Answer: Left-table rows with no match in the right table

A LEFT JOIN adds the unmatched left rows that an INNER JOIN would drop.

In a self join to find pairs of employees in the same department, what prevents pairing a row with itself?

Answer: A condition like e1.id <> e2.id

Adding e1.id <> e2.id excludes the trivial self-pairing.

Joining on an indexed column versus a non-indexed column primarily affects what?

Answer: Query performance

Indexes speed up join lookups but do not change the result set.

What is the result of joining a table to an empty table with an INNER JOIN?

Answer: Zero rows

With no matching rows available, an INNER JOIN returns nothing.

Which approach lets you join a table to the results of a subquery?

Answer: JOIN (SELECT ...) AS sub ON ...

A derived table is a subquery in the FROM clause given an alias and joined normally.

When chaining LEFT JOINs across A, B, and C, what happens to A's rows if B matches but C does not?

Answer: A's rows remain, with C's columns NULL

Each LEFT JOIN preserves the prior result's rows, NULL-filling the unmatched later table.

To combine results of two separate SELECTs row-wise (stacking) rather than joining columns, you use what?

Answer: UNION

UNION stacks rows from two queries; joins instead combine columns by matching rows.