SQL Server cross apply not working?

Question

http://sqlfiddle.com/#!3/78273/1

create table emptb1
(
id int,
name varchar(20),
dept int
)

insert into emptb1 values (1,'vish',10);
insert into emptb1 values (2,'vish',10);
insert into emptb1 values (3,'vish',30);
insert into emptb1 values (4,'vish',20);

create table depttb1
(
id int,
name varchar(20)
)

insert into depttb1 values(10,'IT')
insert into depttb1 values(20,'AC')
insert into depttb1 values(30,'LIC')

select * from emptb1

select e.id, e.name, a.id
from emptb1 e
cross apply
(
select top 1 * from depttb1 d
where d.id = e.dept
order by d.id desc
) a

I was trying to learn cross apply as it's similar as inner join but works with function.

In above query I'm assuming it should take only dept=30 because order d.id desc will give only top 1st id which is 30 and then it should return employees with dept id = 30 but it's giving me all the rows and all the deptid.

What's wrong with query or I'm wrong interpreting the concept of cross apply.

Nova

What you're query is actually saying is "for each employee, find all of the departments that they work for, and select the department with the highest department id that that employee works for" - now, obviously, with your schema, each employee only works for one department, but it's still going to return one row per employee. – Knightly

@Knightly Okay If an employee is working for two different dept like 40 and 50 it will give me only 50. but I tried inserting an employee with 40 and 50 deptid but it still gives me both the deptid. – Nova

That's because the only way to model having an employee in two departments in your current schema is to have two rows in emptbl with (presumably) the same id and name? If so, look again at your query - it says nothing about those columns during the cross apply - so first it does it for the row where deptid is 40, and the only matching row in depttbl is the one with id 40. So that's the top 1 matching row for the first row from emptbl. Then, we do the same process for the row where deptid is 50. This time, we match a row with id 50, and it's the top 1 again... – Knightly

Answer 1

You say "In above query I'm assuming it should take only dept=30 because order d.id desc will give only top 1st id which is 30 and then it should return employees with dept id = 30".

That's not how it works. Here's your query (reformatted a little for clarity):

select e.id, e.name, a.id
from   emptb1 e
cross apply
(
    select top 1 * 
    from depttb1 d
    where d.id = e.dept
    order by d.id desc
) a

The APPLY keyword means that the inner query is (logically) called once for each row of the outer query. For what happens inside the inner query, it's helpful to understand the logical order that the clauses of a SELECT are executed in. This order is:

1. FROM clause
2. WHERE clause
3. SELECT columns
4. ORDER BY clause
5. TOP operator

Note that in your inner query then, the TOP operator gets applied last, well after the WHERE clause. This means the where d.id = e.dept will first reduce the inner rows to those whose d.id matches the e.dept of the outer row (which is not necessarily 30), then sort them, and then return the first one. And it does this for every row in the outer query. So obviously, many of them are not going to be 30.

What you are trying to would be more akin to this (still retaining the CROSS APPLY):

select e.id, e.name, a.id
from   emptb1 e
cross apply
(
    select top 1 * 
    from
    (
        select top 1 * 
        from depttb1 d
        order by d.id desc
    ) b
    where b.id = e.dept
) a

Here, the logic has been reordered by use of another, nested, sub-query that insures that the ORDER BY, then TOP 1 get applied before the WHERE clause. (Note that this would not normally the recommended way to do this as nested sub-queries can hamper readability, I just used it here to retain the CROSS APPLY and to retain the rest of the original structure).

Qualification

Ooo! I think that I earned my Secret Hat from this answer! Still don't know why though ... ? – Qualification

Answer 2

To exand on Damien's comment, the inner query:

select top 1 * from depttb1 d
where d.id = e.dept
order by d.id desc

is going to run for every row in the outer query:

select e.id, e.name, a.id
from emptb1 e

So you will always get a match from the inner query for each row. I think you were expecting the inner query to run only one time, but that's not what APPLY does.

So, taking the first row from your outer query, with an ID of 1 and a dept id of 10, your inner query will translate to:

select top 1 * from depttb1 d
where d.id = 10  //this is the dept id for the current row from your outer query
order by d.id desc

Thessa

then what's the user of top 1 * – Nova

You only have 1 row for each value in depttb, I don't understand what you are trying to do with TOP 1. – Thessa

Answer 3

To solve this problem without a cross apply, use a sub query. However in your example it will only return one row, the last department entered assuming id value is increasing.

-- Using a sub query to find max dept
select e.id, e.name
from emptb1 e
where e.dept in
(
select top 1 id 
from depttb1 
order by id desc
)

The idea behind a CROSS APPLY is kinda like a CROSS JOIN. This will return all rows. It is used by DBA's with many of the Dynamic Management View (DMVs) that are Table Value Functions (TVF)

What you want is a OUTER APPLY kinda like a LEFT JOIN.

select e.id, e.name
from emptb1 e
outer apply 
    (
    select top 1 d.id from depttb1 d order by d.id desc
    ) AS m (id)
where e.dept = m.id

Check out my articles on these concepts.

CROSS APPLY - http://craftydba.com/?p=3767

OUTER APPLY - http://craftydba.com/?p=3796

TABLE VALUE FUNCTION (INLINE) - http://craftydba.com/?p=3733

TABLE VALUE FUNCTION (MULTI LINE) - http://craftydba.com/?p=3754

Paresthesia

CROSS APPLY and OUTER APPLY return the same results in this case, no? OUTER APPLY will return rows from the 'outer' table even if there is no match from the 'inner' table. – Thessa

Yes - CROSS = OUTER in this toy example. However, if we had something more interesting, like total sale per order by department and we wanted the top 3, if there was data, or a null entry if there was not, the outer apply is the way to go. Check out Rob Farley's article. sqlblog.com/blogs/rob_farley/archive/2011/04/13/… – Paresthesia