SQLite Forum

Find rows not in other sub query?
Login

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 [link] [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 [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".