SQL Server Tutorialprovides basic and advanced concepts of C# for beginners and professionals.

  1. Home
  2. SQL Server Tutorial

SQL Expressions

Back to: SQL Server Tutorial

In Microsoft SQL Server, expressions are combinations of constants, literals, functions, and operators that produce a value. They are used in various places, such as in queries, in the SELECT clause, WHERE clause, and ORDER BY clause, among others. Here’s a breakdown of some common types of expressions and how they’re used:

1. Arithmetic Expressions

These involve arithmetic operators to perform calculations.

  • Operators: +, -, *, /, % (modulus)
  • Example:
    SELECT Price * Quantity AS TotalAmount FROM Orders;

2. String Expressions

These involve string concatenation or manipulation.

  • Operators: + (concatenation), LEN(), SUBSTRING(), REPLACE()
  • Example:
    SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

3. Date and Time Expressions

These involve date and time functions and calculations.

  • Functions: GETDATE(), DATEADD(), DATEDIFF(), FORMAT()
  • Example
    SELECT DATEADD(day, 7, GETDATE()) AS NextWeek FROM Orders;

4. Logical Expressions

These evaluate conditions and return a Boolean value (TRUE or FALSE).

  • Operators: =, !=, >, <, >=, <=, AND, OR, NOT
  • Example:
    SELECT * FROM Products WHERE StockQuantity > 0 AND Price < 50;

5. Comparison Expressions

These compare values and return a Boolean result.

  • Operators: =, !=, >, <, >=, <=
  • Example
    SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

6. NULL Expressions

These deal with NULL values.

  • Functions: IS NULL, IS NOT NULL, COALESCE(), ISNULL()
  • Example
    SELECT FirstName, ISNULL(MiddleName, 'N/A') AS MiddleName FROM Employees;

7. CASE Expressions

These provide conditional logic within queries.

  • Syntax
    CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END
  • Example
    SELECT FirstName, CASE WHEN Salary > 50000 THEN 'High' WHEN Salary BETWEEN 30000 AND 50000 THEN 'Medium' ELSE 'Low' END AS SalaryCategory FROM Employees;

8. Subqueries and Derived Tables

Subqueries are queries within queries.

  • Example
    SELECT EmployeeID, FirstName FROM Employees WHERE EmployeeID IN (SELECT ManagerID FROM Managers);

9. Common Table Expressions (CTEs)

CTEs are used to define temporary result sets that can be referred to within a SELECT, INSERT, UPDATE, or DELETE statement.

  • Syntax
    WITH CTE_Name AS ( SELECT column1, column2 FROM table WHERE condition ) SELECT * FROM CTE_Name;
  • Example
    WITH SalesCTE AS ( SELECT SalesPersonID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY SalesPersonID ) SELECT SalesPersonID, TotalSales FROM SalesCTE WHERE TotalSales > 10000;

10. Window Functions

These are used for calculations across a set of table rows that are somehow related to the current row.

  • Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG()
  • Example
    SELECT EmployeeID, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum FROM Employees;

These expressions and functions can be combined and used in various ways to perform complex queries and data manipulations in SQL Server.

Scroll to Top