Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2003
    Posts
    130

    Question Unanswered: outer join shortcut?

    Is this a shortcut for an outer join and if so, why can't I find any info on it?

    Code:
    select id, name
    from table1, table2
    where id *= id_tbl2
    instead of

    Code:
    select tb1.id, tb1.name
    from table1 as tb1
    outer join table2 as tb2
    where tb1.id = tb2.id_tbl2

    and what happens in the first example if you try to join more tables?
    Last edited by wey97; 11-20-07 at 16:14.

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    http://en.wikipedia.org/wiki/Join_(SQL)

    the first is an inner join.

    the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by amthomas
    http://en.wikipedia.org/wiki/Join_(SQL)

    the first is an inner join.

    the 2nd would show all result (left outer join) for table1 and only combine the result from table2 that matched. Of course I could be wrong on this if it is something MS SQL specific. Does it allow for just 'outer join'?
    according to sql server, the first is an outer join.

    you can verify that by attempting

    Code:
    select id
    from table1
    where id *= 1
    and the error message is

    Code:
    Msg 150, Level 15, State 1, Line 5
    Both terms of an outer join must contain columns.

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    *= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.

    It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    oops, I am sorry. that was something ms sql specific. I should not have answered that. I just didn't see the little * :/

    sorry about that. well.. at least I learned something
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  6. #6
    Join Date
    Mar 2003
    Posts
    130
    Quote Originally Posted by tomh53
    *= is indeed a left outer join, just as =* is a right outer join. It is a T_SQL extension and was valid up thru S2K. It is no longer available in S2K5.

    It is not in the ANSI Standard, so if you want your code to run in SQL Server later than 2000, do not use it!
    Code I inherited.

    I don't think the db admin knew exactly what he was doing, other than taking a shortcut to write less code.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Its not a shortcut. Its an "old cut". It used to be the standard coding syntax, so far back in the mists of time that only wise men such as Pat Phelan know the origins.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2011
    Posts
    2
    Hello,

    I realize this post is a little old but...

    I have been using the 'old cut' for a long time now without issue. I have recently been trying to convert to the 'ANSI Standard' with mixed results. I am sure it is user-error but I often have problems pulling the correct rows with the 'ANSI Standard' and can get exactly what I need with the 'old cut' way.

    Maybe somebody can see what I am doing wrong here. I have tried changing my joins to left outer/inner with no luck.

    'Old cut' way, returns the needed 589 records:
    select amt.family_id, u.user_type_id, ui.lastname + ', ' + ui.firstname as StuName, sf.sf_file_name
    from advisor_student_matches amt, user_info ui, users u, student_files sf
    where amt.edcom_id = ui.edcom_id
    and ui.edcom_id = u.edcom_id
    and ui.edcom_id *= sf.sf_edcom_id
    and amt.delete_date is null
    and sf.sf_file_type = 'pdf'
    order by amt.family_id, u.user_type_id


    'ANSI Standard' way, returns 142 records:
    select amt.family_id, u.user_type_id, ui.lastname + ', ' + ui.firstname as StuName, sf.sf_file_name
    from advisor_student_matches amt
    left join user_info ui on amt.edcom_id = ui.edcom_id
    left join users u on ui.edcom_id = u.edcom_id
    left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id
    where amt.delete_date is null
    and sf.sf_file_type = 'pdf'
    order by amt.family_id, u.user_type_id


    Any help would be appreciated.

    Thanks

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id 
    where amt.delete_date is null
    and sf.sf_file_type = 'pdf'
    to this --
    Code:
    left outer join student_files sf on ui.edcom_id = sf.sf_edcom_id 
    and sf.sf_file_type = 'pdf'
    where amt.delete_date is null
    you will be surprised at the difference in results


    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    Its better to use outer join instead of shortcut for performance gain

  11. #11
    Join Date
    Sep 2011
    Posts
    31
    Quote Originally Posted by jassi.singh View Post
    Hello,

    Its better to use outer join instead of shortcut for performance gain
    Though join shortcuts have been depracated now, can you elaborate how will this help in performance?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you include a column from an outer-joined dataset in the WHERE clause, it effectively turns it into an inner join. So, as Rudy showed, you need to include such filters in the outer join itself:
    Code:
    select	amt.family_id,
    	u.user_type_id,
    	ui.lastname + ', ' + ui.firstname as StuName,
    	sf.sf_file_name
    from	advisor_student_matches amt
    	left outer join user_info ui on amt.edcom_id = ui.edcom_id
    	left outer join users u on ui.edcom_id = u.edcom_id
    	left outer join student_files sf
    		on ui.edcom_id = sf.sf_edcom_id
    		and sf.sf_file_type = 'pdf'
    where	amt.delete_date is null
    order by amt.family_id,
    	u.user_type_id
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Sep 2011
    Posts
    75
    Quote Originally Posted by cindyaz View Post
    Though join shortcuts have been depracated now, can you elaborate how will this help in performance?
    YOu can write both the quries in ssms and then clcik on estimated execution plan button to see the total cost and resource usage

  14. #14
    Join Date
    Oct 2011
    Posts
    2

    Thumbs up

    Sorry, I didn't mean to abandon my posting. I got tasked onto something else here at work.

    Your suggestions worked like a charm; I get the records I need. Thank you for the help and teaching me something new.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sdtacoma View Post
    Your suggestions worked like a charm;
    you are of course referring to post #9

    thanks very much
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •