Consider two queries: 1) SELECT id, name FROM table1 WHERE active = 1 2) SELECT id, name FROM table2 WHERE active = 1 How would you combine these to get all active users sorted by name descending, including duplicates?
ASELECT id, name FROM table1 WHERE active = 1 UNION SELECT id, name FROM table2 WHERE active = 1 ORDER BY name DESC;
BSELECT id, name FROM table1 WHERE active = 1 UNION ALL SELECT id, name FROM table2 WHERE active = 1 ORDER BY name DESC;
CSELECT id, name FROM table1 WHERE active = 1 INTERSECT SELECT id, name FROM table2 WHERE active = 1 ORDER BY name DESC;
DSELECT id, name FROM table1 WHERE active = 1 EXCEPT SELECT id, name FROM table2 WHERE active = 1 ORDER BY name DESC;
Step-by-Step Solution
Solution:
Step 1: Understand requirement for duplicates
Including duplicates means use UNION ALL, not UNION.
Step 2: Combine queries and sort descending by name
Use ORDER BY name DESC after UNION ALL.
Final Answer:
Use UNION ALL with ORDER BY name DESC -> Option B
Quick Check:
UNION ALL keeps duplicates; ORDER BY sorts descending [OK]
Quick Trick:Use UNION ALL to keep duplicates; ORDER BY after union [OK]
Common Mistakes:
MISTAKES
Using UNION instead of UNION ALL
Using INTERSECT or EXCEPT incorrectly
Placing ORDER BY before UNION
Master "Set Operations" in SQL
9 interactive learning modes - each teaches the same concept differently
