Not to hammer the JOIN vs. IN topic to death, but I have a question regarding number 36 on the exam...
Upon typing parts I and II into jedit and running the files in Oracle, I found that the results are similar, but not the same. The following is the output that I received...
Output for part I
CUS CUSTOMER_NAME
--- -----------------------------------
408 The Everything Shop
282 Brookings Direct
608 Johnson's Department Store
148 Al's Appliance and Sport
608 Johnson's Department Store
Output for part II
CUS CUSTOMER_NAME
--- -----------------------------------
148 Al's Appliance and Sport
282 Brookings Direct
408 The Everything Shop
608 Johnson's Department Store
The main difference stems from the fact that customer #608 appears twice in the first set of output results. Here is my theory as to why these results differ…
The first query says to join the tables and then to display all rows where the customer number in the orders table matches a customer number in the customers table, with the exception of those orders that were placed on October 20th. Since customer #608 placed two orders on October 23rd, the results display customer #608 twice. The join query isn’t sophisticated enough to display unique customers only because we are considering both tables with an equal weighting.
On the other hand, the second set of output results yields only unique customers. I would argue that this is due to the structure of the query. If we start off by considering the inner part of the query… and look at the customer numbers in the orders table that have orders outstanding for all days other than October 20th, this limits our results to customer #s 408, 282, 608, 148, and 608. Once we have this list of customer numbers, we then need to trace them back up to the customer table. Since the customer numbers are only listed once in the customer table and the query says to display the customer name and customer number from the customer table, each customer only has the potential to appear once in the results, even though some customers, such as #608, may have more than one order outstanding in the system. Because we have to evaluate this query in different steps it is different than the first query, which seems to have a flatter structure.
Therefore, I would argue that only some of the queries are equivalent... queries II and III are equivalent but I and II are not. If you’ve read this far, you may think that I am completely crazy... am I?