John D. Burger wrote: >> select * >> from people >> where id not in >> ( >> select id >> from class_registration >> ) > > In my experience, queries like the OUTER LEFT JOIN version posted > earlier are usually much more efficient than NOT IN queries like the > above. The planner seems to be pretty smart about turning (positive) > IN queries into joins, but NOT IN queries usually turn into nested > table scans, in my experience. Interesting, I am aware that each DBMS query optimizer does better with some expressions, and worse with others. When I was a DB2 DBA, DB2 would change from release to release the expressions it most preferred. I imagine the above formulation is what many people would try initially, until they encounter experiences such as yours. I checked the TO-DO list and I don't see anything pending to address this. Bruce and/or Tom, are there any far-off intentions to do anything to improve "not in" execution? Or perhaps to rewrite it to an equivalent expression that already works well? -- Guy Rouillier
