MSQL get first 100 rows of not intersection

Question

I have 2 tables in MSQL

The first one is called tbl_file and it has the following properties:

• id
• hash_id
• description
• mime-type
• creation date
• etc..

The second one is called tbl_sys_filecontent and it only has:

• id
• content (base64)

It's supposed that these two tables must have the same row size. (hash-id of tbl_file is the same as id in tbl_sys_filecontent

However, something went wrong and now if we execute:

select count(*) from tbl_sys_filecontent;

We get about 1 000 000 rows

And we're supposed to get the same row count after executing the query:

select count(*) from tbl_file as f
JOIN tbl_sys_filecontent as sf on f.hash_id = sf.id;

But we are returned with only roughly 100 000 results

Let's call the first query collection A and the second B

The question is how do I get the first 100 rows which are equal to A-B (so the hashes aren't equal?)

Thanks in advance.

Answer 1

With not exists

select *
from tbl_file as f
where not exists (select 'x' from tbl_sys_filecontent  as sf where f.hash_id = sf.id);

With not in

select *
from tbl_file as f
where f.hash_id not in (select 'x' from tbl_sys_filecontent  as sf);

However, not exists is to favor over not in: https://stackoverflow.com/a/11074428/59119

With a left join

select f.* from tbl_file as f
    LEFT JOIN tbl_sys_filecontent as sf on f.hash_id = sf.id
where sf.id is null;