August 14, 2009 at 2:13 am
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 🙂
August 14, 2009 at 2:15 am
August 14, 2009 at 2:38 am
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
August 14, 2009 at 3:48 am
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
August 14, 2009 at 3:51 am
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 🙂
August 14, 2009 at 3:57 am
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
August 14, 2009 at 3:57 am
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
August 14, 2009 at 4:02 am
August 14, 2009 at 4:04 am
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?
August 14, 2009 at 4:14 am
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
August 14, 2009 at 4:16 am
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
August 14, 2009 at 4:47 am
August 14, 2009 at 8:19 am
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]
August 14, 2009 at 8:48 am
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 25 total)
You must be logged in to reply to this topic. Login to reply