Detailed explanation of MySQL multi-table join query advanced technology and advanced function examples

MySQL multi-table join query advanced techniques and advanced functions

The following is a detailed introduction to the advanced techniques and advanced functions of multi-table join query in MySQL:

1. Advanced skills for multi-table connection query

1. Reduce the number of connections

Skill: Preprocess part of the data through subqueries or temporary tables, reducing the complexity and number of multi-table connections, thereby improving query efficiency.

Example：

-- First filter out the required order data through subqueries，Connect with the client table again
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN (SELECT * FROM orders WHERE order_date &gt;= '2024-01-01') o
ON c.customer_id = o.customer_id;

2. Select the appropriate connection type

Skill: Choose the appropriate connection type (such as INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.) according to actual needs to avoid unnecessary full table scanning.

Example：

-- If only customer information exists in the order form is required，use INNER JOIN
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
INNER JOIN orders o
ON c.customer_id = o.customer_id;
-- If all customer information is required，Even if they don't have an order，use LEFT JOIN
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

3. Optimize connections using indexes

Skill: Ensure that the connection fields (such as primary keys, foreign keys) have appropriate indexes to speed up connection operations.

Example：

-- for customer_id Field creation index
CREATE INDEX idx_customer_id ON orders(customer_id);
-- Accelerate connections using indexes during query
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id;

4. Use EXISTS and NOT EXISTS

Skill: In some cases, using EXISTS or NOT EXISTS instead of IN or NOT IN can improve query performance, especially when subqueries return large amounts of data.

Example：

-- use EXISTS Alternative IN
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
-- use NOT EXISTS Alternative NOT IN
SELECT c.customer_name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

5. Optimize subqueries with JOIN

Skill: Rewriting complex subqueries to JOINs can usually improve query performance and readability.

Example：

-- use JOIN Alternative subquery
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date &gt;= '2024-01-01';
-- Atomic query version
SELECT c.customer_name, (SELECT o.order_id FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date &gt;= '2024-01-01')
FROM customers c;

2. Advanced functions

1. String function

CHAR_LENGTH(): Returns the number of characters in the string.

SELECT CHAR_LENGTH(name) AS name_length FROM employees;

CONCAT(): Concatenate multiple strings into one string.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

SUBSTRING(): Extract substrings from strings.

SELECT SUBSTRING(name, 1, 5) AS name_part FROM employees;

REPLACE(): Replace the specified substring in the string.

SELECT REPLACE(name, 'John', 'Jane') AS updated_name FROM employees;

TRIM(): Remove spaces or specified characters at both ends of the string.

SELECT TRIM(name) AS trimmed_name FROM employees;

2. Aggregation function

COUNT(): Calculate the number of rows or the number of non-NULL values that meet the criteria.

SELECT COUNT(*) AS employee_count FROM employees;

SUM(): Calculate the sum of the numeric columns.

SELECT SUM(salary) AS total_salary FROM employees;

AVG(): Calculate the average value of the numeric column.

SELECT AVG(salary) AS average_salary FROM employees;

MAX()andMIN(): Returns the maximum and minimum values of the numeric column respectively.

SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees;

GROUP_CONCAT(): Concatenate all values in the group into a string.

SELECT GROUP_CONCAT(name) AS all_names FROM employees;

3. Conditional Function

IF(): Return different values according to the conditions.

SELECT name, IF(salary > 50000, 'High', 'Low') AS salary_level FROM employees;

CASE WHEN(): Return different values according to multiple conditions.

SELECT name,
       CASE
           WHEN salary < 30000 THEN 'Low'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
           ELSE 'High'
       END AS salary_level
FROM employees;

COALESCE(): Returns the first non-NULL value in its parameter.

SELECT name, COALESCE(department, 'Unknown') AS department_name FROM employees;

NULLIF(): Returns NULL when two parameters are equal, otherwise returns the first parameter.

SELECT name, NULLIF(salary, 0) AS adjusted_salary FROM employees;

4. Window Function

ROW_NUMBER(): Assign a unique sequence number to each row in the result set.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num FROM employees;

RANK()andDENSE_RANK(): Assign rankings to each row in the result set respectively. The former may have ranking gaps, while the latter does not.

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank FROM employees;

NTILE(): Divide the result set into a specified number of groups and assign a group number to each row.

SELECT name, salary, NTILE(4) OVER (ORDER BY salary DESC) AS quartile FROM employees;

LAG()andLEAD(): Returns the value of an offset before or after the current row, respectively.

SELECT name, salary, LAG(salary, 1) OVER (ORDER BY salary) AS prev_salary FROM employees;
SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary FROM employees;

By mastering these advanced techniques and functions, the performance and flexibility of MySQL queries can be significantly improved and the complex business needs can be met.

This is the article about the detailed explanation of the advanced skills and examples of MySQL multi-table connection query. For more related contents of MySQL multi-table connection query, please search for my previous articles or continue browsing the related articles below. I hope you will support me in the future!