cs (8/14/2009)

---

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

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 

SELECT Col1, func.result
FROM #SomeTable st CROSS APPLY 
(SELECT st.Col1 UNION ALL SELECT st.Col1-1 ) AS func

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

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.

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 :)

GilaMonster (8/14/2009)

---

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.