如何在SQL中避免使用UNION ALL？多条件查询性能优化咨询

Absolutely! You can ditch the repeated UNION ALL subqueries here by consolidating your filtering logic and adjusting your CASE statement to handle all conditions in a single table scan. This way, the database only reads DB1 once instead of multiple times, which should cut down on execution time significantly.

Here's how you can rewrite your query:

SELECT 
    (price*quantity) as total, 
    date, 
    CASE
        -- Prioritize the more specific condition first
        WHEN name = 'extra' AND export = 'yes' AND import = 'yes' THEN 
            CASE when type='Yes' then 'BRAZIL. YES' else 'BRAZIL. NO' end
        -- Then handle the broader condition
        WHEN name = 'manuf' AND export = 'yes' THEN 
            CASE when type='Yes' then 'USA. YES' else 'USA. NO' end
    END as name 
FROM DB1
WHERE 
    (name = 'manuf' AND export = 'yes')
    OR 
    (name = 'extra' AND export = 'yes' AND import = 'yes')

Key Notes for This Approach: #

• Single Table Scan: Unlike your original query which scans DB1 twice (once per subquery), this version only scans the table once—this is the biggest win for performance.
• CASE Order Matters: We placed the name='extra' condition first because it has an additional import='yes' check, making it more specific. This ensures rows that match both condition sets (if any) get categorized correctly, just like your original query.
• Index Compatibility: If you have indexes on columns like name, export, or import, this combined WHERE clause can still leverage them. A composite index on (name, export, import) would be especially effective here, letting the database quickly locate matching rows without a full table scan.

Just run a quick comparison between the original and rewritten query to confirm the output matches—this rewrite should produce identical results but with better efficiency.

内容的提问来源于stack exchange，提问作者John Castello