Subconsultas
Las subconsultas pueden ser distinguidas de dos formas. Una es por el numero esperado de valores (escalar o multivalores), y otra es por la dependencia en otra consulta. Ambas subconsultas escalar y multivalor pueden ser contenidas asi misma.
Una subconsulta autocontenida es una subconsulta que puede ser ejecutada independientemente
de otra consulta. Las subconsultas autocontenidas son muy convenientes para depurar a subconsultas correlacionadas.
Scalar subqueries can appear anywhere in the query where an expression resulting in a scalar value is expected, while multivalued subqueries can appear anywhere in the query where a collection of multiple values is expected.
A scalar subquery is valid when it returns a single value, and also when it returns no valuesin which case, the value of the subquery is NULL. However, if a scalar subquery returns more than one value, a run-time error will occur.
Por ejemplo, ejecuta la siguiente consulta 3 veces: una como se muestra, segundo con LIKE N'Kollar' en lugar de LIKE N'Davolio', y la terceracon LIKE N'D%:
SET NOCOUNT ON; USE Northwind;
SELECT OrderID FROM dbo.Orders WHERE EmployeeID =
(SELECT EmployeeID FROM dbo.Employees
-- also try with N'Kollar' and N'D%' in place of N'Davolio'
WHERE LastName LIKE N'Davolio');
Con N'Davolio', la subconsulta retorna un simple valor (1) y la otra consulta retorna todas las
ordenes con la EmployeeID 1.
With N'Kollar', the subquery returns no values, and is therefore NULL. The outer query obviously doesn't find any orders for which EmployeeID = NULL and therefore returns an empty set. Note that the query doesn't break (fail), as it's a valid query.
With N'D%', the subquery returns two values (1, 9), and because the outer query expects a scalar, it breaks at run time and generates the following error:
Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Entradas
