Chapter 4 Subqueries

Subqueries are queries contained within other queries, often known as the outer and inner query. This chapter look at the various types of nested queries.

The chapter starts by looking at self-contained queries, here the outer query has no effect on the inner query. Various helpful examples are provided (e.g. single and multivalued). The independent nature of self-contained queries makes them particularly easy to debug.

The chapter continues with a look at correlated subqueries, here the inner query refers to attributes that are provided by the outer query, so the inner query is dependent on the outer query. An example includes getting the maximum order id associated with each customer. Since the queries can’t be run independently it makes debugging more troublesome. The use of the EXISTS predicate is used to illustrate how the existence of any row in the inner query will return TRUE.

The chapter ends with a look at subqueries from an advanced viewpoint. Queries are provided to return previous and next values and creating running aggregates. Finally, some of the more common subquery problems are examined.

This chapter provides useful examples to illustrate both self-contained and correlated subqueries.  

Chapter 5 Table Expressions

Table expressions are named query expressions, represented as valid virtual tables, which can be used like normal tables in your SQL. There are 4 types discussed in the chapter.

The chapter opens with a look at derived tables (also called table subqueries), they are defined on the outer query’s FROM clause. Restrictions include: the row order is not guaranteed and all columns must be named and unique. Examples are provided of using derived tables with arguments, multiple levels of nesting, and with multiple references.

Next, Common Table Expressions (CTEs) are examined, these are similar to derived tables but with advantages, including simpler SQL and recursion. The general syntax of a CTE is given, together with a simple example. As with derived tables, arguments can be supplied to CTEs. Extended examples are given showing how to define multiple CTEs, and recursive CTEs.

The third table expression given is that of the VIEW, these have the advantage of being reusable. Views are stored as definitions and populated when they are used. The use of TOP and ORDER BY to order the view data is discussed. Various view options are examined including encryption, schemabinding, and check option.

The last table expression type discussed is Inline Table-Valued Functions (TVF), these too are reusable. In many ways they are similar to views, except they support input parameters (they’re often thought of as parameterized views). Example code is provided and the results examined.

The chapter ends with a look at the powerful APPLY table operator that is used together with table expressions. Example code is provided and the results discussed for both CROSS APPLY and OUTER APPLY.

This chapter discussed the 4 types of table expressions together with the APPLY table operator. Plenty of useful examples are provided together with helpful discussions.

Chapter 6 Set Operators

Set operators act on two input sets, the T-SQL features that implement set operators are UNION, INTERSECT, and EXCEPT.

The chapter opens with a look at the UNION operator, explained with the help of a Venn diagram. The union operator combines the data in both sets, removing any duplicates. The associated UNION ALL operator does the same thing but doesn’t remove the duplicates, this can improve performance. As always, helpful examples are provided.

Next the INTERSECT operator is explained, again with a Venn diagram. The INTERSECT operator creates a set that contains data that is common between the two input sets, again duplicates are removed. The corresponding INTERSECT ALL operator doesn’t exist in T-SQL, but the author provides some equivalent code and example usage.

Lastly, the EXCEPT operator is discussed, again with a Venn diagram. The EXCEPT operator creates a set that contains data that is in one of the input sets but not the other, again removing any duplicates. The corresponding EXCEPT ALL operator doesn’t exist in T-SQL, but the author again provides some equivalent code and example usage.

The chapter ends with a small section on set operator precedence. The UNION and EXCEPT set operators are considered to be equal, whereas INTERSECT precedes both. Example code is provided to illustrate this.

This has been a useful chapter, discussing some of the more explicit set functionality, explaining to the reader how set processing works.

Chapter 7 Beyond the Fundamentals of Querying

This chapter is optional reading for readers that are new to T-SQL, since it contains some advanced functionality. It can be read later when the user has more experience.

The chapter opens with a look at windows functions, these allow you to perform dynamic calculations in a flexible manner. Here the window represents a subset of data, and uses the OVER clause. Examples discussed include Partition By, Order By and framing (i.e. rows between). There’s a useful reference to the author’s book “Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions” www.amazon.com/Microsoft-High-Performance-Functions-Developer-Reference/dp/0735658366/.

Next, various ranking functions are discussed. Ranking allows you to apply functions to rank a row in terms of others. The following 4 ranking functions are discussed with examples: ROW_NUMBER, RANK, RANK_DENSE, and NTILE. The section then looks at offset window functions, returning rows based on their offset from the current row. The offset window functions discussed are: LAG, LEAD, FIRST_VALUE and LAST_VALUE. The section ends with a look at some aggregated windows functions.

The chapter now moves on to discussing pivoting data, which involves transforming rows into columns, and typically performing some aggregation too. Helpful example code is provided showing pivoting using the CASE expressions, and with the native T-SQL PIVOT operator.

The next section discusses unpivoting data, which is basically the reverse of pivoting, and is often applied to data that has previously been pivoted. Examples are provided showing unpivoting using both standard SQL and with the native T-SQL UNPIVOT operator.

The chapter ends with a look at Grouping Sets, this is a set of attributes by which you can group. It is possible to use the UNION ALL set operator to group various grouping sets, or use the GROUPING SETS subclause - an enhancement of the GROUP BY clause. Code is provided that shows how the CUBE and ROLLUP subclauses can be used to provide shortcuts to defining multiple grouping sets.

This chapter discusses the advantages of using various windows functions. As always many examples are included and helpful discussions provided. There’s a useful reference to the author’s more advanced book for readers that want to take things further.