SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
Order of the generated ON clause

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
Order of the generated ON clause
Author Message
seth.rothman



Joined: 29 Jan 2008
Posts: 54
Country: United States

Post Order of the generated ON clause Reply with quote
I've noticed that the order of the aliased tables in the ON is not what I expect. For example, consider the following SQL:

SELECT *
FROM SalesOrder so
INNER JOIN SalesOrderDetail sod
ON sod.SalesOrderID = so.SalesOrderID

Notice that sod is on the left side of the = in the ON clause. In this example, the order doesn't matter since it's an INNER JOIN, but if I wrote it as a LEFT OUTER JOIN, the resulting SQL is incorrect.
Fri Mar 28, 2008 6:53 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Hi there,

Order of columns in the ON line is not important. What is important is in which order tables are joined. You always start with the driving table and then add to it some additional elements, in other words you do it in left-to-right order. SQL Assistant generates syntax in the left-to-right order, which is a natural order for SQL language, just as you would read the below example in plain English, "I want to get results from table #primary and also available results from table #secondary where exist records in table #secondary that have values in column c1 the same as in my primary table #secondary in column c1". That's exactly what is written below in SQL terms in the second SELECT statement.

Try for yourself, as you can see the results are identical.


Code:
CREATE TABLE #primary (c1 int, c2 int)
INSERT INTO #primary VALUES (1, 2)
INSERT INTO #primary VALUES (2, 3)

CREATE TABLE #secondary (c1 int, c2 int)
INSERT INTO #secondary VALUES (2, 2)

SELECT * FROM #primary t1 LEFT OUTER JOIN #secondary t2 ON t1.c1 = t2.c1

SELECT * FROM #primary t1 LEFT OUTER JOIN #secondary t2 ON t2.c1 = t1.c1

DROP TABLE #primary
DROP TABLE #secondary

Fri Mar 28, 2008 7:49 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.