CODEWITHSUNDEEP
sqlsql-serversql-server-2008inner-join
user1261620
user1261620

Reputation: 377

Can the order of Inner Joins Change the results o a query

I have the following scenario on a SQL Server 2008 R2:

The following queries returns :

select * from TableA where ID = '123'; -- 1 rows
select * from TableB where ID = '123'; -- 5 rows
select * from TableC where ID = '123'; -- 0 rows

When joining these tables the following way, it returns 1 row 

SELECT A.ID 
  FROM TableA A 
 INNER JOIN ( SELECT DISTINCT ID
              FROM TableB  ) AS D
  ON D.ID = A.ID
 INNER JOIN TableC C 
  ON  A.ID = C.ID 
ORDER BY A.ID

But, when switching the inner joins order it does not returns any row 

SELECT A.ID 
  FROM TableA A 
 INNER JOIN TableC C 
  ON  A.ID = C.ID 
 INNER JOIN ( SELECT DISTINCT ID
              FROM TableB  ) AS D
  ON D.ID = A.ID
ORDER BY A.ID

Can this be possible?

Print Screen:enter image description here

Upvotes: 0

Views: 1216

Answers (2)

Eli
Eli

Reputation: 2608

As Gordon mentioned, for inner joins the order of joins doesn't matter, whereas it does matter when there's at least one outer join involved; however, in your case, none of this is pertinent as you are inner joining 3 tables, one of which will return zero rows - hence all combinations will result in zero rows.

You cannot reproduce the erratic behavior with the queries as they are shown in this question since they will always return zero records. You can try it again on your end to see what you come up with, and if you do find a difference, please share it with us then.

For the future, whenever you have something like this, creating some dummy data either in the form of insert statements or in rextester or the like, you make it that much easier for someone to help you.

Best of luck.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

For inner joins, the order of the join operations does not affect the query (it can affect the ordering of the rows and columns, but the same data is returned).

In this case, the result set is a subset of the Cartesian product of all the tables. The ordering doesn't matter.

The order can and does matter for outer joins.

In your case, one of the tables is empty. So, the Cartesian product is empty and the result set is empty. It is that simple.

Upvotes: 2

Related Questions