09.05.2015

Left outer join – Differences between on and where clause for additional filter conditions

Technical Value

When we use an inner join and we have additional filter conditions then it does not matter where to put the filter conditions. We put the filter conditions either in the on clause or in the where clause. Let’s consider the following example where we create two tables in order to fill it with some values.

1. create table dbo.lefttable ( id integer , value integer ) create table dbo.righttable ( id integer , value integer )
2.
3. insert into dbo.lefttable (id, value) values (1, 10), (2, 20), (3,30) insert into dbo.righttable (id, value) values (2, 200), (3, 300)

The table Lefttable has the following rows: The table Righttable has the following rows: Now we join the tables over the column id and we have an additional filter condition where value is 20. When we use an inner join it does not matter where to put the filter condition. The first sql statement uses the filter condition in the where clause.

1. select * from dbo.lefttable t1 inner join dbo.righttable t2 on t1.id = t2.id where t1.value = 20

The second sql statement uses the filter condition in the on clause.

1. select * from dbo.lefttable t1 inner join dbo.righttable t2 on t1.id = t2.id and t1.value = 20

Both statements gives us the following result: But when we use an left outer join then it does matter where to put the additional filter condition. The first sql statement uses the filter condition in the where clause.

1.  select * from dbo.lefttable t1 left outer join dbo.righttable t2 on t1.id = t2.id where t1.value = 20

Here we have only one row, because the where clause is applied at the end eliminating all rows not having value 20. Now let’s consider the sql statment with the filter condition in the on clause.

1. select * from dbo.lefttable t1 left outer join dbo.righttable t2 on t1.id = t2.id and t1.value = 20

Here we have all three rows from the left table because a left outer join takes all rows from the left table and tries to match it with the right table. The columns for the right tables are null, when the value for column value does not match 20. It does not matter if we can find a matching row in the right table.

Teilen auf