How to use JOIN instead of SELECT NOT IN?

user_3450645 0
Viewed 139

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?

firebird sql
3 Answers

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.

user_18632290

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);
user_3303150

Use a left join and filter where the left table key is null (but you'll still need nested query to get the subset of documents with the name Rent contract):

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

This is sometimes referred to as a Frustrated Join. Performance comparison for a frustrated join vs a not in selection depends upon varying factors, such as the database system in use, volume of records, sometimes database statistics/trends that a query optimizer may use...

user_41556650
Related