The question is:
Find the listing of customers who did not make purchases during the invoicing period.
I have the following query using NOT IN, but I want to write one that does NOT use NOT IN. What is a better alternative? Would I use joins?
SELECT customers.cus_code, customers.cus_balance
FROM customers
WHERE customers.cus_code NOT IN (SELECT invoices.cus_code FROM invoices)
ORDER by customers.cus_code
One option would be to rephrase your query using a LEFT JOIN:
SELECT
t1.cus_code,
t1.cus_balance
FROM customers t1
LEFT JOIN invoices t2
ON t1.cus_code = t2.cus_code
WHERE t2.cus_code IS NULL
ORDER by t1.cus_code
This might have a performance advantage over your current NOT IN query, if you had proper indices setup on the join columns.
You can use a left join and check where the right table value is null, something like this:
SELECT customers.cus_code, customers.cus_balance
FROM customers left join invoices on invoices.cus_code = customers.cus_code
WHERE invoices.cus_code IS NULL
ORDER by customers.cus_code
Basically, with the left join you select all the records from the first table that have or don't have a relation with the second. By filtering where the field of the second table is null, you get only the left part of the green circle, that doesn't intersect
You could also try the NOT EXISTS version. This may (or may not) generate a better plan. Some people prefer EXISTS to IN. There are definitely differences if the column allows null.
SELECT customers.cus_code, customers.cus_balance
FROM customers
WHERE NOT EXISTS (SELECT * FROM invoices WHERE
invoices.cus_code=customers.cus_code )
ORDER by customers.cus_code
not inis "performance reasons" then you're unlikely to win by changing it. If that's not the reason, perhaps you could elaborate on what the reasons are for avoiding straightforward code?