SQL Joins

  • Vivek Johari

    SSC Veteran

    Points: 236

    Comments posted to this topic are about the item SQL Joins

    My Blog
    http://www.vivekjohari.blogspot.com

  • cs-892458

    SSC Enthusiast

    Points: 110

    This is a very good article. To be complete it only needs to explain semi-joins, anti-semi-joins and cross apply.

    If someone could explain how cross apply works, I'd be a very happy man 🙂

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    That's odd... this logged me in under a different account. Ah well.

    Random Technical Stuff[/url]

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    I have one question -

    I posed a question on our technical tests for new DBA/Developers and I asked

    "Name All Types of join you are aware of"

    The question was deliberatly loosely worded as I was looking to see how deep the person's understanding of joins was

    I was looking for

    Logical = Inner, Left/Right/Full outer , Cross Apply

    Physical = Hash, Merge, Loop

    this would demonstrate they understood not only the function of the join, but how the SQL engine behaves and physically performs these joins.

    Now when we recruit in india I always get SELF JOIN added to the answers (but never in the UK for some reason)- I immediatly scratch this answer off as incorrect - but should i???

    In the example SELF join actually used the INNER JOIN syntax - it does not matter which tables you join together it is still an INNER JOIN...

    how about if the "self join" used a left OUTER??? is it still a self join? nope - it's a left outer (In my opinion)

    Any thoughts??? In My opinion "SELF JOIN" is a nickname , but it does not functionally describe the join itself (ie you don't know if it's left , right, inner or outer"

    Mike Vessey

    MVDBA

  • Gail Shaw

    SSC Guru

    Points: 1004474

    cs (8/14/2009)


    If someone could explain how cross apply works, I'd be a very happy man 🙂

    I'll give it a try.

    CREATE TABLE #SomeTable (Col1 int)

    Insert into #SomeTable Values (1)

    Insert into #SomeTable Values (2)

    Insert into #SomeTable Values (3)

    Insert into #SomeTable Values (4)

    GO

    CREATE Function dbo.SomeFunction (@Input int)

    RETURNS TABLE AS

    RETURN (SELECT @Input-1 AS Result union all SELECT @Input AS Result)

    GO

    SELECT Col1, func.result

    FROM #SomeTable st CROSS APPLY dbo.someFunction(st.Col1) AS func

    For each row in SomeTable the CROSS APPLY will run the table-valued function dbo.SomeTable and pass it the value of Col1 from that row of SomeTable. Based on the table and function created above, the results would be

    1,0

    1,1

    2,1

    2,2

    3,2

    3,3

    4,3

    4,4

    Make sense so far?

    Cross apply is like inner join, if the function doesn't return a row for a particular parameter, that parameter won't appear in the resultset. If you want outer join behaviour, use OUTER APPLY

    Cross apply with a subquery is pretty much the same as with a function, it's the subquery that's run once for each row. The equivalent of that function above with a subquery would be this:

    SELECT Col1, func.result

    FROM #SomeTable st CROSS APPLY

    (SELECT st.Col1 UNION ALL SELECT st.Col1-1 ) AS func

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    michael vessey (8/14/2009)


    I have one question -

    I posed a question on our technical tests for new DBA/Developers and I asked

    "Name All Types of join you are aware of"

    The question was deliberatly loosely worded as I was looking to see how deep the person's understanding of joins was

    I was looking for

    Logical = Inner, Left/Right/Full outer , Cross Apply

    Physical = Hash, Merge, Loop

    this would demonstrate they understood not only the function of the join, but how the SQL engine behaves and physically performs these joins.

    Now when we recruit in india I always get SELF JOIN added to the answers (but never in the UK for some reason)- I immediatly scratch this answer off as incorrect - but should i???

    In the example SELF join actually used the INNER JOIN syntax - it does not matter which tables you join together it is still an INNER JOIN...

    how about if the "self join" used a left OUTER??? is it still a self join? nope - it's a left outer (In my opinion)

    Any thoughts??? In My opinion "SELF JOIN" is a nickname , but it does not functionally describe the join itself (ie you don't know if it's left , right, inner or outer"

    Mike Vessey

    Why would you ever want to do a left outer self join? A left outer self join means that you want to find every match and you want to find all rows that don't match. Sounds like a cross join to me.

    Others might see the flaw in my reasoning... is there something I'm missing?

    One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that 🙂

    Random Technical Stuff[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004474

    ta.bu.shi.da.yu (8/14/2009)


    Why would you ever want to do a left outer self join?

    CREATE TABLE #SomeTable (Col1 int)

    Insert into #SomeTable Values (1)

    Insert into #SomeTable Values (2)

    Insert into #SomeTable Values (3)

    Insert into #SomeTable Values (4)

    GO

    SELECT t1.Col1, t2.Col1 FROM

    #SomeTable t1 LEFT OUTER JOIN #SomeTable t2 ON t1.Col1 = t2.Col1-1

    A left outer self join means that you want to find every match and you want to find all rows that don't match. Sounds like a cross join to me.

    It's not a cross join. Cross join returns all possible combinations. Left outer join means return all rows in the left table, where the join condition matches rows from the right table return them, where they do not make those columns null.

    With the table above, a cross join to itself would return 16 rows. The left outer join to itself will return 4.

    One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that 🙂

    There's nothing at all special about a 'self' join. It's a standard inner or outer join that just happens to have the same table on both sides. Nothing more.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    left outer self join - easy

    show me all employees and their managers

    include all employees that do not have a manager

    select e1.*,e2.name from employee e1 left outer join employee e2 on e1.managerid=e2.employeeid

    MVDBA

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    I stand corrected 🙂

    Random Technical Stuff[/url]

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    GilaMonster (8/14/2009)


    ta.bu.shi.da.yu (8/14/2009)


    One point I would like to make though, is that it's definitely a special type of join where the left and right tables are the same table. Therefore there is no point calling it a left or right outer join. It is what it says it is - it's a self join. To be precise, a self inner join I guess, but nobody calls it that 🙂

    There's nothing at all special about a 'self' join. It's a standard inner or outer join that just happens to have the same table on both sides. Nothing more.

    Ah, OK. But I think that calling it a left outer join is a bit of a misnomer. Could be a right outer join to, right?

    Random Technical Stuff[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004474

    ta.bu.shi.da.yu (8/14/2009)


    But I think that calling it a left outer join is a bit of a misnomer. Could be a right outer join to, right?

    If it was specified as FROM Table1 t1 RIGHT OUTER JOIN Table1 t2 ON .... yes. Self-join is not a technical term. All it indicates is that a table is joined to itself. The term 'self join' doesn't define what the join type is.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21757

    nope

    the use of left or right determines which side of the join returns all results

    my example is clearly a left outer join

    if this were right outer then the results would be different

    i would be asking for "show me all employeesand the name of the person they manage - include all people that do no manage anyone"

    .eg

    select e1.*,e2.name from employee e1 left outer join employee e2 on e1.managerid=e2.employeeid

    assume "maggie os" has no manager and "paul smith" manages no-one

    returns (example data)

    Name id active manager

    john smith 1 1 fred jones

    abe lincoln 2 1 fred jones

    maggie os 3 1 NULL

    paul smith 4 1 fred jones

    .......

    select e1.*,e2.name from employee e1 RIGHT outer join employee e2 on e1.managerid=e2.employeeid

    returns (example data)

    Name id active manager

    john smith 1 1 fred jones

    abe lincoln 2 1 fred jones

    NULL NULL NULL Paul Smith

    Paul Smith 4 1 fred jones

    MVDBA

  • ta.bu.shi.da.yu

    Hall of Fame

    Points: 3985

    Very interesting - I've never thought of it that way before. But absolutely, that makes total sense.

    You'll have to forgive the newbie questions, but I am the eternal learner 🙂

    Random Technical Stuff[/url]

  • Thordog

    Ten Centuries

    Points: 1182

    I'd like to work for a company where I am my boss's boss. 😎 (self join exampl)

    [font="Courier New"]____________________________________________________________________________________________
    Remember as you walk down lifes road, don't forget to stop and pee on the bushes - Thordog
    [/font]

  • rtodd-997233

    SSC Enthusiast

    Points: 117

    I agree with you. A self join is not a 'type' of join because it only means that your two tables in your join are the same. It is no different from any other join. What if you join a table to a VIEW of itself? Is THAT a self join? Not even close, because the view might a have a where clause that eliminates some records....

    The biggest indicator that SELF join is not a type of join, is that you cannot use the word 'SELF' in the syntax of your SQL....all the other types of joins are valid SQL statements

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply