It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example:
SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)
SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)
SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL
I'm not sure if I got all the syntax correct, but these are the general techniques I've seen. Why would I choose to use one over the other? Does performance differ...? Which one of these is the fastest / most efficient? (If it depends on implementation, when would I use each one?)
EXISTS
clause. You may return*
,NULL
or whatever: all this will be optimized away. – QuassnoiSELECT
andFROM
. And*
is just easier to type. Yes,SQL
does bear some resemblance to a natural language, but it is parsed and executed by a machine, a programmed machine. It's not that it will ever suddenly break into your cubicle and shout "stop demanding for the extra fields in anEXISTS
query because I'm f**g sick of parsing them and then throwing them off!". It's OK with a computer, really. – Quassnoi