December 14, 2017 at 9:43 pm
Hi Guys,
Need help to understand, Please help me to understand....
Here is my sample data
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
2,Faid,Kim,Null
3,Jim,Al,Null
My Simple SQL Query is
Select * from table1
where STATUS not in ('InActive','Confirmed')
I am getting this result back
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
It should return all three records. Any thought why it is behaving like this ? STATUS data type is VARCHAR(10)
December 15, 2017 at 1:20 am
rocky_498 - Thursday, December 14, 2017 9:43 PMHi Guys,
Need help to understand, Please help me to understand....
Here is my sample data
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
2,Faid,Kim,Null
3,Jim,Al,NullMy Simple SQL Query is
Select * from table1
where STATUS not in ('InActive','Confirmed')I am getting this result back
ID,FNAME,LNAME,STATUS
1,Smith,Sam,ActiveIt should return all three records. Any thought why it is behaving like this ? STATUS data type is VARCHAR(10)
Null means "Unknown" so it is not a value that can be compared to any value, so as it cannot be compared it is excluded.
You need to factor in NULLs into your query Select * from table1 where STATUS not in ('InActive','Confirmed') OR STATUS IS NULL
December 15, 2017 at 9:36 pm
This question has been answered. Thank You for your reply!
December 17, 2017 at 1:02 pm
rocky_498 - Thursday, December 14, 2017 9:43 PMHi Guys,
Need help to understand, Please help me to understand....
Here is my sample data
ID,FNAME,LNAME,STATUS
1,Smith,Sam,Active
2,Faid,Kim,Null
3,Jim,Al,NullMy Simple SQL Query is
Select * from table1
where STATUS not in ('InActive','Confirmed')I am getting this result back
ID,FNAME,LNAME,STATUS
1,Smith,Sam,ActiveIt should return all three records. Any thought why it is behaving like this ? STATUS data type is VARCHAR(10)
Rows are nothing like records.There is no such thing as generic "status" in RDBMS. We post DDL and ASCII picture of data.
NULLs make some special problems in a NOT IN predicate with a subquery. Consider these two tables:
CREATE TABLE Table1 (x INTEGER);
INSERT INTO Table1 VALUES (1), (2), (3), (4);
CREATE TABLE Table2 (x INTEGER);
INSERT INTO Table2 VALUES (1), (NULL), (2);
Now execute the query:
SELECT *
FROM Table1
WHERE x NOT IN (SELECT x FROM Table2)
Let's work it out step by painful step:
1) do the subquery
SELECT * FROM Table1WHERE x NOT IN (1, NULL, 2);
2) convert the NOT IN to its definitional form
SELECT * FROM Table1
WHERE NOT (x IN (1, NULL, 2));
3) expand IN predicate
SELECT *FROM Table1 WHERE NOT ((x = 1) OR (x = NULL) OR (x = 2));
4) apply DeMorgan's law:
SELECT * FROM Table1 WHERE ((x <> 1) AND (x <> NULL) AND (x <> 2
5) constant logical expression
SELECT * FROM Table1 WHERE ((x <> 1) AND UNKNOWN AND (x <> 2));
6) Reduction of OR to constant
SELECT * FROM Table1 WHERE UNKNOWN;
7) Results are always empty.
Now try this with another set of tables
CREATE TABLE Table3 (x INTEGER);
INSERT INTO Table3 VALUES (1), (2), (NULL), (4);
CREATE TABLE Table4 (x INTEGER);
INSERT INTO Table3 VALUES (1), (3), (2);
Let's work out the same query again, step by painful step:
1) do the subquery
SELECT *
FROM Table3
WHERE x NOT IN (1, 3, 2);
2) convert the NOT IN to Boolean expression
SELECT *
FROM Table3
WHERE NOT (x IN (1, 3, 2));
3) expand IN predicate
SELECT *
FROM Table3
WHERE NOT ((x = 1) OR (x = 3) OR (x = 2));
4) DeMorgan's law:
SELECT *
FROM Table3
WHERE ((x <> 1) AND (x <> 3) AND (x <> 2));
5) Computed result set; I will show it as a UNION with substitutions
SELECT * FROM Table3 WHERE ((1 <> 1) AND (1 <> 3) AND (1 <> 2)) -- FALSE
UNION ALL
SELECT * FROM Table3 WHERE ((2 <> 1) AND (2 <> 3) AND (2 <> 2)) -- FALSE
UNION ALL
SELECT * FROM Table3 WHERE ((CAST(NULL AS INTEGER) <> 1)
AND (CAST(NULL AS INTEGER) <> 3)
AND (CAST(NULL AS INTEGER) <> 2)) -- UNKNOWN
UNION ALL
SELECT *
FROM Table3
WHERE ((4 <> 1) AND (4 <> 3) AND (4 <> 2)); -- TRUE
6) Result is one row = (4).
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply