How to perform multi-table joint query and conditional filtering in the database?

Database multi-table joint query and conditional filtering skills

In database queries, it is often necessary to extract data from multiple tables and filter by specific conditions. This article will explore how to achieve this goal efficiently and illustrate it in combination with actual cases.

Application scenarios

Suppose we need:

1. Step 1: Based on the user table and user profile table, query user data that meets specific conditions (paging index, paging size, province, city, gender, age).
2. Step 2: Remove the blacklisted user from the result of the first step.
3. Optional step 3: Further exclude users from other tables (such as blocked tables).

Solution

There are two main strategies:

Strategy 1: Single SQL statement implements joint query of multiple tables

Use a single SQL statement to join multiple tables through JOIN operation and add all filter conditions in the WHERE clause. This method has few query times and is efficient. The example SQL statement is as follows:

 SELECT u.*, ud.*
FROM User Table u
JOIN User Profile Table ud ON u.user_id = ud.user_id
LEFT JOIN Blacklist table b ON u.user_id = b.user_id
LEFT JOIN Mask table s ON u.user_id = s.user_id
WHERE b.user_id IS NULL -- Exclude blacklist users AND s.user_id IS NULL -- Exclude masked table users AND ud.province = 'Special province'
  AND ud.city = 'Special City'
  AND ud.gender = 'Specific Gender'
  AND ud.age BETWEEN Specific age range LIMIT paging index, paging size;

This statement uses LEFT JOIN to connect blacklist tables and mask tables and filters out users in these tables through the IS NULL condition in the WHERE clause. All operations are done in one SQL statement, with the best efficiency.

Strategy 2: Step-by-step query and filtering

First execute the query to obtain the preliminary results, and then perform subsequent filtering. This method is easy to manage and debug, but it has many queries that may affect performance. The steps are as follows:

1. Step 1: Obtain preliminary user data

 SELECT u.*, ud.*
FROM User Table u
JOIN User Profile Table ud ON u.user_id = ud.user_id
WHERE ud.province = 'Special Province'
  AND ud.city = 'Special City'
  AND ud.gender = 'Specific Gender'
  AND ud.age BETWEEN Specific age range LIMIT paging index, paging size;

2. Step 2: Filter blacklist users

 SELECT t.*
FROM (first step result) t
LEFT JOIN Blacklist table b ON t.user_id = b.user_id
WHERE b.user_id IS NULL;

3. Step 3: Filter the block table user (if required)

 SELECT t.*
FROM (Second step 2) t
LEFT JOIN mask table s ON t.user_id = s.user_id
WHERE s.user_id IS NULL;

This method facilitates step-by-step processing and verification of data, but multiple queries can affect performance.

Summarize

Which strategy to choose depends on actual demand and data volume. In the case of large data volume, it is recommended to use a single SQL statement, which is more efficient. The data volume is small or for easy debugging, you can choose to query step by step.

The above is the detailed content of How to perform multi-table joint query and conditional filtering in the database?. For more information, please follow other related articles on the PHP Chinese website!