MySQL WITH: Common Table Expression (CTE)

Common Table Expression (CTE) is an important feature of MySQL that is used to generate a temporary result set. It can be used with any SQL statement like SELECT, INSERT, UPDATE, etc. The complicated queries can be simplified by using CTE. The result set of any query is stored as an object for the derived table at the time of query execution. But CTE can be self-referencing which means the same query can be referenced multiple times using CTE. For this reason, CTE performance is better than the derived table. WITH clause is used to define a CTE and more than one CTE can be defined in a single statement using this clause. How a CTE can be applied in the query to make it more readable and increase the performance of the query are explained in this article.

Benefits of using CTE:

• It makes the query more readable.
• It improves query performance.
• It can be used as an alternative to the VIEW.
• It is possible to create a chaining of CTE to simplify the query.
• Recursive queries can be implemented easily by using CTE.

Syntax:

WITH CTE-Name (column1,column2,… columnn) AS (
Query
)
SELECT * FROM CTE-Name;

Here, you can define any SQL statement as Query, SELECT, UPDATE, DELETE, INSERT or CREATE statement. If you define column list in WITH clause, then the number of columns in the query must be the same with the number of columns defined in WITH clause.

Prerequisite:

CTE feature is not supported by any MySQL version less than 8.0. So, you have to install MySQL 8.0 before practicing the example of this article. You can check the currently installed version of MySQL by running the following command.

$ mysql -V

The output shows that MySQL version 8.0.19 is installed in the system.

If the correct version is installed then create a database named mydb and create two tables named users and users_profile with some data to know the uses of CTE in MySQL. Run the following SQL statements to do the tasks. These statements will create two related tables named users and users_profile. Next, some data will be inserted in both tables by INSERT statements.

CREATE DATABASE mydb;
USE mydb;

CREATE TABLE users (
username VARCHAR(50) PRIMARY KEY,
password VARCHAR(50) NOT NULL,
status VARCHAR(10) NOT NULL);

CREATE TABLE users_profile (
username VARCHAR(50) PRIMARY KEY,
name VARCHAR(50) NOT NULL,
address VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
FOREIGN KEY (username) REFERENCES users(username) ON DELETE CASCADE);

INSERT INTO users values
('admin' , '7856', 'Active'),
('staff' , '90802', 'Active'),
('manager' , '35462', 'Inactive');

INSERT INTO users_profile values
('admin', 'Administrator' , 'Dhanmondi', 'admin@test.com' ) ,
('staff', 'Jakir Nayek' , 'Mirpur', 'zakir@test.com' ),
('manager', 'Mehr Afroz' , 'Eskaton', 'mehr@test.com' );

Use of simple CTE:

Here a very simple CTE named cte_users_profile is created where no field list is defined with CTE name in WITH clause and it will retrieve all data from the users_profile table. Next, the SELECT statement is used to read all records from cte_users_profile CTE.

WITH cte_users_profile AS (
SELECT * FROM users_profile
)
SELECT * FROM cte_users_profile;

The following output will appear after running the statement.

Use of simple CTE with column list:

You can create CTE more specifically by defining the field list with CTE name in WITH clause. In this case, the field names defined with the CTE name will be the same as the field names defined in the SELECT query inside the WITH clause. Here, name and email fields are used in both places.

WITH cte_users_profile(name, email) AS (
SELECT name, email
FROM users_profile
)
SELECT * FROM cte_users_profile;

The following output will appear after running the above statement.

Use of simple CTE with WHERE clause:

The SELECT statement with WHERE clause can be defined in CTE statement like another SELECT query. The SELECT query with retrieve records from users and users_profile tables where the values of usersname field are equal for both tables and the value of username is not ‘staff’.

WITH cte_users AS (
SELECT users.username, users_profile.name, users_profile.address, users_profile.email
FROM users, users_profile
WHERE users.username = users_profile.username and users_profile.username <> 'staff'
)
SELECT name as Name , address as Address
FROM cte_users;

The following output will appear after running the statement.

Use of simple CTE with GROUP BY clause:

Any aggregate function can be used in the query that is used in CTE. The following CTE statement shows the use of SELECT query with COUNT() function. The first SELECT statement is used to display all records of users table and the last SELECT statement is used to display the output of CTE that will count the total number of users from users table who are active.

SELECT * FROM users;
WITH cte_users AS (
SELECT COUNT(*) as total
FROM users
WHERE status='Active' GROUP BY status
)
SELECT total as `Total Active Users`
FROM cte_users;

The following output will appear after running the statement.

Use of simple CTE with UNION operator:

The following CTE statement shows the use of the UNION operator in the CTE statement. The output will display the values of username from users table where the status value is ‘Inactive’ and the other values of username from users_profile table.

WITH cte_users AS (
SELECT users.username
FROM users
WHERE status = 'Inactive'
UNION
SELECT users_profile.username
FROM users_profile
)
SELECT * FROM cte_users;

The following output will appear after running the statement.

Use of simple CTE with LEFT JOIN:

The following CTE statement shows the use of LEFT JOIN in CTE. The output will display the values of name and email fields from users_profile table by applying LEFT JOIN based on username field between users and users_profile tables and WHERE condition, that will filter those records from users table where the value of status is ‘Inactive’.

WITH cte_users AS (
SELECT name, email
FROM users_profile
LEFT JOIN users
ON users.username= users_profile.username WHERE users.status = 'Inactive'
)
SELECT * FROM cte_users;

The following output will appear after running the statement.

Conclusion:

If you want to increase the query performance and get the query output faster then the CTE is the better option than other MySQL options. This article will help MySQL users to learn the use of CTE for the SELECT query very easily.

from Linux Hint https://ift.tt/2RXMIWy