CODEWITHSUNDEEP
sqloracle-database
Mr 2017
Mr 2017

Reputation: 113

NOT IN with one condition in Oracle

I'd like to double-check this:

If you're using a WHERE clause with the NOT IN condition, and you only have one single criteria, do you have to leave that criteria in brackets?

So in the clause below if Y and Z were removed, would X have to remain in brackets (see the second WHERE NOT IN CLAUSE below).

WHERE LastEditedBy NOT IN ('X','Y','Z') 

WHERE LastEditedBy NOT IN ('X')

Upvotes: 0

Views: 62

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The syntax diagram in the documentation shows the parentheses as required, but it does work without them:

select * from dual where dummy not in 'Y';

DUMMY
-----
X

and

select * from dual where dummy in 'X';

DUMMY
-----
X

both return data.

db<>fiddle

Including the parentheses anyway doesn't really cost you anything, keeps you in line with the official syntax (and as @jarlh noted, relying on undocumented behaviour isn't wise), will perhaps be a bit less confusing for someone else looking at your code, and will make future maintenance slightly easier if you need to add more values to the list later.

Upvotes: 6

Related Questions