January 22, 2009 at 1:35 am
I have a couple of questions in the below query which joins 4 tables:
select t1.*
from table1 t1
inner join table2 t2
on t1.id = t2.id
inner join table3 t3
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
My Questions:
1. Can table3 be JOINed without specifying ON what fields they have to join?
2. Should only table1 be joined with table2, table3 and table4 i.e., the first table should be joined with the rest of the tables in the FROM clause? or can table2 be joined with table4, table3 be joined with table2 and so on?
3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?
select ....
from ....
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
is this same as
select ....
from ....
inner join table4 t4
on t3.id = t4.id
and t2.name = t3.name
4. Can INNER JOINS/OUTER JOINS be used in WHERE clause?
January 22, 2009 at 6:30 am
gyessql (1/22/2009)
I have a couple of questions in the below query which joins 4 tables:
select t1.*
from table1 t1
inner join table2 t2
on t1.id = t2.id
inner join table3 t3
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
My Questions:
1. Can table3 be JOINed without specifying ON what fields they have to join?
Well, yes, but that's what's referred to as a cartesian product, meaning every row is associated with every other row. It's generally considered to be a bad thing.
2. Should only table1 be joined with table2, table3 and table4 i.e., the first table should be joined with the rest of the tables in the FROM clause? or can table2 be joined with table4, table3 be joined with table2 and so on?
If I understand the question, yes, you can join one table to more than one other table like this:
SELECT...
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
JOIN Table42 t42
ON t1.Id = t42.Id
3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?
select ....
from ....
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
is this same as
select ....
from ....
inner join table4 t4
on t3.id = t4.id
and t2.name = t3.name
No. That's not an AND. That's just two seperate ON clauses. While you can format the code that way, most people, for ease of readability, format the code with each JOIN associated directly to the ON clause. However, you can use AND clauses within the JOIN clause, no issue:
SELECT...
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
AND t2.Date > '1/1/2009'
JOIN Table3 t3
ON t2.Id = t3.Id
4. Can INNER JOINS/OUTER JOINS be used in WHERE clause?
Yes they can.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2009 at 6:38 am
Grant, would you mind showing an example of how you would use joins in a where clause? I'm not sure what you mean.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 22, 2009 at 7:07 am
Oops. There I go again not reading everything before posting.
You can't.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2009 at 8:44 am
Thanks Grant for the answers.
Grant Fritchey (1/22/2009)
If I understand the question, yes, you can join one table to more than one other table like this:
SELECT...
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
JOIN Table42 t42
ON t1.Id = t42.Id
Can Table2 and Table42 be joined in this query?
SELECT ...
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
JOIN Table42 t42
ON t1.Id = t42.Id
AND t2.Id = t42.Id-- Is this correct?
3. inner join table4 statement is joined ON id, ON name. Here, ON clause is used twice. Is using 2 ONs same as ON and AND?
No. That's not an AND. That's just two seperate ON clauses. While you can format the code that way, most people, for ease of readability, format the code with each JOIN associated directly to the ON clause.
Does this mean, instead of ON and AND, for readability ON and ON can be used? Do the below 2 queries return the same result
select ....
from ....
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
select ....
from ....
inner join table4 t4
on t3.id = t4.id
and t2.name = t3.name
How are the tables joined in the below query? Aren't table1 and table2 joined both on id and name?
For example:
select ...
from table t1
inner join t2
on t1.id = t2.id
on t1.name = t2.name
January 22, 2009 at 8:52 am
Bob Hovious (1/22/2009)
Grant, would you mind showing an example of how you would use joins in a where clause? I'm not sure what you mean.
Well, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but
from table1, table2, table3, table4
where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name
joins in the where clause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 9:00 am
gyessql (1/22/2009)
Does this mean, instead of ON and AND, for readability ON and ON can be used?
No. They're not synonymous.
select ....
from ....
inner join table4 t4
on t3.id = t4.id
on t2.name = t3.name
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'on'.
How are the tables joined in the below query? Aren't table1 and table2 joined both on id and name?
Yup, that's a join based on two columns. It's not what you had earlier, where the two clauses did not refer to the same tables.
good practice (for readability) is to have the on straight after the join so.
from table1
inner join table2 on < join conditions between table1 and table2>
inner join table3 on < join conditions between table2 and table3, or between table1 and table3, whichever is the correct one>
inner join table4 on < join conditions between table4 and either table1, table2 or table3, whichever is correct>
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 9:13 am
Thanks Gail..
GilaMonster (1/22/2009)
Yup, that's a join based on two columns. ...
Is there any difference between joining in these 2 ways?
select ...
from table t1
inner join t2
on t1.id = t2.id
on t1.name = t2.name
select ...
from table t1
inner join t2
on t1.id = t2.id
and t1.name = t2.name
from table1inner join table2 on < join conditions between table1 and table2>
inner join table3 on < join conditions between table2 and table3, or between table1 and table3, whichever is the correct one>
inner join table4 on < join conditions between table4 and either table1, table2 or table3, whichever is correct>
Thanks Gail, this clears a lot of my questions.
January 22, 2009 at 9:24 am
gyessql (1/22/2009)
Is there any difference between joining in these 2 ways?
select ...
from table t1
inner join t2
on t1.id = t2.id
on t1.name = t2.name
select ...
from table t1
inner join t2
on t1.id = t2.id
and t1.name = t2.name
Yes. One of them will run and one of them will give you a syntax error.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2009 at 9:28 am
GilaMonster (1/22/2009)
Yes. One of them will run and one of them will give you a syntax error.
:D:D Good one!!
Thanks for all the clarifications..
January 22, 2009 at 10:09 am
GilaMonster (1/22/2009)
gyessql (1/22/2009)
Is there any difference between joining in these 2 ways?
select ...
from table t1
inner join t2
on t1.id = t2.id
on t1.name = t2.name
select ...
from table t1
inner join t2
on t1.id = t2.id
and t1.name = t2.name
Yes. One of them will run and one of them will give you a syntax error.
Thanks for picking up on all these Gail. I was off in meetings.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2009 at 10:56 am
GilaMonster (1/22/2009)[hrWell, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but
from table1, table2, table3, table4where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name
joins in the where clause.
Thanks Gail, I was familiar with that and wondered if that style was what Grant was referring to. I always think of that as being a WHERE clause to what is essentially an implied cross join. Never looked at the execution plan though.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 22, 2009 at 11:05 am
Bob Hovious (1/22/2009)
GilaMonster (1/22/2009)[hrWell, it's not the join keyword and it's an old way of doing things, and the outer join syntax only works in SQL 2000 and earlier, but
from table1, table2, table3, table4where t1.id = t2.id and t3.id = t4.id and t2.name = t3.name
joins in the where clause.
Thanks Gail, I was familiar with that and wondered if that style was what Grant was referring to. I always think of that as being a WHERE clause to what is essentially an implied cross join. Never looked at the execution plan though.
Nah, I was just being sloppy.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 22, 2009 at 11:09 am
Watch out... Cardinal Moden may be lurking about somewhere close by with pork chop in one hand and a fish in the other.
😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 22, 2009 at 11:21 am
Bob Hovious (1/22/2009)
Never looked at the execution plan though.
Providing you haven't left a cross join in by accident, it'll be the same as for the one with JOIN.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply