Perferred method of joining multiple tables?

N2ITN2IT 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?

Comments

  • RobertKaucherRobertKaucher Member Posts: 4,298 ■■■■■■■■■■
    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
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • RobertKaucherRobertKaucher Member Posts: 4,298 ■■■■■■■■■■
    N2IT wrote: »
    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.
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
  • N2ITN2IT 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?
  • RobertKaucherRobertKaucher Member Posts: 4,298 ■■■■■■■■■■
    N2IT wrote: »
    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!
  • N2ITN2IT Inactive Imported Users Posts: 7,483 ■■■■■■■■■■
    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.
Sign In or Register to comment.