Ordering of condition clause in SQL JOIN - Stack Overflow

admin管理员组

文章数量:1406000

Consider the following:

select (columns)
from table1
   join table2 on table2.x = table1.y

compared to

select (columns)
from table1
   join table2 on table1.y = table2.x

The first one seems to be more relevant in terms of documenting the condition, but does this change in the ordering of the conditional actually have any impact on the efficiency?

And a normal join is not directional, but a LEFT JOIN is. Does that change the answer at all?

Consider the following:

select (columns)
from table1
   join table2 on table2.x = table1.y

compared to

select (columns)
from table1
   join table2 on table1.y = table2.x

The first one seems to be more relevant in terms of documenting the condition, but does this change in the ordering of the conditional actually have any impact on the efficiency?

And a normal join is not directional, but a LEFT JOIN is. Does that change the answer at all?

• sql

Share Follow edited Mar 6 at 19:58 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Mar 6 at 19:55 Richard HartmanRichard Hartman 5711 silver badge55 bronze badges 6

• 1 What is even the difference between the 2 queries? They're exactly the same. – Eric Commented Mar 6 at 20:19
• Please tag your RDBMS – Dale K Commented Mar 6 at 20:31
• 1 You could inspect the execution plan and find the answer – Dale K Commented Mar 6 at 20:32
• 3 I think maybe you are unclear about how SQL works. SQL is a declarative language, meaning you describe to the database engine the results you want. But you aren't telling it how to return those results. It works that out based on a whole bunch of information (indexes, statistics etc) which is displayed in the execution plan. – Dale K Commented Mar 6 at 20:36
• I am not used to thinking in declarative terms. TBH I was pretty sure that those expressions were essentially identical, but not knowing how SQL really works it was a question that crossed my mind. Thanks for the info. – Richard Hartman Commented Mar 7 at 1:59

 |  Show 1 more comment

2 Answers 2

Sorted by: Reset to default 1

In the general case

The order you mention left or right operands with, will in the general case not be relevant.

SQL does not impose anything in terms of performance or implementation,
however all major implementations (and probably all minor too, apart from some student's project to reimplement SQL starting from ground up) will take for granted that, = being commutative, they are free to choose the most efficiently way to compare: left to right or right to left.

This flexibility allows them to concentrate on more complex aspects of optimization, letting them swap the order of tables if deemed smart (if table1 is small and table2 indexed, it may for example choose to fully read table1 then lookup the index for the corresponding table2 entries, not only if you swap the equality table2.x = table1.y to table1.y = table2.x, but even if you swap the join to table2 join table1);
aAs long as the result stays functionally equal, of course (which is not the case for a left join; but even then, the = part has no impact on the evaluation order).

The rich yet concise documentation of PostgreSQL, while intended for writers of operator overloads (see my next paragraph), also gives an interesting glimpse on all the smartness put by the RDBMS writers in implementing "basic" operators in an highly optimizable way.

On overloaded operators

Then some RDBMS allow you to override operators, even for basic scalar types.
Although your question probably targeted the general case, and operator overloading is not that widely used, it merits to be cited.

In that case of course, you cannot take anything for granted.

First, from a functional point of view, you'll have to ensure that it is implemented commutatively.

Second, from the efficiency point of view, you'll have to verify how it integrates with the (still involved) RDBMS-provided optimization layer:

• it can have omitted to hint the operator as commutative with another
  (which would have helped the optimizer swap the operation with another more optimized for the use case)
• or about its stability
  (if multiple calls with the same operands are done, can the optimizer avoid recalling the operator?)
• it can lie on its estimated cost
  and make the optimizer privilege it as a "quick" operation while in fact it calls a web service on each operation…

An EXPLAIN on queries involving an overloaded operator will only tell you what the optimizer thinks, and in case of irrealistic output, you will have to understand by yourself what made it choose this optimization path given what it has been told.

There is absolutly no impact of ordering in a way or another any member of a SQL predicate for performances reason or anything else...

You must remenber that SQL is not an execution language but a query language. A query language show a pattern of what the user want to have as a result, but never the way to compute the result.

Never the SQL code is directly executed. SQL code is translated into a mathematical formulae (algebraic tree) then this mathematical tree is modified by equivalence (A=B <=> B=A... A + 1 = 2 <=> A = 1...) for performance reasons, which leads to give an execution plan of the query, compound of different steps which are execution modules in order to deliver the result. The order of its modules and the nature of the internal algorithms of each of the modules can vary depending on certain circumstances (volume of tables and indexes, distribution of data, availability of resources...)

For an outer join, only the position of the tables around the join operator gives the direction of processing of the operator. Thus a LEFT OUTER JOIN can be transformed into a RIGHT OUTER JOIN if the position of the tables is reversed. The position of the members of the join ON predicate is of no importance... As a reminder, the "equality" comparison operator is commutative, but not the inequality comparison operators...

本文标签： Ordering of condition clause in SQL JOINStack Overflow