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.
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):
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 aSELECT
are executed in. This order is:FROM
clauseWHERE
clauseSELECT
columnsORDER BY
clauseTOP
operatorNote that in your inner query then, the
TOP
operator gets applied last, well after theWHERE
clause. This means thewhere d.id = e.dept
will first reduce the inner rows to those whosed.id
matches thee.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 be30
.What you are trying to would be more akin to this (still retaining the
CROSS APPLY
):Here, the logic has been reordered by use of another, nested, sub-query that insures that the
ORDER BY
, thenTOP 1
get applied before theWHERE
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 theCROSS APPLY
and to retain the rest of the original structure).