T-SQL Syntax and Expressions

This document provides a comprehensive overview of Transact-SQL (T-SQL) syntax, focusing on the construction and use of expressions within T-SQL statements. Expressions are fundamental building blocks that can return a single value.

Understanding Expressions

An expression is a combination of one or more values, operators, and functions that evaluate to a single value. Expressions can appear in various parts of a T-SQL statement, including the SELECT list, WHERE clause, ORDER BY clause, and HAVING clause.

Types of Expressions

T-SQL supports several types of expressions:

1. Literals

Literals are constant values that are explicitly written in a statement. Examples include:

String literals: 'Hello World', '2023-10-27'
Numeric literals: 123, 45.67, -100
Date and time literals: '2023-10-27 10:30:00', GETDATE() (function returning a literal value)
Boolean literals: TRUE, FALSE (often represented as 1 and 0 in SQL Server)

2. Column References

References to columns in a table or view. These expressions return the value of the specified column for each row processed.

SELECT EmployeeID, FirstName, LastName
FROM Employees;

3. Scalar Functions

Functions that return a single value for each row processed. T-SQL has a rich set of built-in scalar functions for various purposes:

String Functions: LEN(), SUBSTRING(), UPPER(), LOWER(), REPLACE()
Numeric Functions: ABS(), ROUND(), CEILING(), FLOOR()
Date and Time Functions: GETDATE(), DATEPART(), DATEDIFF(), FORMAT()
System Functions: USER_NAME(), DB_NAME()

Example using a scalar function:

SELECT ProductName, UPPER(ProductName) AS UppercaseName
FROM Products;

4. Compound Expressions

Expressions formed by combining other expressions using operators. This includes arithmetic operations, string concatenation, and logical comparisons.

Operators in Expressions

Operators are symbols that perform operations on one or more operands (values or expressions). Common operators include:

Arithmetic Operators: +, -, *, /, % (modulo)
String Concatenation Operator: + (for strings in SQL Server)
Comparison Operators: =, <>, !=, <, >, <=, >=
Logical Operators: AND, OR, NOT
Bitwise Operators: &, |, ^, ~

Example of a compound expression with operators:

SELECT OrderID, Quantity * UnitPrice AS TotalPrice
FROM OrderDetails
WHERE Quantity * UnitPrice > 100;

Using Expressions in Different Clauses

`SELECT` List

Expressions in the SELECT list define the columns or calculated values returned by the query.

SELECT
    FirstName + ' ' + LastName AS FullName,
    Salary * 1.10 AS ProjectedSalary
FROM Employees;

`WHERE` Clause

Expressions in the WHERE clause are used to filter rows based on a condition.

SELECT
    ProductName,
    ListPrice
FROM Products
WHERE ListPrice > 50 AND CategoryID = 3;

`ORDER BY` Clause

Expressions can be used to sort the result set.

SELECT
    EmployeeID,
    HireDate
FROM Employees
ORDER BY DATEDIFF(year, HireDate, GETDATE()) DESC;

`HAVING` Clause

Expressions in the HAVING clause filter groups based on aggregate results.

SELECT
    CustomerID,
    COUNT(OrderID) AS NumberOfOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 5;

Note on Operator Precedence

T-SQL follows specific rules for operator precedence to determine the order in which operations are performed in an expression. Parentheses () can be used to explicitly control the order of evaluation.

Tip: Use Aliases for Clarity

When using complex expressions or functions in the SELECT list, it's good practice to assign them meaningful aliases using the AS keyword. This makes the output more readable.

Important: Data Type Precedence

When an expression involves operands of different data types, T-SQL follows data type precedence rules to determine the data type of the result. Understanding these rules is crucial to avoid unexpected results or errors.

Advanced Expression Concepts

Subqueries

A subquery (or inner query) is a query embedded within another T-SQL statement. Subqueries can return a single value (scalar subquery), a single column of multiple rows (column subquery), or multiple columns of multiple rows (table subquery).

SELECT
    EmployeeID,
    FirstName
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

CASE Expressions

The CASE expression allows you to use if-then-else logic in T-SQL statements. It evaluates a list of conditions and returns one of multiple possible result expressions.

SELECT
    ProductName,
    ListPrice,
    CASE
        WHEN ListPrice > 100 THEN 'High'
        WHEN ListPrice BETWEEN 50 AND 100 THEN 'Medium'
        ELSE 'Low'
    END AS PriceCategory
FROM Products;

Common Table Expressions (CTEs)

CTEs are temporary, named result sets that you can reference within a single SQL statement. They can simplify complex queries and improve readability, especially when dealing with recursive queries or multiple levels of subqueries.

WITH SalesByYear AS (
    SELECT
        YEAR(OrderDate) AS SalesYear,
        SUM(TotalAmount) AS TotalSales
    FROM Orders
    GROUP BY YEAR(OrderDate)
)
SELECT SalesYear, TotalSales
FROM SalesByYear
ORDER BY SalesYear;

Mastering T-SQL expressions is key to writing efficient and powerful SQL queries. By combining literals, column references, functions, and operators, you can construct sophisticated logic to retrieve and manipulate data effectively.