如何将两个LEFT JOIN合并为单个LEFT JOIN，简化代码且不降低性能？

Hey there! Let's break down how to combine those two LEFT JOINs into one while keeping your query snappy and your code clean. First, I’ll reference the structure from your original example (since you’re joining the same secondary table twice with different filter conditions, that’s the key pattern here).

Original Query Structure #

Here’s what your initial query likely looks like (based on the scenario you shared):

SELECT 
  main.id,
  a.data AS a_data,
  b.data AS b_data
FROM main_table main
LEFT JOIN secondary_table a 
  ON main.id = a.main_id AND a.category = 'A'
LEFT JOIN secondary_table b 
  ON main.id = b.main_id AND b.category = 'B';

How to Merge into a Single LEFT JOIN #

We can use conditional aggregation to pull both sets of data in one join. This approach is just as performant as your original query (if not better, with proper indexing) and cuts down on redundant code.

Method 1: Conditional Aggregation Directly in the SELECT

This is the most concise option, perfect if you’re fetching single values per main_id + category pair:

SELECT 
  main.id,
  -- Grab data where category is 'A'
  MAX(CASE WHEN s.category = 'A' THEN s.data END) AS a_data,
  -- Grab data where category is 'B'
  MAX(CASE WHEN s.category = 'B' THEN s.data END) AS b_data
FROM main_table main
LEFT JOIN secondary_table s 
  ON main.id = s.main_id 
  AND s.category IN ('A', 'B') -- Filter early to reduce joined rows
GROUP BY main.id;

Method 2: Subquery Pivot for Complex Data

If you need to pull multiple columns per category, wrap the aggregation in a subquery first to pivot the data before joining:

SELECT 
  main.id,
  s.a_data,
  s.b_data,
  s.a_other_column,
  s.b_other_column
FROM main_table main
LEFT JOIN (
  SELECT 
    main_id,
    MAX(CASE WHEN category = 'A' THEN data END) AS a_data,
    MAX(CASE WHEN category = 'B' THEN data END) AS b_data,
    MAX(CASE WHEN category = 'A' THEN other_column END) AS a_other_column,
    MAX(CASE WHEN category = 'B' THEN other_column END) AS b_other_column
  FROM secondary_table
  WHERE category IN ('A', 'B') -- Filter early here too
  GROUP BY main_id
) s ON main.id = s.main_id;

Keeping Performance On Point #

To make sure your merged query doesn’t slow down, follow these quick tips:

• Filter early: Adding s.category IN ('A', 'B') (either in the JOIN condition or subquery WHERE clause) reduces the number of rows the database has to process—this matches the efficiency of your original per-JOIN filters.
• Index smartly: Create an index on secondary_table(main_id, category) (include any columns you’re selecting, like data or other_column, if your database supports covering indexes). This lets the database quickly locate matching rows without full table scans.
• Aggregate appropriately: Use MAX() or MIN() only if each main_id + category has one row. If there are multiple rows, use GROUP_CONCAT() (MySQL) or STRING_AGG() (PostgreSQL/SQL Server) to combine values, depending on your data needs.

Why This Works #

Instead of joining the same table twice (which can lead to redundant row processing), we join once and use conditional logic to extract the exact data we need. This simplifies your code while maintaining (or even improving) query speed, especially with proper indexing.

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