CODEWITHSUNDEEP
mysqlsqljoinquery-optimizationouter-join
rinogo
rinogo

Reputation: 9163

Simplifying a query (with left outer join)

Our app has been using this query for a while now:

SELECT id 
FROM invoice 
WHERE id NOT IN 
(SELECT invoice_id FROM invoice_transaction) 

#437 rows returned

In a code review, this query was rewritten with a left outer join:

SELECT invoice.id 
FROM invoice 
LEFT OUTER JOIN invoice_transaction on invoice.id=invoice_transaction.invoice_id 

#586 rows returned

The first query returns 437 rows and the second (supposedly identical) query returns 586 rows. Is this not a valid use of the left outer join?

What would be a better way to rewrite this query?

Thank you!

Upvotes: 1

Views: 190

Answers (2)

Jatin
Jatin

Reputation: 1708

best way is this 

SELECT id 
FROM invoice as i
WHERE not exists
(SELECT invoice_id FROM invoice_transaction as it where it.invoice_id=i.id )

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79949

The following query should return the same results of the first query:

SELECT invoice.id 
FROM invoice 
LEFT OUTER JOIN invoice_transaction 
             on invoice.id = invoice_transaction.invoice_id
WHERE invoice_transaction.invoice_id IS NULL

Upvotes: 4

Related Questions