T-SQL Expressions Reference

Expressions

An expression is a combination of one or more values, operators, and SQL statements that evaluate to a single value. Expressions can be used in many Transact-SQL statements, including SELECT, WHERE, HAVING, ORDER BY, and SET clauses.

Types of Expressions

Scalar Expressions: Return a single value. Examples include literal values, column names, variables, and function calls that return a single value.
Table Expressions: Return a table as a result. Examples include subqueries that return a result set, or table-valued functions.

Scalar Expression Components

Literals

A literal is a data value that is directly represented in the source code. Examples include:

String literals: 'Hello World', N'Unicode String'
Numeric literals: 123, 45.67, -10
Date literals: '2023-10-27', '10:30:00'
Boolean literals: TRUE, FALSE (in some contexts)

Column Names

References to columns in tables or views. For example, ProductID, ProductName.

Variables

User-defined or system variables that hold a value. For example, @CustomerID, @@ROWCOUNT.

Function Calls

Calls to built-in or user-defined functions. For example, GETDATE(), SUM(SalesAmount), LEN('SQL').

Operator Usage

Operators combine values to produce a new value. Common operators include:

Arithmetic operators: +, -, *, /, %
Comparison operators: =, <, >, <>, !=, <=, >=
Logical operators: AND, OR, NOT
String concatenation: + (or CONCAT())

Expression Examples

Simple Scalar Expression:

SELECT 1 + 2 * 3;

This expression evaluates to 7.

Expression with Column and Function:

SELECT ProductName, LEN(ProductName) AS NameLength FROM Production.Product;

This query returns the product name and the length of the product name.

Expression in a WHERE Clause:

SELECT OrderID, OrderDate FROM Sales.SalesOrderHeader WHERE OrderDate > '2023-01-01' AND TotalDue > 1000;

This filters orders placed after a specific date and with a total due greater than a value.

Important: The order of operations is crucial in evaluating expressions. Parentheses () can be used to explicitly control the order of evaluation.

Table Expressions

Table expressions are fundamental to operations like joins, subqueries, and table-valued functions. They are essentially sources of rows and columns.

Subqueries

A subquery is a query nested inside another query. It can return a single value (scalar subquery), a single column (column subquery), or multiple rows and columns (table subquery).

Scalar Subquery Example:

SELECT TOP 1 ProductName FROM Production.Product WHERE ListPrice = (SELECT MAX(ListPrice) FROM Production.Product);

This query finds the product with the highest list price.

Table Subquery Example (in FROM clause):

SELECT P.ProductName, P.ListPrice FROM (SELECT ProductName, ListPrice FROM Production.Product WHERE Color = 'Red') AS P WHERE P.ListPrice > 50;

This demonstrates using a subquery as a derived table.

Table-Valued Functions (TVFs)

TVFs are stored procedures or functions that return a table. They can be used in the FROM clause similarly to tables or views.

Using a Table-Valued Function:

SELECT * FROM dbo.GetProductsByCategory('Bikes');

This assumes a TVF named GetProductsByCategory exists.

Understanding T-SQL expressions is key to writing powerful and flexible queries. This section covers the fundamental building blocks of expressions used throughout SQL Server.