Find rows not in other sub query?
(1) By Gilles on 2021-03-18 11:18:39 [link] [source]
Hello,
Is there a way in SQLite to display only the rows that aren't in a sub-query?
I need to investigte why the following queries don't return the same number of rows:
select count(*) from main;
5854
select count(*) from main,zip where main.ZIP=zip.ZIP;
5905
Thank you.
(2) By Dan Kennedy (dan) on 2021-03-18 11:25:40 in reply to 1 [source]
Perhaps:
SELECT zip FROM zip WHERE zip.zip NOT IN (SELECT main.zip FROM main);
to find any rows in table "zip" that have no counterpart in table "main".
(3) By Gilles on 2021-03-18 11:54:53 in reply to 2 [link] [source]
Thanks.
It looks like there's something wrong my data. I'll investigate.
SELECT COUNT(*) FROM main;
5854
SELECT COUNT(*) FROM zip; 35074
SELECT COUNT(*) FROM main,zip WHERE main.ZIP=zip.ZIP; 5905
SELECT COUNT(*) FROM zip WHERE zip.ZIP NOT IN (SELECT main.ZIP FROM main); 34769
(4) By Gilles on 2021-03-18 12:06:36 in reply to 2 [link] [source]
Think I found what it is: Wrong data type when importing
PRAGMA table_info(zip);
cid;name;type;notnull;dflt_value;pk
0;ZIP;TEXT;0;;0
1;CITY;TEXT;0;;0
2;LATITUDE;TEXT;0;;0
3;LONGITUDE;TEXT;0;;0
PRAGMA table_info(main); cid;name;type;notnull;dflt_value;pk 0;ZIP;;0;;0 1;TRANS;;0;;0
(5) By Ryan Smith (cuz) on 2021-03-18 12:08:25 in reply to 1 [link] [source]
What I use mostly:
SELECT zip.*
FROM zip
LEFT JOIN main ON main.ZIP = zip.ZIP
WHERE main.ZIP IS NULL
;
(6) By Gilles on 2021-03-18 14:33:37 in reply to 5 [link] [source]
Thank you.
(7) By Larry Brasfield (larrybr) on 2021-03-18 15:05:44 in reply to 1 [link] [source]
Adding to the solutions already mentioned:
select zip from main
except
select zip from zip
. I submit this as a natural way to express "These but not Those".