Home > Documentation > SQL Server > SQL Reference > Expressions

SQL Expressions

An expression is a combination of one or more values, operators, and SQL functions that evaluate to a single value. Expressions are used throughout SQL Server to specify criteria in WHERE clauses, define values for INSERT and UPDATE statements, and in SELECT lists to derive new values from existing data. Understanding expressions is fundamental to writing effective SQL queries.

Types of Expressions

SQL expressions can take many forms, including:

Literals

Directly specified values such as strings, numbers, or dates.

'Hello, World!'
12345
'2023-10-27'

Column References

References to the values in a specific column of a table.

ProductName
OrderDate

Scalar Functions

Functions that return a single value based on input parameters. These include built-in functions for string manipulation, date/time operations, mathematical calculations, and more.

GETDATE()
UPPER(ProductName)
SUM(Quantity)

Arithmetic Operators

Used for mathematical calculations.

+ (Addition)
- (Subtraction)
* (Multiplication)
/ (Division)
% (Modulo)

Quantity * UnitPrice
SalesAmount - Discount

Comparison Operators

Used to compare two expressions and return a Boolean value (TRUE or FALSE). These are commonly used in WHERE clauses.

= (Equal to)
<> or != (Not equal to)
> (Greater than)
< (Less than)
>= (Greater than or equal to)
<= (Less than or equal to)

Price > 100.00
Status <> 'Shipped'

Logical Operators

Used to combine multiple Boolean expressions.

AND
OR
NOT

(Price > 100.00) AND (Category = 'Electronics')
(Status = 'Pending') OR (Status = 'Processing')

String Concatenation

Combining string values.

FirstName + ' ' + LastName

Expression Contexts

Expressions can be found in various parts of SQL statements:

`SELECT` List

To specify columns to retrieve or derive calculated values.

SELECT ProductCategory, 1.1 AS TaxRate, UnitPrice * 1.1 AS PriceWithTax FROM Products;

`WHERE` Clause

To filter rows based on conditions.

SELECT * FROM Orders WHERE OrderDate >= '2023-01-01' AND TotalAmount > 500.00;

`HAVING` Clause

To filter groups based on aggregate expressions.

SELECT CustomerID, SUM(OrderAmount) AS TotalSpent FROM Orders GROUP BY CustomerID HAVING SUM(OrderAmount) > 1000;

`ORDER BY` Clause

To specify the sorting order of the result set.

SELECT ProductName, UnitPrice FROM Products ORDER BY UnitPrice DESC;

`VALUES` Clause

In INSERT statements or in table value constructors.

INSERT INTO Customers (FirstName, LastName, RegistrationDate) VALUES ('Jane', 'Doe', GETDATE());

Best Practices

Use clear and descriptive aliases for expressions in the SELECT list.
Break down complex expressions into smaller, manageable parts, potentially using Common Table Expressions (CTEs) for readability.
Be mindful of data type conversions and potential errors when combining different data types in expressions.
Test expressions thoroughly to ensure they produce the expected results.

For more detailed information on specific functions and operators, please refer to the relevant sections in the SQL Server documentation.