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.
The CROSS APPLY operator is semantically similar to INNER JOIN operator. It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two.
While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.
In simple terms, a join relies on self-sufficient sets of data, i.e. sets should not depend on each other. On the other hand, CROSS APPLY is only based on one predefined set and can be used with another separately created set.
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
clauseWHERE
clauseSELECT
columnsORDER BY
clauseTOP
operatorNote 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).
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With