SQL查询执行顺序与中间结果分析请求：指定嵌套查询解析

Let's walk through exactly how this query runs, step by step—this is a common pattern to find records in the left table that have no matching entries in the right table.

1. First Execution: The Inner Left Join Subquery #

The very first thing the database runs is the nested subquery inside the parentheses. Here's that code again:

select lg.a.aid ,lg.c.number from lg.a left join lg.c on lg.a.aid=lg.c.aid

This is a LEFT JOIN (also called a left outer join), which means:

• Every record from the left table lg.a is included in the result set, no matter what.
• For each record in lg.a, the database looks for matching records in lg.c where lg.a.aid = lg.c.aid.
  • If a match is found: The number column from lg.c is added to the row.
  • If no match is found: The number column is filled with NULL (since there's no corresponding value in lg.c).

So the intermediate result here (before we name it t1) is a table with two columns: aid (all values from lg.a) and number (either the matching value from lg.c or NULL if no match exists).

2. Next: Create the Temporary Table t1 #

The database takes the result from the inner subquery and assigns it the alias t1. This is just a temporary in-memory table that the outer query can reference—there's no permanent table created here, it's just for the duration of this query.

3. Final Execution: The Outer Query Filter & Selection #

Now the outer query runs against t1:

select 0 from t1 where t1.number is null

What this does:

• It filters the rows in t1 to only keep those where number is NULL (these are exactly the rows from lg.a that had no matching aid in lg.c).
• For every one of these filtered rows, it outputs the value 0 (instead of the original aid or other columns).

So the final result is a list of 0s, with one row for each record in lg.a that has no corresponding entry in lg.c.

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