Given two tables with columns id and value, you want to find common rows but only where value is not NULL. Which query correctly applies this condition using INTERSECT?
hard📝 Application Q9 of 15
SQL - Set Operations
Given two tables with columns id and value, you want to find common rows but only where value is not NULL. Which query correctly applies this condition using INTERSECT?
ASELECT id, value FROM Table1 WHERE value IS NOT NULL EXCEPT SELECT id, value FROM Table2 WHERE value IS NOT NULL;
BSELECT id, value FROM Table1 WHERE value IS NOT NULL INTERSECT SELECT id, value FROM Table2 WHERE value IS NOT NULL;
CSELECT id, value FROM Table1 WHERE value IS NOT NULL UNION SELECT id, value FROM Table2 WHERE value IS NOT NULL;
DSELECT id, value FROM Table1 INTERSECT SELECT id, value FROM Table2 WHERE value IS NOT NULL;
Step-by-Step Solution
Solution:
Step 1: Apply filter on both tables before INTERSECT
We must exclude NULL values in both queries to get correct common rows.
Step 2: Use INTERSECT on filtered results
INTERSECT then returns rows common to both filtered sets.
Final Answer:
SELECT id, value FROM Table1 WHERE value IS NOT NULL INTERSECT SELECT id, value FROM Table2 WHERE value IS NOT NULL; -> Option B
Quick Check:
Filter before INTERSECT to exclude NULLs [OK]
Quick Trick:Filter rows before INTERSECT to exclude unwanted data [OK]
Common Mistakes:
MISTAKES
Filtering only one table
Using UNION instead of INTERSECT
Filtering after INTERSECT
Master "Set Operations" in SQL
9 interactive learning modes - each teaches the same concept differently
