+ Reply to Thread
Results 1 to 9 of 9
  1. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #1

    Default Perferred method of joining multiple tables?

    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?
    Reply With Quote Quote  

  2. SS -->
  3. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #2
    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
    Reply With Quote Quote  

  4. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #3
    Quote Originally Posted by RobertKaucher View Post
    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.
    Reply With Quote Quote  

  5. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #4
    Quote Originally Posted by N2IT View Post
    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.
    Reply With Quote Quote  

  6. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #5
    Quote Originally Posted by RobertKaucher View Post
    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.
    Last edited by N2IT; 02-07-2011 at 09:05 PM.
    Reply With Quote Quote  

  7. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #6
    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?
    Reply With Quote Quote  

  8. Reply With Quote Quote  

  9. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #8
    Quote Originally Posted by N2IT View Post
    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!
    Reply With Quote Quote  

  10. Senior Member
    Join Date
    Apr 2010
    Posts
    7,363
    #9
    Quote Originally Posted by RobertKaucher View Post
    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.
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks