Joe Celko s SQL for Smarties - Advanced SQL Programming P34. 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. | 302 CHAPTER 15 EXISTS PREDICATE ------------------------------------------------------------------------- SELECT was born on a day without a famous New Yorker FROM Personnel AS P1 WHERE NOT IN SELECT FROM Celebrities AS C1 WHERE New York and you would think that the exists version would be SELECT was born on a day without a famous New Yorker FROM Personnel AS P1 WHERE NOT EXISTS SELECT FROM Celebrities AS C1 WHERE New York AND Assume that Gloria Glamour is our only New Yorker and we still do not know her birthday. The subquery will be empty for every employee in the not exists predicate version because her null birthday will not test equal to the known employee birthdays. That means that the not exists predicate will return true and we will get every employee to match to Ms. Glamour. But now look at the IN predicate version which will have a single NULL in the subquery result. This predicate will be equivalent to null which is always unknown and we will get no employees back. Likewise you cannot in general transform the quantified comparison predicates into exists predicates because of the possibility of NULL values. Remember that x ALL subquery is shorthand for x NOT IN subquery and x ANY subquery is shorthand for x IN subquery and it will not surprise you. In general the exists predicates will run faster than the in predicates. The problem is in deciding whether to build the query or the subquery first the optimal approach depends on the size and distribution of values in each and that cannot usually be known until runtime. EXISTS and INNER JOINs The not exists predicate is almost always used with a correlated subquery. Very often the subquery can be flattened into a JOIN which NOT EXISTS and OUTER JOINs 303 will frequently run faster than the original query. Our sample query can be converted into SELECT has the same .
