Joe Celko s SQL for Smarties - Advanced SQL Programming P33. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 292 CHAPTER 14 THE NOT IN PREDICATE SELECT FROM JohnsBook AS J1 WHERE NOT EXISTS SELECT FROM QualityGuide AS Q1 WHERE The reason the second version will probably run faster is that it can test for existence using the indexes on both tables. The not in version has to test all the values in the subquery table for inequality. Many SQL implementations will construct a temporary table from the in predicate subquery if it has a where clause but the temporary table will not have any indexes. The temporary table can also have duplicates and a random ordering of its rows so that the SQL engine has to do a full-table scan. Replacing ORs with the IN Predicate A simple trick that beginning SQL programmers often miss is that an in predicate can often replace a set of ORed predicates. For example SELECT FROM QualityControlReport WHERE test_1 passed OR test_2 passed OR test_3 passed OR test_4 passed can be rewritten as SELECT FROM QualityControlReport WHERE passed IN test_1 test_2 test_3 test_4 The reason this is difficult to see is that programmers get used to thinking of either a subquery or a simple list of constants. They miss the fact that the in predicate list can be a list of expressions. The optimizer would have handled each of the original predicates separately in the where clause but it has to handle the in predicate as a single item which can change the order of evaluation. This might or might not be faster than the list of ORed predicates for a particular query. This NULLs and the IN Predicate 293 formulation might cause the predicate to become nonindexable you should check the indexability rules of your particular DBMS. NULLs and the IN Predicate nulls make some special problems in a not in predicate with a subquery. Consider these two tables CREATE TABLE Tablet x INTEGER INSERT INTO Tablet VALUES t 2 3 4 CREATE TABLE Table2 x INTEGER INSERT INTO Table2 VALUES t NULL 2 Now execute the query SELECT FROM Tablet WHERE
