0

I have the following SQL query which does not give me the correct results. Can you provide me with the correct SQL query for this? It is not returning the row containing ( NULL, 5 ).

-- Create a table
CREATE TABLE [dbo].[MyTable]
(
    [X] INT,
    [Y] INT
)
GO
-- Insert some data
INSERT INTO MyTable ( [X], [Y] ) VALUES ( 1, 2 )
GO
INSERT INTO MyTable ( [X], [Y] ) VALUES ( 2, 3 )
GO
INSERT INTO MyTable ( [X], [Y] ) VALUES ( 3, 4 )
GO
INSERT INTO MyTable ( [X], [Y] ) VALUES ( NULL, 5 )
GO
/*
   Find the rows whose [X] value is other than 1, 3
*/
-- Problem
SELECT [X], [Y]
FROM MyTable
WHERE [X] NOT IN ( 1, 3 )  -- We are missing NULL value row in the output result set
GO

We can use IS NULL records first and then UNION them with the results from the above Query. That is one of the options. Are there any other ways that we can tackle this problem?

Thom A
  • 88,727
  • 11
  • 45
  • 75
Kishore Babu. Peddi
  • 17
  • 1
  • 3
  • 3
    This is [documented](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/not-transact-sql?view=sql-server-ver16#remarks) and [expected](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver16#result-value) behaviour. `NULL NOT IN (1,3)` = UNKNOWN <> TRUE <> FALSE – Thom A Sep 01 '22 at 14:26
  • 1
    `WHERE [X] NOT IN ( 1, 3 )` is the same as `WHERE [X] <> 1 OR [X] <> 3` and imputing the `NULL` gives you `WHERE NULL <> 1 OR NULL <> 3` which results in `WHERE UNKNOWN OR UNKNOWN` which finally results in `WHERE UNKNOWN` which fails predictably. Instead write it as a `NOT EXISTS` like this `WHERE NOT EXISTS ((SELECT 1 UNION SELECT 3) INTERSECT SELECT X)` – Charlieface Sep 01 '22 at 14:31

0 Answers0