[wpseo_breadcrumb]

MySQL Common table expressions (CTE)

Update on:
Feb 18, 2023

SQL is a language that has been used for decades to manage and manipulate data in relational databases. It provides various tools and techniques to fetch, filter, and aggregate data from tables. One such tool is the Common Table Expression (CTE). In this article, we will explore what CTEs are, why they are useful, and how to use them in MySQL.

What are Common Table Expressions (CTE)?

A Common Table Expression (CTE) is a named temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. In simpler terms, it’s a way to store intermediate results temporarily during a query execution, making it easier to write and understand complex queries.

Why use Common Table Expressions (CTE)?

CTEs are a great tool to simplify complex queries, making them more readable and maintainable. They can also help you avoid creating and managing temporary tables, which can add overhead to your database and slow down performance. Additionally, they can help you avoid duplicating code in your queries and make it easier to refactor and modify your SQL statements.

How to use Common Table Expressions (CTE) in MySQL?

In MySQL, you can define a CTE by using the WITH clause followed by a SELECT statement. The syntax for a CTE is as follows:

WITH cte_name (column1, column2, ...) AS (
  SELECT column1, column2, ...
  FROM table
  WHERE conditions
)
SELECT *
FROM cte_name

Let’s take a look at an example to understand how to use CTEs in practice.

Example 1: CTE with a SELECT Statement

Suppose we have a table named “employees” that contains information about employees in a company. The table has the following structure and data:

+----+--------+--------+
| id | name   | salary |
+----+--------+--------+
| 1  | John   | 50000  |
| 2  | Alice  | 60000  |
| 3  | Bob    | 55000  |
| 4  | Eve    | 65000  |
+----+--------+--------+

Let’s say we want to retrieve all employees who earn a salary greater than $60,000 and display the results in descending order of salary. Here’s how we can do that using a CTE:

WITH high_salary_employees (id, name, salary) AS (
  SELECT id, name, salary
  FROM employees
  WHERE salary > 60000
)
SELECT *
FROM high_salary_employees
ORDER BY salary DESC;

The result of this query would be:

+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
| 4  | Eve   | 65000  |
+----+-------+--------+

In this example, we defined a CTE named “high_salary_employees” that retrieves all employees with a salary greater than $60,000. We then used the CTE in the SELECT statement to retrieve all the data from the CTE and order the results by salary in descending order.

Example 2: CTE with a JOIN Statement

Suppose we have another table named “departments” that contains information about the departments in the company. The table has the following structure and data:

+----+----------+
| id | name     |
+----+----------+
| 1  | Sales    |
| 2  | Marketing|
| 3  | IT       |
+----+----------+

Let’s say we want to retrieve the names of all employees and the name of their department. Here’s how we can do that using a CTE:

WITH employee_dept (id, name, department_id) AS (
  SELECT e.id, e.name, d.id
  FROM employees e
  JOIN departments d ON e.department_id = d.id
)
SELECT e.name AS employee_name, d.name AS department_name
FROM employee_dept e
JOIN departments d ON e.department_id = d.id;

The result of this query would be:

+------------+---------------+
| employee_name | department_name |
+------------+---------------+
| John       | Sales         |
| Alice      | Marketing     |
| Bob        | IT            |
| Eve        | Sales         |
+------------+---------------+

In this example, we defined a CTE named “employee_dept” that joins the “employees” and “departments” tables to retrieve the name of the employee and the id of their department. We then used the CTE in a SELECT statement to retrieve the name of the employee and the name of their department.

Conclusion

Common Table Expressions (CTEs) are a useful tool in SQL that allow you to define and reference a named temporary result set within a SELECT, INSERT, UPDATE, or DELETE statement. They provide a way to simplify complex queries, make them more readable, and avoid duplicating code. By using CTEs, you can improve the maintainability and performance of your database queries.

Related Posts

Window functions in MySQL

Window functions are a new feature that was introduced in MySQL 8.0 and they have the ability to perform advanced analytics within the database itself. With these functions, users can perform operations like running totals, moving averages, and ranking without having...

MySQL Generated columns

MySQL Generated Columns are the next big thing in database management. With the introduction of generated columns, you can create virtual columns that calculate values based on an expression and store the result. The generated columns make it easier to perform complex...

Getting information about MySQL databases and tables

MySQL is a widely used database management system for managing relational databases. It is an open-source database management system, which means it is free to use and has a large community of users that contribute to its development. In this article, we will explore...

MySQL Events

Introduction MySQL is a popular open-source relational database management system that is used by many organizations to store and manage their data. One of the most powerful features of MySQL is the ability to schedule tasks to be executed automatically, known as...

Follow Us

Our Communities

More on MySQL

The Ultimate Managed Hosting Platform
Load WordPress Sites in as fast as 37ms!

0 Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

13 + fifteen =