September 1, 2014 at 11:44 am
Hi all expert,
I am having some problem understanding joins (Specially left,right and outer apply)when it comes to more then 3 tables.
Like how are the result set formed , which result set is formed first and then second when is the where clause applied on which result set etc.
I search on the net, on most of the pages venn diagram is present. Venn diagram is good when 2 tables are present, for multiple set of records on different table i think venn diagrams are very difficult to understand.
In Data structure subject there were lot of grapical video available trying to make us understand LILO,FIFO. Are such video or graphic present for joins to understand.
September 2, 2014 at 4:45 am
Guys,
Any link or reference 🙂
Thanks.
September 2, 2014 at 8:43 am
JackTimber (9/1/2014)
Hi all expert,I am having some problem understanding joins (Specially left,right and outer apply)when it comes to more then 3 tables.
Like how are the result set formed , which result set is formed first and then second
The answer for this is that it depends. SQL Server will read the code left to right to compile it and it will create an execution plan that might not be in the same order as you wrote it. SQL is a declarative language so we tell the engine what to do and not how it should be done.
when is the where clause applied on which result set etc.
In the end you end with a single result set made out of all the tables used in your statement. WHERE clauses will apply to any column available and you must indicate which table they belong if ambiguity is present.
I'm not sure that I can explain the whole theory of multiple joins in a single post because it's a very wide subject. It's all about practice.
September 2, 2014 at 1:01 pm
I am having some problem understanding joins (Specially left,right and outer apply)when it comes to more then 3 tables.... which result set is formed first and then second...
When you join between two tables you are combining two sets to create one. Take this query for example:
SELECT <whatever>
FROM A
INNER JOIN B ON a.<something>=b.<something>
We'll nickname the results of this query to AB since it's a combination of A & B...
Now with a 3rd table:
SELECT <whatever>
FROM A
INNER JOIN B ON a.<something>=b.<something>
INNER JOIN C ON c.<something>=a.<something>
In this query this is what happens:
1. A is joined to B to create AB.
2. AB is joined to C to create ABC
Let's say you had 5 tables involved in this order A, B, C, D & E
1. A is joined to B to create AB.
2. AB is joined to C to create ABC
...
5. ABCD is joined to E to create ABCDE
Regarding APPLY:
Paul White did two great articles about the APPLY operator which I suggest you take a look at.
Itzek Ben Gan has two SQL Serve 2012 books out which do a great job of explaining joins. I would suggest looking at one of those as a reference. He dives into the query processing order which is a hugely important topic that is not discussed very often.
-- Itzik Ben-Gan 2001
September 2, 2014 at 1:13 pm
Alan.B (9/2/2014)
In this query this is what happens:1. A is joined to B to create AB.
2. AB is joined to C to create ABC
Let's say you had 5 tables involved in this order A, B, C, D & E
1. A is joined to B to create AB.
2. AB is joined to C to create ABC
...
5. ABCD is joined to E to create ABCDE
Except that the join order is not guaranteed.
It could happen the following:
1. B is joined to C to create BC.
2. D is joined to E to create DE.
3. BC is joined to DE to create BCDE.
4. A is joined to BCDE to create ABCDE.
Or any other combination that the engine will define as the best (technically not always the best but that's a larger subject and won't get into details).
I'm sure Itzek Ben Gan's books will explain better this situations.
September 4, 2014 at 4:10 am
http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
https://kansdb.blogspot.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply