Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following query:

SELECT
  ordno
from
  orders
where
  ordno NOT IN (SELECT ordno FROM invoices)

There are about 25000 records in each table. I have indexed the ordno column.

The query hangs without showing any output.

I imagine that the problem is that the select subquery is called 25000 times for each ordno in orders table.

How can I do this?

SELECT
  ordno
from
  orders
WHERE ordno NOT IN (
--  somehow select ordno from invoice table only once
)
share|improve this question
    
Do you have an index on invoices(ordno)? Are both columns (in the two tables) of the same datatype? –  ypercube Sep 11 '12 at 6:29
1  
MySQL should run that sub-select only once (as it is unrelated to the main query). Are you sure it's optimizer really handles it that badly? –  a_horse_with_no_name Sep 12 '12 at 6:57
    
@ypercube You're right. The datatype in both coulmns is different. One is varchar another is integer. Could you explain why that creates the problem and put it as an answer so that I can select it. –  Kshitiz Sharma Sep 12 '12 at 8:28
add comment

3 Answers

If you LEFT JOIN the tables you can then check if the left ordno does not exist. Something like this:

SELECT o.ordno 
FROM orders AS o
LEFT JOIN invoices AS i 
ON i.ordno = o.ordno
WHERE i.ordno IS NULL;

I hope this helps you.

share|improve this answer
add comment

Another answer:

SELECT
  ordno
from
  orders
where
  NOT EXISTS (SELECT * FROM invoices WHERE invoices.ordno = orders.ordno)
share|improve this answer
add comment

You asked: How to

somehow select ordno from invoice table only once

You could do that by adding DISTINCT:

SELECT
  ordno
FROM
  orders
WHERE
  ordno NOT IN (SELECT DISTINCT ordno FROM invoices) ;

... but there is absolutely no reason to do it, if there is an index on invoices(ordno). You can test of course, but the explain plan should be the same and the execution times identical.

(the situation may be different if the invoices is not a base table but a complex view that returns thousands of identical order numbers).

You should also check if the two variations proposed in other answers are more efficient with your data distribution. I wouldn't expect much difference. This blog article Explain Extended: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL has some tests that support that but it's never bad to test with your data and table sizes, in your envirorment.


What you should really check first of all, is whether you have an index on orders(ordno) and on invoices(ordno) and whether these two columns, are of the same datatype.

If there are no indices, add them. If the two columns are of different datatype, change them into the same datatype.

share|improve this answer
    
The distinct in the sub-query will (should) not make any difference. –  a_horse_with_no_name Sep 12 '12 at 6:56
    
IN ( SELECT ... ) is very poorly optimized (until about 5.6.7); do not use it. EXISTS ( SELECT ... ) is reasonably well optimized. LEFT JOIN ... IS [NOT] NULL is also well optimized. –  Rick James Jan 8 '13 at 1:27
    
@RickJames: NOT IN (SELECT ...) and IN (SELECT ...) are two different things with different optimizations. –  ypercube Jan 8 '13 at 12:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.