December 22, 2016 at 9:34 am
Hello,
I have a query:
SELECT "data" FROM t1... JOIN t2... JOIN t3... LEFT JOIN t4... JOIN t5... LEFT JOIN t6... JOIN t7(derived table)...
It is my understanding that when LEFT JOINing tables, all JOINs must be LEFT JOINS or at least the final JOIN must be a LEFT JOIN. If an INNER JOIN is processed after a LEFT JOIN, it "negates" all preceding LEFT JOINs. So in the above case the LEFT JOIN on table t4 will be "negated" by the JOIN on t5 and the LEFT JOIN on t6 will be "negated" by the JOIN on t7. Am I correct?
December 22, 2016 at 9:47 am
wenger.noah (12/22/2016)
Hello,I have a query:
SELECT "data" FROM t1... JOIN t2... JOIN t3... LEFT JOIN t4... JOIN t5... LEFT JOIN t6... JOIN t7(derived table)...
It is my understanding that when LEFT JOINing tables, all JOINs must be LEFT JOINS or at least the final JOIN must be a LEFT JOIN. If an INNER JOIN is processed after a LEFT JOIN, it "negates" all preceding LEFT JOINs. So in the above case the LEFT JOIN on table t4 will be "negated" by the JOIN on t5 and the LEFT JOIN on t6 will be "negated" by the JOIN on t7. Am I correct?
You are not correct. An inner join doesn't negate a previous join. Not even really sure what you mean by negate in this situation. It is possible that an inner join that logically make another left join function as an inner join but that depends on the predicates.
For example if you had something like this.
select MyColumn
from Table1 t1
left join Table2 t2 on t2.id = t1.id
inner join Table3 t3 on t3.id = t2.id
This would effectively make the join to Table2 an inner join because it would require there to be a joining value from Table2. Does this make sense a little bit more now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2016 at 10:05 am
I think I understand your response. Using your table names for reference, the logic of your query expresses that we will not see all rows from t1; we will only see rows from t1 that inner join t2 on t1.id = t2.id and inner join t3 on t2.id = t3.id...is this correct?
December 22, 2016 at 10:10 am
wenger.noah (12/22/2016)
I think I understand your response. Using your table names for reference, the logic of your query expresses that we will not see all rows from t1; we will only see rows from t1 that inner join t2 on t1.id = t2.id and inner join t3 on t2.id = t3.id...is this correct?
Yes that is correct.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 22, 2016 at 10:12 am
Not necessarily. The problem is when a field from an outer table is used in a JOIN condition (or WHERE clause) without taking into account that it might be NULL. (It converts the outer join to an inner join.) So, for example, the following two queries are equivalent, because no fields from an outer table are used in subsequent join conditions.
SELECT *
FROM TABLEA a
LEFT OUTER JOIN TABLEB b
ON a.BID = b.BID
INNER JOIN TABLEC c
ON a.CID = c.CID
SELECT *
FROM TABLEA a
INNER JOIN TABLEC c
ON a.CID = c.CID
LEFT OUTER JOIN TABLEB b
ON a.BID = b.BID
Furthermore, the logical ordering of the JOIN processing is determined by the order of the ON clauses (which you have not included), so the order of the joins may not be obvious.
SELECT *
FROM TABLEA a
LEFT OUTER JOIN TABLEB b
INNER JOIN TABLEC c
ON b.CID = c.CID -- The inner join is processed first
ON a.BID = b.BID -- The outer join is processed last
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply