Implement Inner Join When Query Contains Outer Join

In this blog, we will learn how to implement inner join, although we define the outer join in the query.

This is something interesting; i.e., how to enforce the compiler to use the inner join instead of the outer join. Let's take an example. We have two tables State_Tab and City_Tab.

 

State_Tab

 

 

City_Tab

 

 

 

Now, we write two queries, using outer join, and compare the execution plan of both the queries.

 

Query 1

 

 

 

Query 2

 

 

 

We can see that both the queries use the left outer join but the second query implements the inner join, instead of outer join, because for "On condition", we define the 1=1, which will be always true, so the query optimizer converts the left outer join to cross join due to the "Where condition". This cross join will convert into the inner join.