rocky_498 - Thursday, December 14, 2017 9:43 PM
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.