Perferred method of joining multiple tables?
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
I like to use this method
select columns (qualified if need or with aliases)
from table1, table2, table3,
where table.column = table2.column
and table2.column = table3.column;
Of course using grouping or order by if required.
I also run them this way to, but I find the first method easier.
select columns (again qualified the columns that are joined)
from table join table2 (I usually run aliases)
on table.column = table2.column
join table3
on table2.column = table3.column;
(Sometimes I will throw in comments to remind me of the query). Other times I will run top 100 if the dataset is huge etc.
I know we have some DBA's in here so I wanted to check and see what you all recommended. I only use SQL for reporting, but it's against different databases DB2, Oracle, and MS so I like to keep it simple, but of course I need the data to be accurate.
One other note, if I need to run a 3 table join, but need the nulls or non matching rows returned I will run a left right or full join. In that case I default to the second syntax. Any expert opinion on how I should proceed to write them?
select columns (qualified if need or with aliases)
from table1, table2, table3,
where table.column = table2.column
and table2.column = table3.column;
Of course using grouping or order by if required.
I also run them this way to, but I find the first method easier.
select columns (again qualified the columns that are joined)
from table join table2 (I usually run aliases)
on table.column = table2.column
join table3
on table2.column = table3.column;
(Sometimes I will throw in comments to remind me of the query). Other times I will run top 100 if the dataset is huge etc.
I know we have some DBA's in here so I wanted to check and see what you all recommended. I only use SQL for reporting, but it's against different databases DB2, Oracle, and MS so I like to keep it simple, but of course I need the data to be accurate.
One other note, if I need to run a 3 table join, but need the nulls or non matching rows returned I will run a left right or full join. In that case I default to the second syntax. Any expert opinion on how I should proceed to write them?
Comments
-
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
The top method is not ANSI compliant SQL and has serveral issues. Don't use it.
The thing I dislike about it the most is that it confuses the join and filter (where) syntax and that could lead to hard to troubleshoot issues with large queries.
sql server - ANSI vs. non-ANSI SQL JOIN syntax - Stack Overflow -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
RobertKaucher wrote: »The top method is not ANSI compliant SQL and has serveral issues. Don't use it.
The thing I dislike about it the most is that it confuses the join and filter (where) syntax and that could lead to hard to troubleshoot issues with large queries.
sql server - ANSI vs. non-ANSI SQL JOIN syntax - Stack Overflow
Thanks for your insight. -
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
Thanks for your insight.
No problem. I realize saying "don't use it." sounds a bit dogmatic, but I am a convert to the ANSI standard and try to stick to it as much as possible. If you look at any of Celko's books, he'll convert you to the religion as well. -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
RobertKaucher wrote: »No problem. I realize saying "don't use it." sounds a bit dogmatic, but I am a convert to the ANSI standard and try to stick to it as much as possible. If you look at any of Celko's books, he'll convert you to the religion as well.
Right on.
The main book I am using "Database Processing" (Kronke and Auer) it's my main source of study for 70-433. I've read that book cover to cover 3-4 times, not to mention all the times I have referenced it. I have also read 70-433 and both really drill on the ANSI style, except for the beginning chapters of Kronke and Auer's book. They use the non ANSI, which threw me off. I am basically using the books from when I took my database classes at the local community college. Both classes referenced 2008 SQL, but touched on MYSQL and Oracle as well.
That's where I am getting my knowledge from basically besides the experience I get at work. -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
Robert:
I was checking on Celko's books off of Amazon. He has a lot of them! I was wondering if his books come with practice disk, of if he has you creating the tables before you start querying data and using TSQL.
Another queston: Does he strictly use T-SQL? -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
-
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
Robert:
I was checking on Celko's books off of Amazon. He has a lot of them! I was wondering if his books come with practice disk, of if he has you creating the tables before you start querying data and using TSQL.
Another queston: Does he strictly use T-SQL?
As for the disk, not that I am aware of.
Most of his books are geared to ANSI SQL including somethings that SQL Server does not yet support. I really enjoy his puzzle books. They really make you think about how you go about things. Be careful, though, they may turn you into a full-blown DBA! Once you get hooked on solving more and more different types of problems, you get hooked! -
N2IT
Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
RobertKaucher wrote: »As for the disk, not that I am aware of.
Most of his books are geared to ANSI SQL including somethings that SQL Server does not yet support. I really enjoy his puzzle books. They really make you think about how you go about things. Be careful, though, they may turn you into a full-blown DBA! Once you get hooked on solving more and more different types of problems, you get hooked!
Sounds exciting!
Thanks again for the heads up.
