The ordering of the clauses in an inner join doesn’t affect the result set. If A=B, then certainly B=A. Inner join clauses are associative. That’s not true for outer joins. The order in which tables are joined directly affects which rows are included in the result set and which values they have. That’s why using the ANSI outer join syntax is so important—the legacy syntax can generate erroneous or ambiguous result sets because specifying join conditions in the WHERE clause precludes specifically ordering them.

To understand fully the effect join order has on OUTER JOINs, let’s explore the effect it has on the result set a query generates. Here’s a query that totals items in the Orders table of the Northwind sample database:

I’ve intentionally introduced join condition failures into the query by incrementing o.OrderId by ten so that we can observe the effects of clause ordering and join failures on the result set. Now let’s reorder the tables in the FROM clause and compute the same aggregate:

See the discrepancy? The total changes based on the order of the tables. Why? Because the first query introduces mismatches between the Orders and Order Details tables before the Unit-Price and Quantity columns are totaled; the second query does so afterward. In the case of the second query, we get a total of all items listed in the Order Details table regardless of whether there’s a match between it and the Orders table; in the first query, we don’t. To understand this better, consider the data on which the two totals are based:

I’ve included a WHERE clause to pare the result set down to just those rows affected by the intentional join mismatch. Since we increment OrderNo by ten and the order numbers are sequential, ten of the OrderNo values in Orders fail to find matches in the Order Details table and, consequently, have NULL UnitPrice and Quantity fields. Here’s a snapshot of the underlying data for the second query (again with a restrictive WHERE clause):

Notice that this set is much longer—nineteen rows longer, to be exact. Why? Because twenty-nine rows were omitted from the result set of the first query due to the join mismatch, though this wasn’t immediately obvious. For each broken order number link, a given number of Order Detail rows were omitted because there was a one-to-many relationship between the Orders and Order Details tables. This, of course, skewed the total reported by the query.

So the moral of the story is this: Be careful with outer join ordering, especially when the possibility of join mismatches exists.

The BETWEEN predicate is probably the most often used of the Transact-SQL predicates. It indicates whether a given value falls between two other values, inclusively. Here’s an example:

BETWEEN works with scalar ranges, so it can handle dates, numerics, and other scalar data types. It combines what would normally require two terms in the WHERE clause: a greater-than-or-equal-to expression, followed by a less-than-or-equal-to expression. WHERE au_lname BETWEEN ‘S’ AND ‘ZZ’ is shorthand for WHERE au_lname >= ‘S’ AND au_lname <=’ZZ’.

In addition to simple constant arguments, BETWEEN accepts subquery, variable, and expression arguments. Here’s an example:

Since the primary purpose of the predicate is to determine whether a value lies within a given range, it’s common to see BETWEEN used to determine whether one event occurs between two others. Locating overlapping events is more difficult than it first appears and its elusiveness gives rise to many false solutions.

This is best explored by way of example. Let’s say we have a list of soldiers, and we need to determine which of them could have participated in the major military engagements of a given war. We’d need at least two tables—one listing the soldiers and their tours of duty and one listing each major engagement of the war with its beginning and ending dates. The idea then would be to return a result set that cross-references the soldier list with the engagement list, taking into account each time a soldier’s tour of duty began or ended during a major engagement, as well as when it encompassed a major engagement. Assume we start with these tables:

Here’s a preliminary solution:

Once the tables are created and populated, the query includes rows in the result set using three separate BETWEEN predicates: A soldier’s tour began during an engagement, his tour ended during an engagement, or an engagement started during his tour. Why do we need this last check? Why do we care whether an engagement started during a soldier’s tour—this would be the same as asking whether a soldier’s tour ended during the engagement, wouldn’t it? No, not quite. Without the third predicate expression, we aren’t allowing for the possibility that an engagement could begin and end within a tour of duty.

Though this query works, there is a better solution. It requires considering the inverse of the problem. Rather than determining when tours of duty and major engagements overlap one another, let’s determine when they don’t. For a tour of duty and a major engagement not to coincide, one of two things must be true: Either the tour of duty ended before the engagement started, or it began after the engagement ended. Knowing this, we can greatly simplify the query and remove the BETWEEN predicates altogether, like so:

LIKE tests a value for a match against a string pattern:

ANSI SQL specifies two pattern wildcard characters: the % (percent) character and the _ (underscore) character; % matches any number of characters, while _ matches exactly one. Here’s an example:

Beyond those supported by ANSI SQL, Transact-SQL also supports regular expression wildcards. These wildcards allow you to test a character for membership within a set of characters. Here’s an example:

In the example above, [ai] is a regular expression wildcard that matches any string with either a or i in the fourth position. To exclude strings using a regular expression, prefix its characters with a caret, like so:

Here, we request authors whose last names begin with “Gr” and contain a character other than e in the third position.

There are some subtle differences between the _ and % wildcards. The _ wildcard requires at least one character; % requires none. The difference this makes is best explained by example. First, consider this query:

Now consider this one:

See the difference? Since _ requires at least one character, “Green_” doesn’t match “Green.”

Another point worth mentioning is that it’s possible for a string to survive an equality test but fail a LIKE test. This is counterintuitive since LIKE would seem to be less restrictive than a plain equality test. The reason this is possible is that ANSI SQL padding rules require that two strings compared for equality be padded to the same length prior to the comparison. That’s not true for LIKE. If one term is padded with blanks and the other isn’t, the comparison will probably fail. Here’s an example:

Notice that the second query doesn’t return any rows due to the padding of the string constant, even though the equality test works fine.

EXISTS is a predicate function that takes a subquery as its lone parameter. It works very simply—if the subquery returns a result set—any result set—EXISTS returns True; otherwise it returns False.

Though EXISTS isn’t defined to require parentheses per se, it does. This is necessary to avoid confusing the Transact-SQL query parser.

The subquery passed to EXISTS is usually a correlated subquery. By correlated, I mean that it references a column in the outer query in its WHERE or HAVING clause—it’s joined at the hip with it. Of course, this isn’t true when EXISTS is used with control-of-flow language statements such as IF and WHILE—it applies only to SELECT statements.

As a rule, you should use SELECT * in the subqueries you pass EXISTS. This allows the optimizer to select the column to use and should generally perform better.

Here’s an example of a simple EXISTS predicate:

(Results abridged)

This query returns all titles for which sales exist in the sales table. Of course, this could also be written as an inner join, but more on that later.

Prefixing EXISTS with NOT negates the expression. Here’s an example:

This makes sense because there are no rows in the sales table for the Net Etiquette title.

As mentioned earlier, the IN predicate provides a shorthand method of comparing a value to each member of a list. You can think of it as a series of equality comparisons between the left-side value and each of the values in the list, joined by OR. Though ANSI SQL-92 allows row values to be used with IN, Transact-SQL does not—you can specify scalar values only. The series of values searched by IN can be specified as a comma-delimited list or returned by a subquery. Here are a couple of simple examples that use IN:

Note that the individual values specified aren’t limited to constants—you can use expressions and subqueries, too. Here’s an example:

The ANY and ALL predicates work exclusively with subqueries. ANY (and its synonym SOME) works similarly to IN. Here’s a query expressed first using IN, then using ANY:

Since IN and =ANY are functionally equivalent, you might tend to think that NOT IN and <>ANY are equivalent as well, but that’s not the case. Instead, <>ALL is the equivalent of NOT IN. If you think about it, this makes perfect sense. <>ANY will always return True as long as more than one value is returned by the subquery. When two or more distinct values are returned by the subquery, there will always be one that doesn’t match the scalar value. By contrast, <>ALL works just like NOT IN. It returns True only when the scalar value is not equal to each and every one of the values returned by the subquery.

This brings up the interesting point that ALL is more often used with the not equal operator (<>) than with the equal operator (=). Testing a scalar value to see whether it matches every value in a list has a very limited use. The test will fail unless all the values are identical. If they’re identical, why perform the test?

The most common use of the subquery is in the SELECT statement’s WHERE clause. Here’s an example:

Here, we return the total sales for the last title_id in the titles table. Note the use of MAX function to ensure that the subquery returns only one row. Subqueries used with the equality operators (=,<>,>= and <=) may return one value only. An equality subquery that returns more than one value doesn’t generate a syntax error, so be careful—you won’t know about it until runtime. One way to avoid returning more than one value is to use an aggregate function, as the previous example does. Another way is to use SELECT’s TOP n extension, like so:

Here, we use TOP 1 to ensure that only one row is returned by the subquery. Just to keep the result set in line with the previous one, we sort the subquery’s result set in descending order on the title_id column, then return the first (actually the last) one.

Make sure that subqueries used in equality comparisons return no more than one row. Code that doesn’t protect against multiple subquery values is a bug waiting to happen. It can crash merely because of minor data changes in the tables it references—not a good thing.

A correlated subquery is a subselect that is restricted by, and very often restricts, a table in the outer query. It usually references this table via the table’s alias as specified in the outer query.

In a sense, correlated subqueries behave like traditional looping constructs. For each row in the outer table, the subquery is reexecuted with a new set of parameters. On the other hand, a correlated subquery is much more efficient than the equivalent Transact-SQL looping code. It’s far more efficient to iterate through a table using a correlated subquery than with, say, a WHILE loop.

Here’s an example of a basic correlated subquery:

In this query, the subquery is executed for each row in titles. As it’s executed each time, it’s qualified by the title_id column in the outer table. This means that the SUM it returns will correspond to the current title_id of the outer query. This total, in turn, is used to limit the titles returned to those with sales in excess of 30 units.

Of course, this query could easily be restated as a join, but the point of the exercise is to show the way in which subqueries and their hosts can be correlated.

Note that correlated subqueries need not be restricted to the WHERE clause. Here’s an example showing a correlated subquery in the SELECT list:

In this example, the subquery is restricted by the outer query, but it does not affect which rows are returned by the query. The outer query depends upon the subquery in the sense that it renders one of its column values but not to the degree that it affects which rows are included in the result set.

As covered in the section on predicates, a scalar value can be compared with the result set of a subquery using special predicate functions such as IN, EXISTS, ANY, and ALL. Here’s an example:

In this example, subqueries reference two separate fields from the outer query—ytd_sales and price—in order to compute the total sales to date for each title. There are two subqueries here, one nested within the other, and both are correlated with the main query. The innermost subquery computes the total unit sales for a given title. It’s necessary because sales is likely to contain multiple rows per title since it lists individual purchases. The outer subquery takes this total, multiplies it by the book unit price, and adds the title’s year-to-date sales in order to produce a sales-to-date total for each title. Those titles with sales in excess of $5000 are then returned by the subquery and tested by the IN predicate.

As I’ve said, joins are often preferable to subqueries because they tend to run more efficiently. Here’s the previous query rewritten as a join:

Though joins are often preferable to subqueries, there are other times when a correlated subquery is the better solution. For example, consider the case of locating duplicate values among the rows in a table. Let’s say that you have a list of Web domains and name servers and you want to locate each domain with the same name servers as some other domain. A domain can have no more than two name servers, so your table has three columns (ignore for the moment that these are unnormalized, repeating values). You could code this using a correlated subquery or as a self-join, but the subquery solution is better. To understand why, let’s explore both methods. First, here’s the self-join approach:

We join with a second instance of the name server table and set up the entirety of the conditions on which we’re joining in the ON clause of the JOIN. For each row in the first instance of the table, we scan the second instance for rows where a) the domain is different and b) the pair of name servers is the same. We’re careful to look for domains where the name servers have been reversed as well as those that match exactly.

Now, here’s the same query expressed using a subquery:

Why is this better than the self-join? Because the EXISTS predicate returns as soon as it finds a single match, regardless of how many matches there may be. The performance advantage of the subquery over the self-join will grow linearly as more duplicate name server pairs are added to the table.

As with the self-join, this approach includes rows where the name servers have been reversed. If we didn’t want to consider those rows duplicates, we could streamline the query even further, like this:

This query groups its results on the ns1 and ns2 columns and returns only pairs with more than one occurrence. Every pair will have one occurrence—itself. Those with two or more are duplicates of at least one other pair.

Closely related to the aggregate functions are the GROUP BY and HAVING clauses. GROUP BY divides a table into groups, and each group can have its own aggregate values. As I said earlier, HAVING limits the groups returned by GROUP BY.

With the exception of bit, text, ntext, and image columns, any column can participate in the GROUP BY clause. To create groups within groups, simply list more than one column. Here’s a simple GROUP BY example:

It’s pretty common to need to reshape vertically oriented data into horizontally oriented tables suitable for reports and user interfaces. These tables are known as pivot tables or cross-tabulations (cross-tabs) and are an essential feature of any OLAP (Online Analytical Processing), EIS (Executive Information System), or DSS (Decision Support System) application.

SQL Server includes a bevy of OLAP support tools that are outside the scope of this book. Install the OLAP Services from your SQL Server CD, and view the product documentation for more information.

That said, the task of reshaping vertical data is well within the scope of this book and is fairly straightforward in Transact-SQL. Let’s assume we start with this table of quarterly sales figures:

And let’s say that we want to produce a cross-tab consisting of six columns: the year, a column for each quarter, and the total sales for the year. Here’s a query to do the job:

Note that it isn’t necessary to total the Qn columns to produce the annual total. The query is already grouping on the yr column; all it has to do to summarize the annual sales is include a simple aggregate. There’s no need for a subquery, derived table, or any other exotic construct, unless, of course, there are sales records that fall outside quarters 1–4, which shouldn’t be possible.

The qtr column in the sample data made constructing the query fairly easy—almost too easy. In practice, it’s pretty rare for time series data to include a quarter column—it’s far more common to start with a date for each series member and compute the required temporal dimensions. Here’s an example that uses the Orders table in the Northwind database to do just that. It translates the OrderDate column for each order into the appropriate temporal boundary:

This query returns a count of the orders for each quarter as well as for each year. It uses the DATEPART() function to extract each date element as necessary. As the query iterates through the Orders table, the CASE functions evaluate each OrderDate to determine the quarter “bucket” into which it should go, then return either “1”—the order is counted against that particular quarter—or NULL—the order is ignored.

The GROUP BY clause’s CUBE and ROLLUP operators add summary rows to result sets. CUBE produces a multidimensional cube whose dimensions are defined by the columns specified in the GROUP BY clause. This cube is an explosion of the underlying table data and is presented using every possible combination of dimensions.

ROLLUP, by contrast, presents a hierarchical summation of the underlying data. Summary rows are added to the result set based on the hierarchy of grouped columns, from left to right.

Here’s an example that uses the ROLLUP operator to generate subtotal and total rows:

This query has several noteworthy features. First, note the extra rows that ROLLUP inserted into the result set. Since the query groups on the stor_name and type columns, ROLLUP produces summary rows first for each stor_name group (ALL TYPES), then for the entire result set.

The GROUPING() function is used to translate the label assigned to each grouping column. Normally, grouping columns are returned as NULLs. By making use of GROUPING(), the query is able to translate those NULLs to something more meaningful.

Here’s that same query again, this time using CUBE:

Note the additional rows at the end of the result set. In addition to the summary rows generated by ROLLUP, CUBE creates subtotals for each type of book as well.

Without detailed knowledge of your data, it’s nearly impossible to know how many rows will be returned by CUBE. However, computing the upper limit of the number of possible rows is trivial. It’s the cross product of the number of unique values 11 for each grouping column. The “+1” is for the ALL summary record generated for each attribute. In this case, there are six distinct stores and six distinct book types in the sales table. This means that a maximum of forty-nine rows will be returned in the CUBEd result set (6+1 * 6+1). Here, there are fewer than forty-nine rows because not every store has sold every type of book.

On a related note, you’ll notice that CUBE doesn’t generate zero subtotals for book types that a particular store hasn’t sold. It might be useful to have these totals so that we can see what the store is and isn’t selling. Having the full cube creates a result set that is dimensioned more uniformly, making it easier to create reports and charts over it. Here’s a full-cube version of the last query:

This query begins by creating a zero-value table of stores and book types by multiplying the stores in the stores table by the book types in the titles table using a CROSS JOIN. It then UNIONs this set with the sales table to produce a composite that includes the sales records for each store, as well as a zero value for each store–book type combo. This is then fed into the outer grouping query as a derived table. The outer query then groups and summarizes as necessary to produce the result set. Note that there are forty-nine rows in the final result set—exactly the number we predicted earlier.

There are a few caveats and limitations related to CUBE and ROLLUP of which you should be aware:

As I said earlier, HAVING restricts the rows returned by GROUP BY similarly to the way that WHERE restricts those returned by SELECT. It is processed after the rows are collected from the underlying table(s) and is therefore less efficient for garden-variety row selection than WHERE. In fact, behind the scenes, SQL Server implicitly converts a HAVING that would be more efficiently stated as a WHERE automatically. This means that the execution plans generated for the following queries are identical:

In the second query, HAVING doesn’t do anything that WHERE couldn’t do, so SQL Server converts it to a WHERE during query execution so that the number of rows processed by GROUP BY is as small as possible.