How to Write INNER JOIN Which is Actually CROSS JOIN? – Interview Question of the Week #250

Question: How to Write INNER JOIN Which is Actually CROSS JOIN?

Answer: Experienced this question another day while working with a client on Comprehensive Database Performance Health Check. I noticed that they are writing all the Inner Join incorrectly. The matter of fact all the INNER JOIN which they wrote were not really an INNER JOIN but rather CROSS JOIN. Let us understand how we can write an INNER JOIN which is actually a Cartesian product.

CROSS JOIN

In the Set Theory, a Cartesian product is a mathematical operation that returns a set or product set or simply product from multiple sets.

There are two different ways to write this one:

Method 1: Inner Join With True Predicate

SELECT *
FROM TABLE1 AS t1 INNER JOIN TABLE2 AS t2 ON 1=1

This method will always product cross join as there is actually no reference of the columns in the WHERE condition which makes it always true and produces the cartesian product of the two tables.

Method 2: Usage of Non-ANSI JOIN Without WHERE Clause

SELECT t1.*
FROM TABLE1 AS t1, TABLE2 AS t2

This method will always product cross join as there is actually missing WHERE condition which makes it always true and produces the cartesian product of the two tables.

Here are a few related blog posts which you may find useful:

SQL SERVER – Introduction to JOINs – Basic of JOINs

This is a comprehensive blog post where we discuss all the different kinds of join with the help of Venn Diagrams.

What is the Difference Between An INNER JOIN and WHERE Clause

Well, the answer can be quite long but I will try to answer in a few words and a very simple one. If you are talking about resultset – there will be not much different. If you are talking about performance – SQL Server engine is much smarter than it will automatically re-write your quest in most of the cases so you will have no difference in performance. With that said, I still prefer that you use INNER JOIN when a query involves more than one table as that is the ANSI valid syntax.

SQL SERVER – Why Should You Not to Use Old Style JOIN?
There was a pretty good discussion about this subject earlier and lots of people participated with their opinion. Though the answer was very simple but the conversation was indeed delightful and was indeed very informative. In this blog post, I have another following up question to all of you. What is the difference between INNER JOIN and JOIN?

Reference: Pinal Dave (https://blog.sqlauthority.com)