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.

How to Write INNER JOIN Which is Actually CROSS JOIN? - Interview Question of the Week #250 crossjoin-800x212

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)

, ,
Previous Post
How to Use GOTO command in SQL Server? – Interview Question of the Week #249
Next Post
How to Determine Read Intensive and Write Intensive Tables in SQL Server? – Interview Question of the Week #251

Related Posts

3 Comments. Leave new

  • What is the difference between INNER JOIN and JOIN?

    They are functionally identical. I would say just writing JOIN is somewhat lazy coding pattern, but I admit that it might also be a style preference. I would opt to be explicit as much as possible and typing 5 more keystrokes is worth it IMO.

    Reply
  • Hi Pinal,

    Has this question out of curiosity. In both the cases, doesn’t the Execution Plan show you the warning on Join operator indicating that there is no join predicate. Do you think that might be a first hint as a best practice to recheck the query written or are there any scenarios where warning doesn’t appear.

    Regards,

    Sravan Pappu

    Reply
  • I agree both are same and writing just JOIN is a lazy coding style.

    Reply

Leave a Reply

Menu