CODEWITHSUNDEEP
mysql
clarkk
clarkk

Reputation: 27669

fetch rows where left join subquery is null (not found)

How to fetch rows where a joined subquery is null?

SELECT *
FROM bank_recon b
LEFT JOIN (
    SELECT o.bank_recon_id
    FROM data_voucher_ocr_bank o
    LEFT JOIN data_voucher v ON v.id=o.data_voucher_id
    WHERE v.is_ocr_verified=1
    LIMIT 1
) s ON s.bank_recon_id=b.id
WHERE s IS NULL

update

When using this query (the subquery) something is fetched depending on if is_ocr_verified is set or not

SELECT o.bank_recon_id
FROM data_voucher_ocr_bank o
LEFT JOIN data_voucher v ON v.id=o.data_voucher_id
WHERE v.is_ocr_verified=1 && o.bank_recon_id=320062

When using this query everything is fetched no matter what!?

SELECT b.txt, b.amount
FROM bank_recon b
LEFT JOIN (
    SELECT o.bank_recon_id
    FROM data_voucher_ocr_bank o
    LEFT JOIN data_voucher v ON v.id=o.data_voucher_id
    WHERE v.is_ocr_verified=1
    LIMIT 1
) s ON s.bank_recon_id=b.id
WHERE b.id=320062 && s.bank_recon_id IS NULL

Upvotes: 0

Views: 213

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

An anti join (which is what you are trying to apply here) is a method we use when the straight-forward NOT IN or NOT EXISTS have performance issues in a DBMS.

Provided data_voucher_ocr_bank.bank_recon_id cannot be null, we can use:

SELECT txt, amount
FROM bank_recon
WHERE id NOT IN
(
  SELECT bank_recon_id
  FROM data_voucher_ocr_bank
  WHERE data_voucher_id IN (SELECT id FROM data_voucher WHERE is_ocr_verified = 1)
);

(Otherwise we'd add AND bank_recon_id IS NOT NULL or use NOT EXISTS instead.)

Upvotes: 0

fancyPants
fancyPants

Reputation: 51868

Specify a column in your WHERE clause, not just the subquery.

WHERE s.bank_recon_id IS NULL

Upvotes: 2

Related Questions