Given two tables: Writers(writer_id, writer_name) Publications(pub_id, title, writer_id) Which SQL query will list all writers along with their publications, including those writers who have no publications?
ASELECT Writers.writer_name, Publications.title FROM Writers LEFT JOIN Publications ON Writers.writer_id = Publications.writer_id;
BSELECT Writers.writer_name, Publications.title FROM Writers INNER JOIN Publications ON Writers.writer_id = Publications.writer_id;
CSELECT Writers.writer_name, Publications.title FROM Publications RIGHT JOIN Writers ON Publications.writer_id = Writers.writer_id;
DSELECT Writers.writer_name, Publications.title FROM Writers CROSS JOIN Publications;
Step-by-Step Solution
Solution:
Step 1: Identify requirement
We want all writers, including those without publications.
Step 2: Choose correct JOIN type
LEFT JOIN returns all rows from the left table (Writers) and matching rows from Publications; unmatched rows show NULL.
Step 3: Analyze options
SELECT Writers.writer_name, Publications.title FROM Writers LEFT JOIN Publications ON Writers.writer_id = Publications.writer_id; uses LEFT JOIN correctly. SELECT Writers.writer_name, Publications.title FROM Writers INNER JOIN Publications ON Writers.writer_id = Publications.writer_id; excludes writers without publications (INNER JOIN). SELECT Writers.writer_name, Publications.title FROM Publications RIGHT JOIN Writers ON Publications.writer_id = Writers.writer_id; uses RIGHT JOIN but reverses tables, which is less clear. SELECT Writers.writer_name, Publications.title FROM Writers CROSS JOIN Publications; produces Cartesian product, not desired.
Final Answer:
SELECT Writers.writer_name, Publications.title FROM Writers LEFT JOIN Publications ON Writers.writer_id = Publications.writer_id; -> Option A
Quick Check:
LEFT JOIN includes all left table rows [OK]
Quick Trick:Use LEFT JOIN to include all left table rows [OK]
Common Mistakes:
MISTAKES
Using INNER JOIN excludes unmatched rows
Using CROSS JOIN creates unwanted combinations
Confusing LEFT JOIN with RIGHT JOIN
Master "INNER JOIN" in SQL
9 interactive learning modes - each teaches the same concept differently
