Find rows not in other sub query?

(1) By Gilles on 2021-03-18 11:18:39

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

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".