Set theory, which originated with the mathematician Georg Cantor, is one of the mathematical branches on which the relational model is based. Cantor’s definition of a set follows:

Every word in the definition has a deep and crucial meaning. The definitions of a set and set membership are axioms that are not supported by proofs. Each element belongs to a universe, and either is or is not a member of the set.

Let’s start with the word whole in Cantor’s definition. A set should be considered a single entity. Your focus should be on the collection of objects as opposed to the individual objects that make up the collection. Later on, when you write T-SQL queries against tables in a database (such as a table of employees), you should think of the set of employees as a whole rather than the individual employees. This might sound trivial and simple enough, but apparently many programmers have difficulty adopting this way of thinking.

The word distinct means that every element of a set must be unique. Jumping ahead to tables in a database, you can enforce the uniqueness of rows in a table by defining key constraints. Without a key, you won’t be able to uniquely identify rows, and therefore the table won’t qualify as a set. Rather, the table would be a multiset or a bag.

Predicate logic, is another branch of mathematics on which the relational model is based. Dr. Edgar F. Codd, in creating the relational model, had the insight to connect predicate logic to both management and querying of data. Loosely speaking, a predicate is a property or an expression that either holds or doesn’t hold—in other words, is either true or false. The relational model relies on predicates to maintain the logical integrity of the data and define its structure. One example of a predicate used to enforce integrity is a constraint defined in a table called Employees that allows only employees with a salary greater than zero to be stored in the table. The predicate is “salary greater than zero” (T-SQL expression: \(salary > 0\)).

In set theory, you can use predicates to define sets. This is helpful because you can’t always define a set by listing all its elements (for example, infinite sets), and sometimes for brevity it’s more convenient to define a set based on a property. As an example of an infinite set defined with a predicate, the set of all prime numbers can be defined with the following predicate: “x is a positive integer greater than 1 that is divisible only by 1 and itself.” For any specified value, the predicate is either true or not true. The set of all prime numbers is the set of all elements for which the predicate is true. As an example of a finite set defined with a predicate, the set {0, 1, 2, 3, 4, 5, 6, 7, 8, 9} can be defined as the set of all elements for which the following predicate holds true: “x is an integer greater than or equal to 0 and smaller than or equal to 9.”

The common belief that the term relational stems from relationships between tables is incorrect. “Relational” actually pertains to the mathematical term relation. In set theory, a relation is a representation of a set. In the relational model, a relation is a set of related information, with the counterpart in SQL being a table—albeit not an exact counterpart. A key point in the relational model is that a single relation should represent a single set (for example, Customers). It is interesting to note that operations on relations (based on relational algebra) result in a relation (for example, a join between two relations).

When you design a data model for a database, you represent all data with relations (tables). You start by identifying propositions that you will need to represent in your database. A proposition is an assertion or a statement that must be true or false. For example, the statement, “Employee Itzik BenGan was born on February 12, 1971, and works in the IT department” is a proposition. If this proposition is true, it will manifest itself as a row in a table of Employees. A false proposition simply won’t manifest itself. This presumption is known as the close world assumption (CWA).

Database tables are objects that store all the data in a database. In a table, data is logically organized in a row-and-column format which is similar to a spreadsheet.

To query data from a table, you use the SELECT statement. The following illustrates the most basic form of the SELECT statement:

SELECT
    select_list
FROM
    schema_name.table_name;

The SELECT TOP clause allows you to limit the number of rows or percentage of rows returned in a query result set.

SELECT TOP (expression) [PERCENT]
    [WITH TIES]
FROM
    table_name
ORDER BY
    column_name;

The FROM clause of a query is the first clause to be logically processed, and within the FROM clause, table operators operate on input tables. Microsoft SQL Server supports four table opera- tors—JOIN, APPLY, PIVOT, and UNPIVOT. The JOIN table operator is standard, whereas APPLY, PIVOT, and UNPIVOT are T-SQL extensions to the standard. Each table operator acts on tables provided to it as input, applies a set of logical query processing phases, and returns a table result.

A JOIN table operator operates on two input tables. The three fundamental types of joins are cross joins, inner joins, and outer joins. These three types of joins differ in how they apply their logical query processing phases; each type applies a different set of phases. A cross join applies only one phase—Cartesian Product. An inner join applies two phases—Cartesian Product and Filter. An outer join applies three phases—Cartesian Product, Filter, and Add Outer Rows.

Logical query processing describes a generic series of logical steps that for any specified query produces the correct result, whereas physical query processing is the way the query is processed by the RDBMS engine in practice. Some phases of logical query processing of joins might sound inefficient, but the inefficient phases will be optimized by the physical implementation. It’s important to stress the term logical in logical query processing. The steps in the process apply operations to the input tables based on relational algebra. The database engine does not have to follow logical query processing phases literally, as long as it can guarantee that the result that it produces is the same as that dictated by logical query processing. The SQL Server relational engine often applies many shortcuts for optimization purposes when it knows that it can still produce the correct result.

There are circumstances where we would like to apply the aggregate function not only to a single set of tuples, but also to a group of sets of tuples; we specify this in SQL using the group by clause. The attribute or attributes given in the group by clause are used to form groups. Tuples with the same value on all attributes in the group by clause are placed in one group.

As an illustration, consider the query “Find the average salary in each department.” We write this query as follows

select dept name, avg (salary) as avg salary
from instructor
group by dept name;

SQL provides a mechanism for nesting subqueries. A subquery is a select-from-where expression that is nested within another query. A common use of subqueries is to perform tests for set membership, make set comparisons, and determine set cardinality by nesting subqueries in the where clause.

Producing multiple copies of rows can be achieved with a fundamental technique that utilizes a cross join. If you need to produce five copies of each employee row, you need to perform a cross join be- tween the Employees table and a table that has five rows; alternatively, you can perform a cross join between Employees and a table that has more than five rows, but filter only five from that table in the WHERE clause. The Nums table is very convenient for this purpose. Simply cross Employees and Nums, and filter from Nums as many rows as the number of requested copies (five, in this case).

create table temptable
(
    id int
);
insert into temptable
values
    (1),
    (2),
    (3),
    (4),
    (5);

select stf.staff_id empid, stf.first_name firstname, last_name lastname, tmp.id n
from sales.staffs as stf cross join temptable as tmp
order by firstname, n;

drop table temptable;

| empid | firstname  | lastname | n |
|-------+------------+----------+---|
|    10 | Bernardine | Houston  | 1 |
|    10 | Bernardine | Houston  | 2 |
|    10 | Bernardine | Houston  | 3 |
|    10 | Bernardine | Houston  | 4 |
|    10 | Bernardine | Houston  | 5 |
|     1 | Fabiola    | Jackson  | 1 |
|     1 | Fabiola    | Jackson  | 2 |
|     1 | Fabiola    | Jackson  | 3 |
|     1 | Fabiola    | Jackson  | 4 |
|     1 | Fabiola    | Jackson  | 5 |
Load 10 more... (10/50)

• The different between UNION and UNION ALL is that, UNION will remove duplicates from your query, UNION ALL will not.

Derived tables (also known as table subqueries) are defined in the FROM clause of an outer query. Their scope of existence is the outer query. As soon as the outer query is finished, the derived table is gone.

You specify the query that defines the derived table within parentheses, followed by the AS clause and the derived table name. For example, the following code defines a derived table called USACusts based on a query that returns all customers from the United States, and the outer query selects all rows from the derived table.

USE TSQL2012;
SELECT *
FROM (SELECT custid, companyname
FROM Sales.Customers
WHERE country = N'USA') AS USACusts;

A query must meet 2 requirements to be valid to define a table expression of any kind:

1. Order is not guaranteed. A table expression is supposed to represent a relational table, and the rows in a relational table have no guaranteed order. Recall that this aspect of a relation stems from set theory. For this reason, standard SQL disallows an ORDER BY clause in queries that are used to define table expressions, unless the ORDER BY serves another purpose besides presentation. An example for such an exception is when the query uses the OFFSET-FETCH filter. T-SQL enforces similar restrictions, with similar exceptions—when TOP or OFFSET-FETCH is also specified. In the context of a query with the TOP or OFFSET-FETCH filter, the ORDER BY clause serves as part of the specification of the filter. If you use a query with TOP or OFFSET-FETCH and ORDER BY to define a table expression, ORDER BY is only guaranteed to serve the filtering-related purpose and not the usual presentation purpose. If the outer query against the table expression does not have a presentation ORDER BY, the output is not guaranteed to be returned in any particular order. See the “Views and the ORDER BY Clause” section later in this chapter for more detail on this item.
2. All columns must have names. All columns in a table must have names; therefore, you

must assign column aliases to all expressions in the SELECT list of the query that is used to define a table expression.

Common table expressions (CTEs) are another standard form of table expression very similar to derived tables, yet with a couple of important advantages. CTEs are defined by using a WITH statement and have the following general form.

WITH <CTE_Name>[(<target_column_list>)]
AS
(
<inner_query_defining_CTE>
)
<outer_query_against_CTE>;

The inner query defining the CTE must follow all requirements mentioned earlier to be valid to define a table expression. As a simple example, the following code defines a CTE called USACusts based on a query that returns all customers from the United States, and the outer query selects all rows from the CTE.

WITH USACusts AS
(
    SELECT custid, companyname
    FROM Sales.Customers
    WHERE country = N'USA'
)
SELECT * FROM USACusts;

With CTE, there are two ways of assigning aliases:

• The inline form:

  WITH C AS
  (
  SELECT YEAR(orderdate) AS orderyear, custid
  FROM Sales.Orders
  )
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C
  GROUP BY orderyear;
  

• The external form:

  WITH C(orderyear, custid) AS
  (
  SELECT YEAR(orderdate), custid
  FROM Sales.Orders
  )
  SELECT orderyear, COUNT(DISTINCT custid) AS numcusts
  FROM C
  GROUP BY orderyear;
  

CTEs are unique among table expressions because they have recursive capabilities. A recursive CTE is defined by at least two queries (more are possible)—at least one query known as the anchor member and at least one query known as the recursive member. The general form of a basic recursive CTE looks like the following.

WITH <CTE_Name>[(<target_column_list>)]
AS
(
<anchor_member>
UNION ALL
<recursive_member>
)
<outer_query_against_CTE>;

The anchor member is a query that returns a valid relational result table—like a query that is used to define a nonrecursive table expression. The anchor member query is invoked only once.

The recursive member is a query that has a reference to the CTE name. The reference to the CTE name represents what is logically the previous result set in a sequence of executions. The first time that the recursive member is invoked, the previous result set represents whatever the anchor member returned. In each subsequent invocation of the recursive member, the reference to the CTE name represents the result set returned by the previous invocation of the recursive member. The recursive member has no explicit recursion termination check—the termination check is implicit. The recursive member is invoked repeatedly until it returns an empty set or exceeds some limit.

Both queries must be compatible in terms of the number of columns they return and the data types of the corresponding columns.

The reference to the CTE name in the outer query represents the unified result sets of the invocation of the anchor member and all invocations of the recursive member.

The following code demonstrates how to use a recursive CTE to return information about an employee (Don Funk, employee ID 2) and all of the employee’s subordinates in all levels (direct or indirect).

WITH EmpsCTE AS
(
    SELECT empid, mgrid, firstname, lastname
    FROM HR.Employees
     WHERE empid = 2
     UNION ALL
    SELECT C.empid, C.mgrid, C.firstname, C.lastname
     FROM EmpsCTE AS P
    JOIN HR.Employees AS C
     ON C.mgrid = P.empid
)
SELECT empid, mgrid, firstname, lastname
FROM EmpsCTE;

The two types of table expressions discussed so far—derived tables and CTEs—have a very limited scope, which is the single-statement scope. As soon as the outer query against those table expressions is finished, they are gone. This means that derived tables and CTEs are not reusable.

Views and inline table-valued functions (inline TVFs) are two reusable types of table expressions; their definitions are stored as database objects. After they have been created, those objects are permanent parts of the database and are only removed from the database if they are explicitly dropped.

IF OBJECT_ID ('Sales.USACusts') IS NOT NULL DROP VIEW Sales.USACusts;

GO
CREATE VIEW Sales.USACusts
AS
    SELECT
        custid,
        companyname,
        contactname,
        contacttitle,
        address,
        city,
        region,
        postalcode,
        country,
        phone,
        fax
    FROM
        Sales.Customers
    WHERE
  country = N'USA';

GO

Note that the general recommendation to avoid using SELECT * has specific relevance in the context of views. The columns are enumerated in the compiled form of the view, and new table columns will not be automatically added to the view. For example, suppose you define a view based on the query SELECT * FROM dbo.T1, and at the view creation time the table T1 has the columns col1 and col2. SQL Server stores information only on those two columns in the view’s metadata. If you alter the definition of the table to add new columns, those new columns will not be added to the view. You can refresh the view’s metadata by using the stored procedure sp_refreshview or sp_refreshsqlmodule, but to avoid confusion, the best practice is to explicitly list the column names that you need in the definition of the view. If columns are added to the underlying tables and you need them in the view, use the ALTER VIEW statement to revise the view definition accordingly.

Inline TVFs are reusable table expressions that support input parameters. In all respects except for the support for input parameters, inline TVFs are similar to views. For this reason, I like to think of inline TVFs as parameterized views, even though they are not called this formally.

USE TSQL2012;

IF OBJECT_ID ('dbo.GetCustOrders') IS NOT NULL DROP FUNCTION dbo.GetCustOrders;

GO
CREATE FUNCTION dbo.GetCustOrders (@cid AS int)
  RETURNS TABLE
  AS RETURN
  SELECT
    orderid,
    custid,
    empid,
    orderdate,
    requireddate,
    shippeddate,
    shipperid,
    freight,
    shipname,
    shipaddress,
    shipcity,
    shipregion,
    shippostalcode,
    shipcountry
FROM
    Sales.Orders
WHERE
    custid = @cid;

GO

The APPLY operator is a very powerful table operator. Like all table operators, this operator is used in the FROM clause of a query. The two supported types of APPLY operator are CROSS APPLY and OUTER APPLY. CROSS APPLY implements only one logical query processing phase, whereas OUTER APPLY implements two.

SQL defines precedence among set operators. The INTERSECT operator precedes UNION and EXCEPT, and UNION and EXCEPT are considered equal. In a query that contains multiple set operators, first INTERSECT operators are evaluated, and then operators with the same precedence are evaluated based on order of appearance.

Consider the following query, which shows how INTERSECT precedes EXCEPT.

SELECT country, region, city FROM Production.Suppliers
EXCEPT
SELECT country, region, city FROM HR.Employees
INTERSECT
SELECT country, region, city FROM Sales.Customers;

Because INTERSECT precedes EXCEPT, the INTERSECT operator is evaluated first, even though it appears second. Therefore, the meaning of this query is, “locations that are supplier locations but not (locations that are both employee and customer locations).”

Write a query that returns customer and employee pairs that had order activity in January 2008 but not in February 2008.