Count rows from left table without corresponding value in the right table-CodePudding

I want to count rows from left table (of a 1-to-many relation between two tables) that do not have PK-FK representative in right table

Left table

id | value
-----------
1  |  a
2  |  b
3  |  c

Right table

id | id-left | value
--------------------
.. |  1      | ....

the expected result is 2 as rows with id 1 and 3 in left table have no counterpart in right table.

CodePudding user response：

You can use a not exists anti-semi-join:

select count(*)
from l
where not exists (
  select * from r where r.id_left = l.id
);