Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using NOT predicates in SQL

Tags:

sql

tsql

I've created some queries and can't understand why the results are not as I expected.

I don't understand why Query II and III don't return the same results. I would expect query II to return all rows not selected by Query I.

I would have expected Query II and III to give the same results. In my opinion the results of III are the right ones.

I'm sure I miss something, I just don't know what.

The example:

Table:

CREATE TABLE [dbo].[TestTable](
 [TestTableId] [int] NOT NULL,
    [ValueA] [int] NULL,
 [ValueB] [int] NULL
) ON [PRIMARY]

Data:

TestTableId ValueA ValueB
1        10      5
2        20      5
3        10      NULL
4        20        NULL
5        NULL      10
6        10        10
7        NULL      NULL

Queries:

All records: select * from TestTable

I. A select query:

select * from TestTable 
where (ValueA = 10 or ValueA = 20) AND ValueB = 5

Result:

TestTableId ValueA ValueB
1           10   5
2           20   5

II. The same query but as NOT

select * from TestTable 
where NOT ((ValueA = 10 or ValueA = 20) AND ValueB = 5)

Result:

TestTableId ValueA ValueB
5           NULL   10
6           10   NULL

III. The same query as the second (I would think)

select * from TestTable where TestTable.TestTableId not in 
    (select TestTableId from TestTable 
where (ValueA = 10 or ValueA = 20) AND ValueB = 5)

Result:

TestTableId ValueA ValueB
3           10   NULL
4           20   NULL
5           NULL   10
6           10   10
7           NULL   NULL
like image 560
Gabriël Avatar asked Nov 18 '10 13:11

Gabriël


People also ask

What is the use of NOT IN in SQL?

The SQL NOT operator NOT is a logical operator in SQL that you can put before any conditional statement to select rows for which that statement is false. In the above case, you can see that results for which year_rank is equal to 2 or 3 are not included. NOT is commonly used with LIKE .

What are predicates in SQL query?

A predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

Can we use not in WHERE clause in SQL?

The NOT IN operator can be used anywhere any other operator is used including WHERE clauses, HAVING clauses, IF statements, or join predicates – although they should be extremely rare in join predicates (SQL JOINS - SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN).


1 Answers

NULLs are funny creatures. They will answer "I don't know" to both of the following questions:

Are you 5?  (... WHERE ValueB = 5)

and

Are you Not 5? (... WHERE NOT ValueB = 5)

Which results in NULL values being excluded from both queries, as you found.

You have to ask the question in a way that explicitly accounts for the NULLs:

... WHERE (ValueB IS NULL OR NOT ValueB = 5) ...
like image 75
BradC Avatar answered Nov 03 '22 03:11

BradC