Implement Inner Join When Query Contains Outer Join

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.

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.