CODEWITHSUNDEEP
mysqlsql
user3550402
user3550402

Reputation: 79

"Exclude" results with an SQL WHERE or JOIN?

I have a query that I want to add some log to, to drop results that successfully match when I add one more table to the JOIN.

I'm accomplishing this now with an additional WHERE IN statement instead:

SELECT blah blah FROM donation
WHERE donation.id NOT IN (SELECT donation_id FROM donation_relation)

I just worry that selecting all ID fields from this donation_relation table in the subquery will begin dragging when the table starts growing. What's the more efficient way (if it exists) to use JOIN to accomplish this exclusion? The two tables are joinable on donation.id and donation_relation.donation_id

Thanks!

Upvotes: 1

Views: 65

Answers (4)

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

You can use NOT EXISTS (anti-join):

SELECT blah blah FROM donation
WHERE NOT EXISTS (
    SELECT 1 FROM donation_relation
    WHERE donation_relation.donation_id = donation.id
)

Upvotes: 0

Manwal
Manwal

Reputation: 23816

Exactly JOIN is faster then sub query in RDBMS. This Join vs. sub-query more explain about this. Use following query:

SELECT blah blah 
FROM   donation 
       LEFT JOIN donation_relation 
              ON donation.id = donation_relation.donation_id 
WHERE  donation_relation.donation_id IS NULL

Upvotes: 0

jarlh
jarlh

Reputation: 44766

LEFT OUTER JOIN alternative:

SELECT blah blah
FROM donation
  LEFT JOIN donation_relation ON donation.id = donation_relation.donation_id 
WHERE donation_relation.donation_id IS NULL;

Probably faster, especially when MySQL.

EXISTS version:

SELECT blah blah FROM donation
WHERE NOT EXISTS (SELECT donation_id FROM donation_relation
                  WHERE donation.id = donation_relation.donation_id )

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

The common way in Standard SQL uses correlated NOT EXISTS (additionally NOT IN might have some non-intuitive side-effects when NULLs are involved):

SELECT blah blah FROM donation as d
WHERE  NOT EXISTS
   (SELECT * FROM donation_relation as dr 
    where dr.donation_id = d.donation_id)

Upvotes: 2

Related Questions