t-sql join

17 Sep 2005 10:27 AM

Duncan Mole

Hi,

Is it possible to join the result of a subquery? Something like;

SELECT * FROM EMPLOYEES EMP
JOIN (SELECT COUNT(*) FROM ORDERS WHERE EMP.EMPLOYEEID = O.EMPLOYEEID)

17 Sep 2005 10:41 AM

John Bell

Hi

You would have to do either:

SELECT EMP.EMPLOYEEID, O.CNT
FROM EMPLOYEES EMP
JOIN (SELECT EMPLOYEEID, COUNT(*) AS cnt FROM ORDERS GROUP BY EMPLOYEEID) O
ON EMP.EMPLOYEEID = O.EMPLOYEEID

OR

SELECT EMP.EMPLOYEEID, (SELECT COUNT(*) FROM ORDERS WHERE EMP.EMPLOYEEID =
O.EMPLOYEEID) AS CNT
FROM EMPLOYEES EMP

John

Show quote

"Duncan Mole" <***@***.com> wrote in message
news:eJNfcJ3uFHA.3152@TK2MSFTNGP12.phx.gbl...
> Hi,
>
> Is it possible to join the result of a subquery? Something like;
>
> SELECT * FROM EMPLOYEES EMP
> JOIN (SELECT COUNT(*) FROM ORDERS WHERE EMP.EMPLOYEEID = O.EMPLOYEEID)
>

17 Sep 2005 10:43 AM

David Portas

You can join to the result of a query as a derived table:

SELECT *
FROM employees AS emp
JOIN
(SELECT *
FROM orders
WHERE customerid = 'HANAR') AS O
ON emp.employeeid = O.employeeid ;

However, you want a correlated subquery, which can't be done in the derived
table because logically it has to be possible to construct that result
before it can be joined. The solution is a subquery in the SELECT list:

SELECT *,
(SELECT COUNT(*)
FROM orders
WHERE emp.employeeid = orders.employeeid)
FROM employees AS emp ;

--
David Portas
SQL Server MVP
--

17 Sep 2005 10:56 AM

Duncan Mole

Yeah, thanks for that. I was actually trying to avoid the subquery in SELECT
list. The example I gave is very simplistic, in reality the result set is
populating a table and the subquery value is used in two places. Obviously
having 2 SELECT subqueries will be less performant. Any thoughts?

Show quote

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:CaidncfUKNPebrbeRVn-rg@giganews.com...
> You can join to the result of a query as a derived table:
>
> SELECT *
> FROM employees AS emp
> JOIN
> (SELECT *
> FROM orders
> WHERE customerid = 'HANAR') AS O
> ON emp.employeeid = O.employeeid ;
>
> However, you want a correlated subquery, which can't be done in the
> derived table because logically it has to be possible to construct that
> result before it can be joined. The solution is a subquery in the SELECT
> list:
>
> SELECT *,
> (SELECT COUNT(*)
> FROM orders
> WHERE emp.employeeid = orders.employeeid)
> FROM employees AS emp ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>

17 Sep 2005 1:30 PM

Erland Sommarskog

Duncan Mole (***@***.com) writes:

> Yeah, thanks for that. I was actually trying to avoid the subquery in
> SELECT list. The example I gave is very simplistic, in reality the
> result set is populating a table and the subquery value is used in two
> places. Obviously having 2 SELECT subqueries will be less performant.
> Any thoughts?

You are right that have multiple instances of a correlated subquery
is not good for performance. In fact, in my experiences correlated
subquries in the SELECT in general can often hur performance. Instead
derived tables can often be better:

   SELECT *, o.cnt
   FROM   employess AS emp
   JOIN   (SELECT employeeid, cnt = COUNT(*)
           FROM   orders
           GROUP BY employeeid) AS o ON emp.employeeid = o.employeeid

But if you want to have certainty, the only way to find out is to
benchmark.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp