Shane Cleary

T-SQL

SQL SELECT

A typical mistake made by people who don’t understand logical query processing is attempting to refer in the WHERE clause to a column alias defined in the SELECT clause. You can’t do this because the WHERE clause is evaluated before the SELECT clause.

Group By - This phase defines a group for each distinct combination of values in the grouped elements from the input virtual table. It then associates each input row to its respective group.

FROM

• It’s the clause where you indicate the tables that you want to query.
• It’s the clause where you can apply table operators like joins to input tables.

In the FROM clause, you can alias the queried tables with your chosen names, as in HR.Employees E. When using aliases, the convention is to use short names, typically one letter that is somehow indicative of the queried table, like E for Employees. Then, when referring to an ambiguous column name in a multi-table query (same column name appears in multiple queried tables), to avoid ambiguity, you add the table alias as a column prefix.

The SELECT clause

The SELECT clause of a query has two main roles:

It evaluates expressions that define the attributes in the query’s result, assigning them with aliases if needed.

Using a DISTINCT clause, you can eliminate duplicate rows in the result if needed.

It is considered a best practice to always explicitly list the attributes that you need.

Delimiting Identifiers

Need to make an additional setting so that Specflow uses MSTest framework for running the test.

When the identifier is regular, delimiting it is optional. In a regular identifier, the identifier complies with the rules for formatting identifiers. The identifier cannot be a reserved keyword in T-SQL, cannot have embedded spaces, and must not include supplementary characters. T-SQL supports both a standard form to delimit identifiers using double quotation marks, as in “Sales”.”Orders”, as well as a proprietary form using square brackets, as in [Sales]. [Orders]. The latter is the more commonly used, and recommended, form in T-SQL.

Nulls

T-SQL supports the IS NULL and IS NOT NULL operators to check if a NULL is or isn’t present, respectively.

Combining predicates

You can combine predicates in the WHERE clause by using the logical operators AND and OR. You can also negate predicates by using the NOT logical operator.

The NOT operator precedes AND and OR, and AND precedes OR.

For example, suppose that the WHERE filter in your query had the following combination of predicates:

WHERE col1 = 'w' AND col2 = 'x' OR col3 = 'y' AND col4 = 'z'

Because AND precedes OR, you get the equivalent of the following:

WHERE (col1 = 'w' AND col2 = 'x') OR (col3 = 'y' AND col4 = 'z')

Trying to express the operators as pseudo functions, this combination of operators is equivalent to OR( AND( col1 = ‘w’, col2 = ‘x’ ), AND( col3 = ‘y’, col4 = ‘z’ ) ).

Because parentheses have the highest precedence among all operators, you can always use those to fully control the logical evaluation order that you need, as the following example

shows:

WHERE col1 = 'w' AND (col2 = 'x' OR col3 = 'y') AND col4 = 'z'

Filtering character data

If you want to look for a character that is considered a wildcard, you can indicate it after a character that you designate as an escape character by using the ESCAPE keyword. For example, the expression col1 LIKE ‘!_%’ ESCAPE ‘!’ looks for strings that start with an underscore (_) by using an exclamation point (!) as the escape character. Alternatively, you can place the wildcard in square brackets, as in col1 LIKE ‘[_]%’.

Sorting data

SQL does not sort data in any particular order. To order use the ORDER BY clause. If you don’t indicate a direction for sorting, ascending order (ASC) is assumed by default. You can specify multiple expressions in the ORDER BY list, separated by commas.

Filtering data with TOP

With the TOP option, you can filter a requested number or percent of rows from the query result based on indicated ordering. You specify the TOP option in the SELECT clause followed by the requested number of rows in parentheses (as a BIGINT typed value).

SELECT TOP (3) orderid, orderdate,

Filtering by percent: SELECT TOP (1) PERCENT

Query multiple tables using Joins

Cross joins

This join performs what’s known as a Cartesian product. If you have m rows in table T1 and n rows in table T2, the result of a cross join between T1 and T2 is a virtual table with m × n rows.

Inner joins

With an inner join, you can match rows from two tables based on a predicate—usually one that compares a primary key value in one side to a foreign key value in another side. The inner join returns only matching rows for which the predicate evaluates to true.

Outer joins

With outer joins, you can request to preserve all rows from one or both sides of the join, never mind if there are matching rows in the other side based on the ON predicate.

By using the keywords LEFT OUTER JOIN (or LEFT JOIN for short), you ask to preserve the left table. The join returns what an inner join normally would—that is, matches (call those inner rows). In addition, the join also returns rows from the left table that have no matches in the right table (call those outer rows), with NULLs used as placeholders in the right side.

Left outer join preserves the table on the left.

Right outer join preserves the table on the right.

Full outer join preserves both tables.

A full outer join returns the matched rows, which are normally returned from an inner join; plus rows from the left that don’t have matches in the right, with NULLs used as placeholders in the right side; plus rows from the right that don’t have matches in the left, with NULLs used as placeholders in the left side. It’s not common to need a full outer join because most relationships between tables allow only one of the sides to have rows that don’t have matches in the other, in which case, a one-sided outer join is needed.

Composite Join

When you need to join tables that are related based on multiple columns, the join is called a composite join and the ON clause typically consists of a conjunction of predicates (predicates separated by AND operators) that match the corresponding columns from the two sides.

Query above will not deal with NULLs. To check for NULLs,

Multi-join queries

It’s important to remember that a join in T-SQL takes place conceptually between two tables at a time. A multi-join query evaluates the joins conceptually from left to right. So the result of one join is used as the left input to the next join.