Set and Union Operations

When you are performing a set operation such as a UNION, you need to abide by certain rules. The types returned from the two sets must be compatible. There cannot be an ORDER BY in either of the queries. If you do have different column names in the queries, the result will get the names from the first set.

The UNION operator implicitly does a DISTINCT on the results. That takes up some extra time. If you don’t mind duplicates in the data, you can instead use the UNION ALL operator that allows duplicates.

The INTERSECT operator returns items that are in common between the two sides. This will also implicitly perform a DISTINCT and not return any duplicates. The EXCCEPT operator returns a difference. It gives you items in the first query that do not appear in the second query results.

Apply

Previously we had talked about the different type of Table Expressions. Usually you are combining a table expression with another table on the left. The way to connect the two is to use APPLY. It is possible for the table expression to access items (columns) from the outer table.

There are two different types of APPLY connectors. One is the CROSS APPLY. In this scenario, a row is not returned unless there is a match on the inner Table Expression. There is also an OUTER APPLY. It works similarly to an outer join. It returns all rows from the left-hand side table, even if there are no matches with the table expression. It will also return right hand side rows that match.

So far we have been talking about running queries and dealing with data as sets. You can perform logical operators on multiple sets. These include the UNION, INTERSECT, and EXCEPT operators. There are certain rules that set operations must conform to. I will save that for a future post.

Table Expressions

Let’s talk about table expressions. I will start with Derrived Tables. They are like subqueries. The return a table result. You put the query inside parentheses after the FROM keyword. The results are only visible to the outer query. In Oracle, we call these inline views.

Next let’s discuss Common Table Expressions (CTEs). They are another type of table expression. Like derived tables, they are only visible to the outer queries. You put the expression in parentheses after the WITH keyword at the beginning of your query. You can have multiple CTEs in the WITH clause separated by commas. You could also make them recursive if you connect them with UNION ALL.

Next there are Views. You are not allowed to pass parameters to a view. Normally just the SQL of the view is stored internally. That SQL is executed every time the view is accessed to generate real time results.

Finally, there are Inline Table Valued Functions. These can have input parameters. The function should be declared as RETURNS TABLE. Then in the body of the function you RETURN a SELECT statement results.

Subqueries

Normally you SELECT values from database tables. However, you can also SELECT values from another query. These other queries are called subqueries. The subquery can be a stand-alone SQL statement. Or it can reference the outer query, making it a correlated subquery.

EXISTS is a procedure is found in the WHERE clause of a query. EXISTS takes a parameter that is a subquery. EXISTS will returns a value of TRUE if the subquery has at least one row in its results.

A Table Expression is a named query. Every column in it must have a name. There is normally no ORDER BY allowed in a Table Expression. The exception to this is when you are using TOP. There are four different types of Table Expressions: (1) derived tables, (2) CTEs, (3) views, and (4) inline table valued functions. These four type will require a separate writeup to explain.

Joining Tables

Most of the time when you are query data, you will need to retrieve data from multiple tables. To connect them you will need to employ a JOIN. There are multiple types of joins depending on your needs. One rare type is the CROSS JOIN. This produces a Cartesian product of two tables. That means every row in the first table is paired with every row in the second table. If the first table has m rows, and the second has n rows, the CROSS JOIN will produce m times n number of rows in the results.

The most common type of JOIN is the INNER JOIN. This is the default JOIN type. It will only connect rows in the first and second tables when there is a match with the values of the columns you are joining on.

Another common type of JOIN is the LEFT OUTER JOIN. This is also known as the LEFT JOIN. This will produce results that have all of the rows from the first (left) table, plus any rows in the second table that have matching values in the column you are joining on. Related is the RIGHT OUTER JOIN. This is like a LEFT OUTER JOIN. But the roles of the tables are reversed. You get all rows from the second (right) table, plus any in the first table that match.

Sorting Results

When you sort by a column using ORDER BY, the default order is in ascending values. You can specify the column or columns you want to sort by name. Or you could use the ordinal position in the ORDER BY, which refers to the position of the column in the SELECT clause. Thus if you ORDER BY 1, you are telling SQL Server to order by the first column in the SELECT clause.

In the case of NULLs, they come before non-NULL values in the ORDER BY. Instead of sorting, you can choose to get a subset of query results with TOP. If you pass in a number x, that means you want just x number of rows. You could also specify TOP(p) PERCENT, and that gives you a percentage of records back from the whole query results. You can also specify TOP(x) WITH TIES, which means you get at least x number of rows. You might also get extra rows in case some of those top values have duplicates.

You can also jump down and get some rows in the middle/end of the query result set. You do this with OFFSET(n) ROWS. That will skip n rows in the results and output the rest. Or you could couple it with FETCH NEXT m ROWS ONLY. That will skip the n rows, and then give you the next m rows in the query results.

The Predicate Clause

I have mentioned that parts of a SELECT statement to do queries. Certain pieces of it are called predicates. These include ON, WHERE and HAVING. Some categories of predicates are Search Arguments. Those can take advantage of indexes for better performance. This can only happen if the predicate does not use functions like COALESCE or ISNULL. If it does, it is not a Search Argument and the indexes cannot be used.

You can have multiple clauses in a predicate. They are connected by AND or OR keywords. The AND has a higher precedence than OR. That means in a statement with multiple AND/OR combinations, the AND operations are evaluated first.

When doing checks on character (string) values, you can use the LIKE operator. The LIKE works with a string with special values such as the % which represents any characters. The underscore represents any single character. A list of characters in braces means any one character from that list. And if you put a caret (^) in front of the list, it means any character that is not in that list.