How to select data in a sub query only once? 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
)
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.