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
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 .
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.
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).
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) ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With