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. An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them. The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN returns all rows from multiple tables where the join condition is met.

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.