Is it possible to have a nested where and and clause in oracle sql?-CodePudding

I've tried search for this and I'm not sure I'm using the correct terminology, but is it possible to have a nested WHERE AND clause?:

select id from tabel_1 where first_name = 'Jay' and last_name = 'McDonald' and (age = 18 and hair_colour = black)

So the nested AND would "and (age != 18 and hair_colour != black)" so you could have:

first_name = 'Jay' evaluate to true

last_name = 'McDonald' evaluate to true

(age = 18 and hair_colour = black) only evaluate to true if both age and hair_colour are true

With the idea that only ids with both 18 and black are exclude, but not ids with just black or just 18

CodePudding user response：

(age = 18 and hair_colour = black) only evaluate to true if both age and hair_colour are true

This already happens. You don't even need the parentheses.

only ids with both 18 and black are exclude, but not ids with just black or just 18

You want something more like this:

AND NOT (age = 18 and hair_colour = 'black')

But you should also look at DeMorgan's Law, which allows us to rewrite it like this:

AND (age != 18 OR hair_colour != 'black')

But let's say you also want to require one of those to be TRUE: that is, we want an exclusive OR (XOR):

AND (age  = 18 OR hair_colour  = 'black')
AND (age != 18 OR hair_colour != 'black')

CodePudding user response：

select id 
from tabel_1 
where first_name = 'Jay' 
and last_name = 'McDonald' 
and (
  age = 18 OR 
  hair_colour = black
)