Home > OS >  What is the correct way to use "OR NOT" in SQL where clause
What is the correct way to use "OR NOT" in SQL where clause

Time:08-26

In SQL where clause,

Is ..... AND NOT (A OR B OR C) .....

the same as ..... AND ((NOT A) OR (NOT B) OR (NOT C))? .....

Thank you!

CodePudding user response:

SQL uses ternary logic with truth tables including true, false, and unknown to handle comparisons with null. In the OR truth table, (false or unknown) == unknown (which is falsy) but (true or unknown) == true

Consider:

SELECT 1
WHERE not (1=2 or 1=null)

Versus

SELECT 2
WHERE (not 1=2) or (1=null)

Example SqlFiddle

Redgate article with more about ternary logic in SQL

CodePudding user response:

No, it's not the same. First and second will return different result. First one will return the result of the or condition.

Query below will be like the first you wrote, it will filter and return result you want.

SELECT * FROM tablename
WHERE NOT(columnname= 'columnvalue' OR columnname= 'columnvalue')

For second one, you need to use AND instead of OR. This is because you apply 'NOT' to each one.

SELECT * FROM tablename
WHERE (NOT(columnname= 'columnvalue') AND NOT(columnname= 'columnvalue'))

You can refer to this example

  •  Tags:  
  • sql
  • Related