Subquery Flashcards
What is a self-contained subquery?
- A subquery that has no dependency on the outer query.
- You could run the content of the subquery by itself and it would work just fine.
SQL Server 70-461 04-02a
What is a scalar subquery?
A query that returns a single value
SQL Server 70-461 04-02a
Where can a self-contained scalar subquery be used?
- Anywhere in the out query where a single-valued expression can appear.
- Examples are the WHERE and SELECT clauses
SQL Server 70-461 04-02a
What might a query using a scalar subquery look like?
SELECT orderid, orderdate, empid, custid FROM sales.orders WHERE orderid=(SELECT MAX(O.orderid) FROM sales.orders AS O)
In the code above, (SELECT MAX(O.orderid) FROM sales.orders AS O) is the scalar subquery and it returns a single value.
SQL Server 70-461 04-02a
What happens if what is supposed to be a scalar query returns more than one value?
It fails at run time
SQL Server 70-461 04-02a
What happens if a scalar query returns an empty set?
It is converted to a null
SQL Server 70-461 04-02a
What is a multi-valued subquery?
- A subquery that returns multiple values in the form of a single column.
- This would be useful if you wanted to use WHERE IN and get the values from a different table, for example.
SQL Server 70-461 04-02a
Where can a self-contained multi-valued subquery be used?
A good example is with IN or NOT IN. They are examples but they are rarely used.
SQL Server 70-461 04-02a
What might a query using a multi-valued subquery look like?
SELECT orderid FROM sales.orders WHERE empid IN (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE N'D%')
In the code above, (SELECT E.empid FROM HR.Employees AS E WHERE E.lastname LIKE NāD%ā) is the multi-valued subquery and it returns multiple values as a single column.
SQL Server 70-461 04-02a
Where is EXISTS placed in the query?
Just after WHERE in the outer query
SQL Server 70-461 04-02a
What is an example of what a query using EXISTS would look like?
SELECT custid, companyname FROM sales.customers AS C WHERE EXISTS (SELECT * FROM sales.orders AS O WHERE O.custid=C.custid AND O.orderdate='20070212')
This query returns customers only if they have had an order.
SQL Server 70-461 04-02a
Can you negate EXITSTS?
Yes. You can use NOT EXISTS
SQL Server 70-461 04-02a
What might an example of a correlated subquery look like?
SELECT categoryid, productid, productname, unitprice FROM production.products AS P1 WHERE unitprice= --Correlated Subquery ( SELECT MIN(unitprice) FROM production.products AS P2 WHERE P2.categoryid=P1.categoryid )
P1.categoryid in the correlated subquery correlates to the outer query
SQL Server 70-461 04-02a