Common Table Expressions (CTE) in MySQL

Common Table Expressions (CTE) is a feature in MySQL that allows you to define a temporary named result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for creating a CTE is as follows:

WITH cte_name (column1, column2, ...) AS (
   SELECT ...
   FROM ...
   WHERE ...
)
SELECT ...
FROM ...
JOIN cte_name
ON ...

The CTE keyword is followed by a name (cte_name) that must be unique within a single query. The columns to be included in the result set are defined within the parentheses. The SELECT statement within the CTE defines the data that is included in the result set.

Example

Let's take a look at an example of how to use a CTE in MySQL. Suppose we have a table called "employees" with columns "id", "name", "department", and "salary". We want to get the average salary for each department along with the employee's name and salary. Here's how we can use a CTE to accomplish this:

WITH dept_avg_sal AS (
   SELECT department, AVG(salary) as avg_salary
   FROM employees
   GROUP BY department
)
SELECT e.name, e.salary, das.avg_salary
FROM employees e
JOIN dept_avg_sal das
ON e.department = das.department;

In this example, we've created a CTE called "dept_avg_sal" that calculates the average salary by department. We then join the result set of this CTE with the "employees" table to get the employee's name and salary along with the department's average salary.

Output

When we run the example code above, we will get a table similar to this:

+-------+--------+------------+
| name  | salary | avg_salary |
+-------+--------+------------+
| John  | 50000  | 55000      |
| Sally | 60000  | 55000      |
| Bob   | 70000  | 70000      |
+-------+--------+------------+

This output shows the name and salary of each employee along with the department's average salary.

Explanation

In the example above, we've used a CTE to calculate the average salary by department. We then joined this result set with the "employees" table to get the employee's name and salary along with the department's average salary. This allows us to easily compare each employee's salary to the average for their department.

Use

CTEs can be used to simplify complex queries and make them more readable. They allow you to define subqueries that can be referenced multiple times within a single query. This can make it easier to understand and modify a query over time.

Important Points

• CTEs can be used within SELECT, INSERT, UPDATE, and DELETE statements.
• The result set of a CTE can be referenced within a single query.
• CTEs can be joined with other tables or result sets in the same query.

Summary

In this tutorial, we discussed how to use Common Table Expressions (CTE) in MySQL. We covered the syntax, example, output, explanation, use, and important points of CTEs. With this knowledge, you can now use CTEs to simplify complex queries and make them more readable in your MySQL database.