sql query: fast find the table 1 records that do not have a record in table 2 with `value = c`

table 1: id1, name, surname

table 2: id1, id2, value

id1 is a foreign key for table 2

What is the fastest query to find the table 1 records 开发者_如何学JAVAthat do not have a record in table 2 with value = c ?

In general circumstances query is doomed to be slow.

select * from table1 where not exists 
  ( select id1 from table2 where table1.id1 = table2.id2 and value = с )

You can use NOT IN or NOT EXISTS. Here is a functionality comparative website.

select * from table1 as tb1
where tb1.id1 not in 
  (select tb2.id1 from table2 as tb2
   where tb1.id1 = tb2.id1
     and value='c')

Depends of course largely on the sizes of table1 and table2, but I've found that inner queries are a lot more efficient than I originally assumed through testing.

You might try this solution,

WITH MISSING (id1)
AS
(
SELECT id1 FROM table1
EXCEPT
SELECT distinct id1 FROM table2 WHERE value = 'c'
)
SELECT * FROM table1 t inner join MISSING m on t.id1 = m.id1;
-- or
SELECT * FROM table1 t WHERE EXISTS (SELECT id1 FROM MISSING m WHERE t.id1 = m.id1);

SELECT * 
FROM table1 
WHERE id1 NOT IN 
    (SELECT id1 FROM table2 WHERE value = 'c')

This query will give you all records in Table 1 that either

1. Do not have records in table 2.
2. Do not have records in table 2 with value = 'c'

SQL:

SELECT * 
FROM table1 
WHERE id1 NOT IN 
    (SELECT id1 FROM table2 WHERE value = 'c')

This query will give you all records in Table 1 that either

1. Do HAVE records in table 2.
2. BUT do not have records in table 2 with value = 'c'

SQL:

SELECT * 
FROM table1 t1
INNER JOIN table2 t2
ON t1.id1 = t2.id1
WHERE t1.id1 NOT IN 
    (SELECT id1 FROM table2 WHERE value = 'c')