SQL filter LEFT TABLE before left join

Question

I have read a number of posts from SO and I understand the differences between filtering in the where clause and on clause. But most of those examples are filtering on the RIGHT table (when using left join). If I have a query such as below:

select * from tableA A left join tableB B on A.ID = B.ID and A.ID = 20

The return values are not what I expected. I would have thought it first filters the left table and fetches only rows with ID = 20 and then do a left join with tableB.

Of course, this should be technically the same as doing:

select * from tableA A left join table B on A.ID = B.ID where A.ID = 20

But I thought the performance would be better if you could filter the table before doing a join. Can someone enlighten me on how this SQL is processed and help me understand this thoroughly.

Answer 1

A left join follows a simple rule. It keeps all the rows in the first table. The values of columns depend on the on clause. If there is no match, then the corresponding table's columns are NULL -- whether the first or second table.

So, for this query:

select *
from tableA A left join
     tableB B
     on A.ID = B.ID and A.ID = 20;

All the rows in A are in the result set, regardless of whether or not there is a match. When the id is not 20, then the rows and columns are still taken from A. However, the condition is false so the columns in B are NULL. This is a simple rule. It does not depend on whether the conditions are on the first table or the second table.

For this query:

select *
from tableA A left join
     tableB B
     on A.ID = B.ID 
where A.ID = 20;

The from clause keeps all the rows in A. But then the where clause has its effect. And it filters the rows so on only id 20s are in the result set.

When using a left join:

• Filter conditions on the first table go in the where clause.
• Filter conditions on subsequent tables go in the on clause.