43 lines
819 B
Plaintext
43 lines
819 B
Plaintext
=== on 16 Jun 2016, 16:13:18 Ann Campbell wrote:
|
|
https://www.researchgate.net/publication/222432151_Semantic_errors_in_SQL_queries_A_quite_complete_list
|
|
|
|
=== on 29 Aug 2018, 16:30:32 Alexandre Gigleux wrote:
|
|
----
|
|
CREATE TABLE TABLE1 (
|
|
value VARCHAR2(10)
|
|
)
|
|
|
|
CREATE TABLE TABLE2 (
|
|
value VARCHAR2(10)
|
|
)
|
|
|
|
INSERT INTO TABLE1 VALUES (1)
|
|
INSERT INTO TABLE1 VALUES (2)
|
|
INSERT INTO TABLE1 VALUES (NULL)
|
|
INSERT INTO TABLE2 VALUES (2)
|
|
INSERT INTO TABLE2 VALUES (NULL)
|
|
|
|
SELECT *
|
|
FROM TABLE1
|
|
|
|
SELECT *
|
|
FROM TABLE2
|
|
|
|
-- KO
|
|
SELECT *
|
|
FROM TABLE1
|
|
WHERE VALUE NOT IN (SELECT VALUE FROM TABLE2)
|
|
|
|
-- OK
|
|
SELECT *
|
|
FROM TABLE1
|
|
WHERE VALUE NOT IN (SELECT VALUE FROM TABLE2 WHERE VALUE IS NOT NULL)
|
|
|
|
-- OK
|
|
SELECT *
|
|
FROM TABLE1
|
|
WHERE NOT EXISTS (SELECT 1 FROM TABLE2 WHERE TABLE1.VALUE = TABLE2.VALUE)
|
|
|
|
----
|
|
|