How do you represent the sub queries in SELECT and WHERE clauses?
+9
A:
Statements that include subqueries usually take one of these formats:
- WHERE expression [NOT] IN (subquery)
- WHERE expression comparison_operator [ANY | ALL] (subquery)
- WHERE [NOT] EXISTS (subquery)
Subquery Fundamentals (SQL Server 2008 Books Online, January 2009)
CMS
2009-02-09 06:42:32
Please note that many sub-queries are better represented as a join to a derived table (although the optimiser is getting better at translating them).
Mitch Wheat
2009-02-09 06:45:51
+3
A:
You can use subqueries in SELECT and WHERE clauses like this:
select
c.customerid,
(
select sum(i.amount) as totalspent
from item i
where i.customerid = c.customerid
) as totalspent
from customer c
where exists
(
select *
from purchase p
where p.customerid = c.customerid
)
jeremcc
2009-02-09 06:45:22
+1
A:
You've gotten some good anwsers, but try to see if you can change your logic such that the sub-query takes place in the from clause, so that it is only run once instead of once for each row returned by the main query.
Sub-queries really suck the performance out of a system.
Michael Walts
2009-02-09 07:14:40