Find rows not in other sub query?
(1) By Gilles on 2021-03-18 11:18:39 [link]
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: <code>select count(*) from main; 5854 </code> <code>select count(*) from main,zip where main.ZIP=zip.ZIP; 5905 </code> Thank you.
(2) By Dan Kennedy (dan) on 2021-03-18 11:25:40 in reply to 1 [link]
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]
Thanks. It looks like there's something wrong my data. I'll investigate. <code> 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 </code>
(4) By Gilles on 2021-03-18 12:06:36 in reply to 2 [link]
Think I found what it is: Wrong data type when importing <code> 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 </code>
(5) By Ryan Smith (cuz) on 2021-03-18 12:08:25 in reply to 1 [link]
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]
Thank you.
(7) By Larry Brasfield (larrybr) on 2021-03-18 15:05:44 in reply to 1
Adding to the solutions already mentioned:<code> select zip from main except select zip from zip </code>. I submit this as a natural way to express "These but not Those".