SQL filter LEFT TABLE before left join

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.

Where you have from tablea, you could put a subquery like from (select x.* from tablea X where x.value=20) TA

Then refer to TA like you did tablea previously.

Likely the query optimizer would do this for you.

Oracle should have a way to show the query plan. Put "Explain plan" before the sql statement. Look at the plan both ways and see what it does.

In your first SQL statement, A.ID=20 is not being joined to anything technically. Joins are used to connect two separate tables together, with the ON statement joining columns by associating them as keys.

WHERE statements allow the filtering of data by reducing the number of rows returned only where that value can be found under that particular column.