Inner and Left Outer Join with Where Clause vs ON Clause

Filtering results with the [ON] clause and the [Where] clause using LEFT OUTER JOIN and INNER JOIN is a very powerful technique.

An INNER JOIN gives rows which match on the values in common columns of two or more tables using an operator like (=) equal.

A LEFT JOIN or LEFT OUTER JOIN gives all the rows from the left table with matched rows from both tables. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

Now, we will demonstrate how these work.

We will create two tables: FRUIT and FRUIT_COLOR.

IF OBJECT_ID('FRUIT') IS NOT NULL
DROP TABLE FRUIT;
GO

CREATE TABLE FRUIT (
name VARCHAR(25),
color INT
);
GO

IF OBJECT_ID('FRUIT_COLOR') IS NOT NULL
DROP TABLE FRUIT_COLOR;
GO

CREATE TABLE FRUIT_COLOR
(
id INT,
name VARCHAR(25)
);
GO

Regarding the structure of the created tables, the FRUIT_COLOR table has a unique column (ID) and color (name)s.

The FRUIT table consists of fruit (name)s and their respective (color).

I have not created any primary key and foreign key relationships to keep it simple, but you can easily understand the primary key FRUIT_COLOR(ID) is a foreign key to FRUIT(color).

Populate these tables with some data.

INSERT into FRUIT_COLOR VALUES (1,'orange');
INSERT into FRUIT_COLOR VALUES(2,'yellow');
INSERT into FRUIT_COLOR VALUES (3,'red');
INSERT into FRUIT_COLOR VALUES (4,'blue');
GO

INSERT into FRUIT VALUES ('banana',2);
INSERT into FRUIT VALUES ('mango',2);
INSERT into FRUIT VALUES ('orange',1);
INSERT into FRUIT VALUES ('apple',3);
INSERT into FRUIT VALUES ('grapes',null);
INSERT into FRUIT VALUES ('avocado',null);
GO

SELECT * from FRUIT;
SELECT * from FRUIT_COLOR;
GO

We will demonstrate different scenarios with a left outer join.

Query 1.1 – A Plain, Simple Left Outer Join

SELECT  *
FROM    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id;
GO

As we know the concept of a LEFT OUTER JOIN, we know it will show all the rows from the left table. In this case, FRUIT is the left table, and if any match is found with the right table called FRUIT_COLOR, it will show the matching rows. Otherwise, it will return NULL.

In the example above, grapes and avocados have their color as NULL, so the NULL comparison fails and returns NULL.

Query 1.2 – A Left Outer Join with the Where Clause

SELECT  *
FROM    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id
WHERE   F.name='apple';
GO

When we add a where clause with a left outer join, it behaves like an inner join, where the filter is applied after the ON clause, showing only those rows having fruit with the name “apple.”

Query 1.3 – A Left Outer Join with the ON Clause

SELECT  *
from    FRUIT F LEFT outer join FRUIT_COLOR FC
ON      F.color = FC.id AND F.name='apple';
GO

In the query above, most people get confused about how this query is evaluated. You can see the results below all the rows from the left table are included in the result set as a plain left outer join query that we saw in Query 1.1, but from the second table (i.e., fruit_color), only those rows are shown which match the condition f.name=’apple.’ Otherwise, it is shown as NULL.

This is useful when filtering data with a left outer join. The [ON] clause can be useful when we need to return all the rows from the left table and only those rows from the right table which match the condition of the On clause.

— Query 1.4 – An Inner Join with the Where Clause

SELECT  *  
FROM    FRUIT F INNER join FRUIT_COLOR FC
ON      F.color = FC.id
WHERE   F.name='orange';
GO

— Query 1.5 – An Inner Join with the ON Clause

SELECT   *  
FROM    FRUIT F INNER join FRUIT_COLOR FC
ON        F.color = FC.id AND F.name='orange';
GO

You can see both query 1.4 and query 1.5, using an inner join, return the same result because it does not matter where you are filtering the rows with an inner join. With either the on clause or the where clause, both cases will give you same results.