December 12, 2017 at 10:59 am
Hi All,
New to the forums, but I've been working with SQL for a little bit. I'm starting to explore very complex queries, and gaining a more detailed understanding of what is occuring during these joins. So, I've arrived upon a query and had a question about it's structure. The following is the basic format:
SELECT * FROM
((TableA LEFT OUTER JOIN TableB
ON ((TableA.ID1=TableB.ID1) AND (TableA.ID2=TableB.ID2)))
AND (TableA.ID3=TableB.ID3));
Is there any difference to doing the following, where the 3rd join is included inside of the first double join?
SELECT * FROM
((TableA LEFT OUTER JOIN TableB
ON ((TableA.ID1=TableB.ID1) AND (TableA.ID2=TableB.ID2) AND (TableA.ID3=TableB.ID3)));
Thanks in Advance,
SP
December 12, 2017 at 11:17 am
CoffeeHeaven - Tuesday, December 12, 2017 10:59 AMHi All,New to the forums, but I've been working with SQL for a little bit. I'm starting to explore very complex queries, and gaining a more detailed understanding of what is occuring during these joins. So, I've arrived upon a query and had a question about it's structure. The following is the basic format:
SELECT * FROM
((TableA LEFT OUTER JOIN TableB
ON ((TableA.ID1=TableB.ID1) AND (TableA.ID2=TableB.ID2)))
AND (TableA.ID3=TableB.ID3));Is there any difference to doing the following, where the 3rd join is included inside of the first double join?
SELECT * FROM
((TableA LEFT OUTER JOIN TableB
ON ((TableA.ID1=TableB.ID1) AND (TableA.ID2=TableB.ID2) AND (TableA.ID3=TableB.ID3)));Thanks in Advance,
SP
Functionally, there is no difference.
if this code that is generated by a tool? Such as an ORM, maybe Crystal reports?
If it's not, and this is the manner in which you are writing code, it may make sense to do some research.
The parenthesis are not needed, and definitely make the code hard to read.
Along the same lines, including the schema in the names is recommended, and providing a meaningful alias instead of the full table name is also recommended.
Something like this, although that's subject to a long debate/discussion!
SELECT *
FROM dbo.TableA TabA
LEFT OUTER JOIN dbo.TableB TabB ON TabA.ID1=TabB.ID1
AND TabA.ID2=TabB.ID2
AND TabA.ID3=TabB.ID3
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 12, 2017 at 11:21 am
It also looks like you are confusing a WHERE clause in SQL with an IF statement in a programming.
These are fundamentally different structures. Far too many programmers think that a WHERE clause is the same as an IF.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 12, 2017 at 11:46 am
Combining operands with the AND operator is commutative. That is, it does not matter which order the operands are combined.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 12, 2017 at 11:48 am
Also, you only have one join. The join condition has three criteria, but it is still only one join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 12, 2017 at 2:49 pm
Thanks, for the feedback. It was all very helpful. I'm trying to mark this thread as solved; but am unable to find how to do that.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply