MySQL：根据表记录存在性动态配置WHERE子句的查询需求

Hey there, let's work through this dynamic SQL logic you need. The core goal is to check if table_private has any records first, then use that result to adjust the WHERE clause for your images query. Here are two straightforward, easy-to-maintain approaches:

Approach 1: Use a CTE to Create a Clear "Flag" #

This method explicitly generates a flag value to represent whether table_private has records, making the logic super readable:

WITH private_check AS (
    SELECT 
        CASE 
            WHEN EXISTS(SELECT 1 FROM table_private) THEN 1 
            ELSE NULL 
        END AS private_flag
)
SELECT i.*
FROM images i
CROSS JOIN private_check pc
WHERE i.advert_id = 5795
AND (
    (pc.private_flag = 1 AND i.img_user_id = 1)
    OR (pc.private_flag IS NULL AND i.img_user_id IS NULL)
);

Breakdown: #

• The CTE private_check uses EXISTS (an efficient check that stops searching as soon as it finds one record) to set private_flag to 1 if table_private has any entries, otherwise NULL.
• We do a CROSS JOIN to bring this flag into our main query, then use it in the WHERE clause to pick the right img_user_id condition alongside the fixed advert_id = 5795.

Approach 2: Inline Existence Check (Concise One-Liner) #

If you prefer a more compact query without a CTE, you can embed the existence check directly in the WHERE clause:

SELECT *
FROM images
WHERE advert_id = 5795
AND (
    (EXISTS(SELECT 1 FROM table_private) AND img_user_id = 1)
    OR (NOT EXISTS(SELECT 1 FROM table_private) AND img_user_id IS NULL)
);

Breakdown: #

• This skips creating a separate flag and directly evaluates both scenarios in the WHERE clause:
  1. If table_private has records, match img_user_id = 1
  2. If table_private has no records, match img_user_id IS NULL
• The advert_id = 5795 condition applies to both cases, so we keep it outside the parentheses to avoid redundant code.

Both approaches will deliver exactly the dynamic behavior you're after. Choose the one that fits better with your codebase's readability standards!

内容的提问来源于stack exchange，提问作者Pavlo.G