MS SQL SERVER 1 - Common Table Expression(CTE) with simple easy example

 Hello All,

I am Chakrapani Upadhyaya a full stack engineer, In this article we will learn about CTE in SQL Server.

What is CTE?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can help simplify complex queries and improve code readability.

Here's a simple example of how to use a CTE in MS SQL Server:

Suppose you have a table called "employees" with the following columns:

1. employee_id (int)
2. first_name (varchar)
3. last_name (varchar)
4. department_id (int)
5. salary (float)

You want to retrieve the average salary of all employees in each department. You can achieve this using a CTE as follows:

WITH avg_salary_per_dept AS (

  SELECT department_id, AVG(salary) AS avg_salary

  FROM employees

  GROUP BY department_id

)

SELECT department_id, avg_salary

FROM avg_salary_per_dept;

In this example, the CTE named "avg_salary_per_dept" calculates the average salary for each department by using the AVG function and the GROUP BY clause. Then, the SELECT statement outside the CTE references the temporary result set and retrieves the department_id and avg_salary columns.

Note that the CTE is defined using the WITH keyword and enclosed in parentheses. The SELECT statement outside the CTE references the CTE by its name. Also, note that the CTE is not a permanent object in the database and is only valid for the duration of the query.