Pros and Cons: Different Ways of Expressing the Same Thing
SQL is supposed to be descriptive, in the sense that a SQL query describes the result set rather than specify the exact steps for creating it (hence the need for compiling and optimizing the query). SQL is so descriptive, in fact, that it often offers more than one way to express a given result set. The SQL optimizer is supposed to “understand” the query and find the best way to execute it.
That’s the theory. The practice is rather different. Often, one way of writing a query is more efficient than other equivalent ways. Of course, sometimes the differences depend on the database engine. Perhaps surprisingly, in many situations the same construct works best across many databases.
What States Are Not Recognized in Orders?
This section investigates the simple question: What states are in Orders but not in ZipCensus?
The Most Obvious Query
A simple query answers the question:
SELECT DISTINCT o.State From...