如何优化用于关联两张lookup表取值的多子查询？

Hey there! Your current query gets the job done, but all those repeated subqueries make it messy to read, hard to maintain, and likely inefficient—since it’s hitting T1 and T2 multiple times for each row. Let’s clean this up with a more streamlined approach using joins and conditional aggregation (or pivot, if your database supports it).

The Core Issue With Your Current Query #

Right now, you’re running 12 separate subqueries (6 to fetch T2’s PK, 6 to get T1’s TYPE_NAME) just to populate 6 columns. That’s redundant and can slow down execution, especially with larger datasets. Instead, we can join T1 and T2 once, then reshape the results into your desired column format.

Method 1: Join + Conditional Aggregation (Works for Most Databases) #

This approach uses a single join between T1 and T2, then uses CASE WHEN to pick out the values you need and pivot them into columns. It’s universally compatible (MySQL, PostgreSQL, SQL Server, etc.) and way easier to tweak later.

SELECT
    MAX(CASE WHEN T2.ID = 'A' AND T1.ID = 'AA' THEN T1.TYPE_NAME END) AS A,
    MAX(CASE WHEN T2.ID = 'B' AND T1.ID = 'BB' THEN T1.TYPE_NAME END) AS B,
    MAX(CASE WHEN T2.ID = 'C' AND T1.ID = 'CC' THEN T1.TYPE_NAME END) AS C,
    MAX(CASE WHEN T2.ID = 'D' AND T1.ID = 'DD' THEN T1.TYPE_NAME END) AS D,
    MAX(CASE WHEN T2.ID = 'E' AND T1.ID = 'EE' THEN T1.TYPE_NAME END) AS E,
    MAX(CASE WHEN T2.ID = 'F' AND T1.ID = 'FF' THEN T1.TYPE_NAME END) AS F
FROM T1
JOIN T2 ON T1.FK_T2 = T2.PK;

Why this is better:

• Fewer table hits: We only join T1 and T2 once, instead of running 12 separate subqueries
• Easier maintenance: Adding a new column just means adding one more CASE WHEN line, not copying an entire subquery block
• More readable: All lookup logic is centralized, so you can see exactly which T2/T1 pairs map to each column at a glance

Method 2: PIVOT (For Databases That Support It) #

If you’re using SQL Server, Oracle, or PostgreSQL (with the crosstab extension), you can use built-in pivot functionality to make this even more concise. Here’s a SQL Server example tailored to your use case:

SELECT A, B, C, D, E, F
FROM (
    SELECT 
        T2.ID AS pivot_column,
        T1.TYPE_NAME
    FROM T1
    JOIN T2 ON T1.FK_T2 = T2.PK
    WHERE 
        (T2.ID = 'A' AND T1.ID = 'AA') OR
        (T2.ID = 'B' AND T1.ID = 'BB') OR
        (T2.ID = 'C' AND T1.ID = 'CC') OR
        (T2.ID = 'D' AND T1.ID = 'DD') OR
        (T2.ID = 'E' AND T1.ID = 'EE') OR
        (T2.ID = 'F' AND T1.ID = 'FF')
) AS source_data
PIVOT (
    MAX(TYPE_NAME)
    FOR pivot_column IN ([A], [B], [C], [D], [E], [F])
) AS pivot_table;

This is cleaner if your database supports it, but the conditional aggregation method is more flexible across different SQL dialects.

Quick Note About MYTABLE #

In your original query, you’re selecting from MYTABLE but not using any of its columns. If MYTABLE is supposed to be part of the logic (e.g., you need these lookup values per row in MYTABLE), adjust the query to include a join or cross join with grouping. For example:

SELECT
    MT.*,
    MAX(CASE WHEN T2.ID = 'A' AND T1.ID = 'AA' THEN T1.TYPE_NAME END) AS A,
    MAX(CASE WHEN T2.ID = 'B' AND T1.ID = 'BB' THEN T1.TYPE_NAME END) AS B,
    -- ... add remaining CASE statements here
FROM MYTABLE MT
CROSS JOIN T1
JOIN T2 ON T1.FK_T2 = T2.PK
GROUP BY MT.id, MT.all_other_columns; -- Group by all columns from MYTABLE

If MYTABLE was just a placeholder (since you don’t use its data), you can omit it entirely like in the first examples.

内容的提问来源于stack exchange，提问作者D.J.