How to use JOIN instead of SELECT NOT IN?

Question

I have 2 tables:

• customers (customer_id, customer_name)
• documents (document_id, document_name, customer_id)

I wish to find all customers, for example, that do not have a rent contract.

I can do it such way:

SELECT contragent_name 
FROM contragents 
WHERE contragent_id NOT IN (SELECT contragent_id 
                            FROM documents 
                            WHERE document_name = 'Rent contract');

Can anybody suggest how to do it without the second select?

Answer 1

A left join is an alternative:

SELECT c.contragent_name
FROM contragents AS c
  LEFT JOIN documents d. 
         ON c.contragent_id = d.contragent_id 
        AND document_name = 'Rent contract'
WHERE d.contragent_id IS NULL;

But typically a NOT EXISTS condition is the most efficient way to do it (if there is a difference in performance - this largely depends on the actual DBMS being used).

SELECT c.contragent_name 
FROM contragents c
WHERE NOT EXISTS (SELECT *
                  FROM documents d
                  WHERE d.document_name = 'Rent contract'
                    AND d.contragent_id = c.contragent_id);

Answer 2

Well you could rephrase your requirement using a left anti-join:

SELECT c.contragent_name
FROM contragents c
LEFT JOIN documents d
    ON d.contragent_id = c.contragent_id AND
       d.document_name = 'Rent contract'
WHERE
    d.contragent_id IS NULL;

However, if you were to compare the explain plan and performance of the above to your subquery approach, you might find similar things. Actually, your current approach can easily be made performant by adding the following index:

CREATE INDEX idx ON documents (contragent_id, document_name);

This index should allow the rapid lookup of any record in the contragents table.