Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/mysql-common-table-expressions/ below:

MySQL Common Table Expressions - GeeksforGeeks

One of the most powerful features added in MySQL version 8.0 is common table expressions, which allow for the construction of temporary result sets within a single SQL query. In our daily life queries, we often use common table expressions and it makes our work easier. In this article, we will understand the Common Table Expression with examples and also we will learn how to use the statements.

A common table expression is a temporary result set that's named and which you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Temporary result sets are usually referred to in the execution of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTEs are absolutely necessary to reduce complex queries down to standard readable and reusable pieces of code. Moreover, CTEs might be recursive—thereby permitting extremely complicated hierarchical or recursive queries.

The basic syntax of a CTE in MySQL is as follows:

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

where,

• WITH Keyword: This keyword introduces the CTE
• cte_name: This is the name of the CTE
• (column1, column2, ...): These are the column names that are passed.
• SELECT: Used to select the columns

Here are some examples of MySQL Common Table Expressions:

Suppose that you have the table employees and you need to compute the total and average salary by department; then, among those, find the departments above an average salary of $60,000.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('John Doe', 'Sales', 55000.00),
('Jane Smith', 'Sales', 60000.00),
('Jim Brown', 'Sales', 65000.00),
('Jake White', 'Engineering', 75000.00),
('Jill Green', 'Engineering', 80000.00),
('Jenny Black', 'Engineering', 85000.00),
('James Gray', 'Marketing', 50000.00),
('Janet Blue', 'Marketing', 52000.00),
('Joan Pink', 'Marketing', 54000.00);

WITH department_salaries AS (
    SELECT department, 
           SUM(salary) AS total_salary, 
           AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
)
SELECT department, total_salary, average_salary
FROM department_salaries
WHERE average_salary > 60000;

department

department

average_salary

HR

140000

70000

IT

180000

90000

Explanation: The above example uses a Common Table Expression to determine the total and average salary by department from some Employees table. Here, the following CTE is named department_salaries. It aggregates the total and average salaries for each department. The main query then selects departments where the average salary exceeds $60,000. By using the CTE, the query becomes more readable and maintainable by breaking down this complex aggregation and filtering into clear, logical steps.

Suppose these table categories represent a hierarchical category structure with a self-referencing foreign key parent_id.

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

INSERT INTO categories (name, parent_id) VALUES
('Electronics', NULL),
('Computers', 1),
('Laptops', 2),
('Desktops', 2),
('Smartphones', 1),
('Accessories', 1),
('Chargers', 6),
('Cables', 6);

WITH RECURSIVE category_hierarchy AS (
    SELECT id, name, parent_id, 1 AS level
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ch.level + 1
    FROM categories c
    JOIN category_hierarchy ch ON c.parent_id = ch.id
)
SELECT id, name, parent_id, level
FROM category_hierarchy;

id

name

parent_id

level

1

Electronics

NULL

1

6

Clothing

NULL

1

2

Computers

1

2

5

Smartphones

1

2

7

Men

6

2

8

Women

6

2

3

Laptops

2

3

4

Desktops

2

3

9

Accessories

8

3

Explanation: This example shows the listing of all categories and their subcategories in a "categories table" where there is a self-referencing column for parent_id. Here, it uses a recursive Common Table Expression named category_hierarchy that starts off with the selection of top-level categories where parent_id is NULL, then recursively joins to include subcategories, incrementing a level column to show depth in hierarchy.

Let there be a table sales and you need to calculate the total sales for each salesperson, then filter those that achieved sales more than a given cutoff value.

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    salesperson_id INT,
    sales_amount DECIMAL(10, 2)
);

INSERT INTO sales (salesperson_id, sales_amount) VALUES
(1, 3000.00),
(1, 2500.00),
(1, 1500.00),
(2, 4000.00),
(2, 2000.00),
(3, 1000.00),
(3, 2000.00),
(4, 7000.00),
(5, 3000.00),
(5, 2500.00);

WITH total_sales AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
)
SELECT salesperson_id, total_sales
FROM total_sales
WHERE total_sales > 5000;

salesperson_id

total_sales

101

7000

102

5500

104

6000

Explanation: The example shows the use of Common Table Expressions in computing aggregate data and then reusing this aggregate inside a single query. Here, it creates a CTE called total_sales that computes the total amount of sales for each salesperson by grouping data from the sales table. It then selects from this total_sales CTE those salespersons with total sales greater than $5000.

Here are some benefits of the CTE:

• Readability: CTEs make difficult queries easier to understand, as they are broken down into smaller parts. It also provides clear readability of your SQL queries by naming the intermediate result sets, which any person not familiar with the specific query logic can use.
  
• Maintainability: You can move parts of a query into a CTE, and then changes to one part of the query will not affect the whole query; that is, modularity. This makes it easier for debugging and maintenance since every CTE can be understood and tested independently.
  
• Reusability: Once a CTE is defined, you can refer to it from other places within the same query. All this does is eliminate duplication, hence making your SQL code more compact. You do not need to repeat similar subquery logic in several places—a fact that not only reduces typing and increases readability but also lowers the possible error count.
  
• Recursive Queries: CTEs also support recursion and hence are perfect for any hierarchical data structure, from organizational charts to family trees, or bill-of-materials structures. Recursive CTEs can realize inquiries that would be very difficult or simply impossible to perform in regular SQL.
  
• Temporary Aggregation: One of the main use cases of CTEs is to provide temporary aggregations or calculations one might want to reference later in the main query. This improves the performance and clarity of a query by separating out the aggregation logic from the main query logic.

MySQL Common Table Expressions are used often by us in SQL queries. Whether it is creating a table or resolving any query you will need them. By the above article, you can understand Common Table Expressions easily with the help of the examples.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4