SQL Server cross apply not working?

Question

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.

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. — Damien_The_Unbeliever, Dec 16 '13 at 14:46
@Damien_The_Unbeliever 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. — Registered User, Dec 16 '13 at 14:53
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... — Damien_The_Unbeliever, Dec 16 '13 at 14:59

RBarryYoung · Accepted Answer · 2013-12-16T15:07:33.600

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:

FROM clause
WHERE clause
SELECT columns
ORDER BY clause
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).

Ooo! I think that I earned my Secret Hat from this answer! Still don't know why though ... ? — RBarryYoung, Dec 16 '13 at 15:22

score 1 · Answer 2 · answered Dec 16 '13 at 14:55

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

You only have 1 row for each value in `depttb`, I don't understand what you are trying to do with `TOP 1`. — Andrew, Dec 16 '13 at 15:04

CRAFTY DBA · Answer 3 · 2013-12-16T16:22:47.670

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

`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. — Andrew, Dec 16 '13 at 14:50
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. http://sqlblog.com/blogs/rob_farley/archive/2011/04/13/the-power-of-t-sql-s-apply-operator.aspx — CRAFTY DBA, Dec 16 '13 at 16:11

SQL Server cross apply not working?

3 Answers3