John Strickler
John Strickler

Reputation: 25421

Oracle Sql NOT NULL xor NULL?

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

Answers (3)

onedaywhen
onedaywhen

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

Klas Lindbäck
Klas Lindbäck

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

Quassnoi
Quassnoi

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

Related Questions