Actual cases of JOIN, subquery, and window functions of MySQL advanced query

Preface

In daily development, single-table queries often cannot meet business needs. Through advanced query technology, MySQL can flexibly obtain data from multiple tables and perform complex data screening and analysis. This article will focus on three advanced query methods:JOIN (Connection Query)、SubqueryandWindow Functionsand provide practical cases to help you better understand and apply these technologies.

1. JOIN (Connection Query)

JOIN allows us to combine two or more tables through associated columns in a SQL statement, thereby obtaining multi-table data in a single query. Common JOIN types in MySQL include:

1.1 Inner JOIN

principle: Returns records in two tables that meet the join conditions.
Example：
```
SELECT o.order_id, o.order_date, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
```
The above query returns all orders and their corresponding customer names, and will only return the result if there is a matching relationship between the order and the customer.

1.2 Left connection (LEFT JOIN)

principle: Returns all records in the left table, and NULL will be displayed even if there is no match in the right table.
Example：
```
SELECT c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
```
This query lists all customers and even if some customers do not have orders, the relevant order field will be displayed as NULL.

1.3 Right connection (RIGHT JOIN)

principle: Similar to the left join, but returns all records in the right table, and the part of the left table does not match will display NULL.
Example：
```
SELECT o.order_id, c.customer_name
FROM orders AS o
RIGHT JOIN customers AS c ON o.customer_id = c.customer_id;
```
This connection method is rarely used in actual development, and most scenarios can achieve the same effect by adjusting the order of LEFT JOINs.

1.4 Self JOIN

principle: Related queries between different records in the same table, usually used to find data with hierarchy or relationships.

Example：

SELECT e1.employee_name AS Manager, e2.employee_name AS Subordinate
FROM employees AS e1
INNER JOIN employees AS e2 ON e1.employee_id = e2.manager_id;

This query shows the relationship between the administrator and his subordinates.

2. Subquery

Subquery is a query statement nested inside other SQL statements and is usually used to take the result of a query as a condition or data source. According to the usage location, subqueries can be divided into the following types:

2.1 Scalar subquery

Features: Returns a single value, which can be used directly in the WHERE or SELECT clause.

Example：

SELECT order_id, order_date
FROM orders
WHERE customer_id = (SELECT customer_id FROM customers WHERE customer_name = 'Zhang San');

This query extracts the customer ID with the customer name "Zhang San" and is used to filter records in the order table.

2.2 List subquery

Features: Returns a column of values that can be used in IN or NOT IN conditions.

Example：

SELECT order_id, order_date
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Beijing');

This query filters out orders from all customers from Beijing.

2.3 Table subquery

Features: Returns a result set, usually used to act as a temporary table in a FROM clause.

Example：

SELECT t.customer_id, t.total_orders
FROM (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY customer_id
) AS t
WHERE t.total_orders > 5;

The sub-query here first counts the order quantity of each customer, and then filters out customers with orders greater than 5.

2.4 Related subqueries

Features: Subqueries depend on data from outer queries, and each row of records will perform a subquery once.

Example：

SELECT e.employee_id, e.employee_name,
       (SELECT COUNT(*) FROM orders o WHERE o.salesperson_id = e.employee_id) AS order_count
FROM employees AS e;

This query counts the number of orders that each salesperson is responsible for.

3. Window Function

MySQL supports window functions (Window Functions) since version 8.0, which makes it possible to directly group statistics, ranking and other operations on query results without using subqueries.

3.1 Common window functions

ROW_NUMBER(): Returns a unique sequence number for each row in the result set.
```
SELECT order_id, order_date,
       ROW_NUMBER() OVER (ORDER BY order_date) AS row_num
FROM orders;
```
The query assigns a line number to each order by order date.

RANK() and DENSE_RANK(): Used for ranking, but handled slightly differently when the same value exists. RANK skips rankings, while DENSE_RANK does not.

SELECT customer_id, total_spent,
       RANK() OVER (ORDER BY total_spent DESC) AS rank
FROM (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) AS spending;

Aggregation functions such as SUM(), AVG(), MAX(), MIN(), etc.: It can be used as a window function to calculate the accumulated value or average value in each group, etc.
```
SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;
```
The above query shows the cumulative sum of order amounts and can be used to generate reports or trend charts.

3.2 Use scenarios

Ranking and sorting: Sort and rank sales, scores or other indicators.
Accumulated sum: Generate dynamic cumulative values, such as daily accumulation of sales.
Partition statistics: Without using GROUP BY, perform partition statistics on the data and retain detailed data rows.

4. Actual case: comprehensive application

Suppose you need to generate a sales report that contains the total order amount of each salesperson and its ranking in its respective area, which can be implemented in combination with subqueries and window functions:

WITH SalesData AS (
  SELECT salesperson_id, region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY salesperson_id, region
)
SELECT salesperson_id, region, total_sales,
       RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) AS sales_rank
FROM SalesData;

Here, **CTE (common table expression)** first counts the total order amount of each salesperson in each area, and then uses the window function to partition by area and rank the total sales to help managers quickly identify the sales champion in each area.

5. Summary

JOINMake multi-table association query simple and efficient, and can meet various business needs through different types of connections.
SubqueryProvides flexible data filtering and filtering methods, suitable for processing a single data item or an entire result set.
Window FunctionsIt was introduced after MySQL 8.0, providing a more intuitive and efficient solution for data statistics, rankings and cumulative calculations.

By deeply mastering these three advanced query technologies, you can greatly improve the complexity and flexibility of MySQL queries, thereby better supporting complex business scenarios and data analysis needs. Welcome to constantly try and optimize in practice to make full use of MySQL's powerful data processing capabilities!

This is the article about JOIN, subquery, and window functions of MySQL advanced query. For more related contents of JOIN, subquery, and window functions of MySQL advanced query, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!