Reputation: 25421
I'm selecting a group of records and I want to filter a column in the logic of XOR - IS NOT NULL xor IS NULL.
--basic
SELECT make, model
FROM cars
results
--------
ford taurus
ford (null)
toyota camry
toyota (null)
honda (null)
--Obviously XOR is not a real Oracle operator
--This is what I'm trying to do..
SELECT make, model
FROM cars
WHERE model IS NOT NULL
XOR model IS NULL
results (pulls records where model IS NOT NULL, falling back to NULL if necessary)
--------
ford taurus
toyota camry
honda (null)
Can anyone give me insight on how to achieve the desired result I'm looking for? I'm struggling on this one!
Many thanks!
Upvotes: 9
Views: 9987
Reputation: 57063
I initially upvoted Klas Lindbäck's answer but now I'm wondering whether this instead gives the desired results:
SELECT make, model
FROM Cars
WHERE model IS NOT NULL
UNION
SELECT make, NULL
FROM Cars
MINUS
SELECT make, NULL
FROM cars
WHERE model IS NOT NULL;
Upvotes: 2
Reputation: 33273
SELECT make, model
FROM cars
WHERE model IS NOT NULL
UNION -- Add makes that don't have any specific model
SELECT make, model
FROM cars
WHERE make NOT IN
(SELECT make
FROM cars
WHERE model IS NOT NULL)
Upvotes: 3
Reputation: 425643
SELECT make, model
FROM (
SELECT c.*,
ROW_NUMBER() OVER (PARTITION BY make ORDER BY model NULLS LAST) AS rn
FROM cars c
)
WHERE NOT (rn > 1 AND model IS NULL)
Upvotes: 12