0

For the simplified query:

Select t1.c1, t1.c2, t2.d1
FROM table1 t1
LEFT JOIN table2 t2
ON
(t1.c1 = t2.d2)

It seems from math by the symmetric property that this would be exactly the same as if one reversed the ON to:

Select t1.c1, t1.c2, t2.d1
FROM table1 t1
LEFT JOIN table2 t2
ON
(t2.d2 = t1.c1)

Is this always to true in TSQL or is there some exception where one could get more rows if the query was subtly changed as described above?

I've learned that very subtle join changes (in queries much more complicated than this simple example) can affect row counts greatly.

Also, in addition to rows returned (which I think should be EXACTLY THE SAME for both queries always) I would suppose that one ordering of the "ON" clause could make the query have better performance speed. Could someone verify that?

JosephDoggie
  • 1,514
  • 4
  • 27
  • 57
  • Intriguingly, one of the auto-suggestions from StackOverflow just before I posted this was: https://stackoverflow.com/questions/14415881/how-can-i-pair-socks-from-a-pile-efficiently -- that certainly looks like a worthy quest, but it doesn't really have much to do with my question above! LOL – JosephDoggie Jul 20 '21 at 21:15
  • See also DBA question: https://dba.stackexchange.com/questions/16477/is-it-correct-order-of-where-clause-doesnt-matter-when-it-is-used-with-join – JosephDoggie Jul 20 '21 at 21:17
  • 1
    The join order of tables what matters with the result and performance, If you use correct join order of the tables it will not affect on the performance, And on clause in the first and second query will run as same. If you check the actual query plan and Statistics you will observe the same for both queries. But if you have a where clause or changed the joining order, your result will be different so as Performance. set statistics io on use to set the statistics on and you will be able to get an idea of the quantitative measurements of stats – Gudwlk Jul 21 '21 at 00:34
  • 1
    if you have indexes on Joining columns your performance will be more better than you join on ordinary columns. – Gudwlk Jul 21 '21 at 00:56
  • 1
    There is no order to rows in a table. A result set [sic] has ("partial") order only per an outermost order by. Also this question (as can be expected) is a faq. So are basics of query optimization/implementation. Please research before considering asking. [ask] [Help] PS It is not helpful to "think" things that can't be pointed to in the documentation. – philipxy Jul 21 '21 at 01:57
  • 1
    Have a look at the _actual execution plan_ to see the difference. (I wouldn't expect any.) The results will be identical, though there is no expected order to the rows without an explicit `order by` clause. The _query optimizer_ will use the indexes (if any) and their statistics to determine the (likely) best way to proceed with execution of the statement. – HABO Jul 21 '21 at 03:34

1 Answers1

1

The ON condition in any sort of JOIN can be commutative, as you have observed. You can do ON a = b or ON b = a and have them mean precisely the same thing. They're nothing but Boolean expressions.

O. Jones
  • 103,626
  • 17
  • 118
  • 172