Prev: Issues with Select statement doing 'simple' math
Next: Linked Server Hanging
From: Plamen Ratchev on 28 Apr 2010 10:47
trubar a wrote:
> hi
>
> a) If a query/subquery doesn�t find any matching rows, then it either
> returns NULL or no value at all, thus not even a NULL value. Based on
> what criteria does a query or a subquery return a NULL and when
> doesn�t it return any results, not even a NULL value?
>

The subquery always returns the same result but depending on the operator that is used it can be treated differently. If
assignment (like in SELECT list) and the query returns no values, then the result is NULL because there is no value to
assign. If you use the subquery with the IN predicate then if not values match it is treated as empty set.

> b) I assume a scalar subquery will always return NULL, when no
> matching rows are found? I assume most-outer scalar query also returns
> NULL if no rows are found?
>

The result is NULL as no values are returned by the subquery.

>
> c)
>
> SELECT FirstName, LastName, YEAR(BirthDate)
> FROM Persons
> WHERE YEAR(BirthDate) IN (SELECT YearReleased FROM Albums);
>
> * If subquery finds no results, is then a WHERE clause of an outer
> query translated into �WHERE YEAR(BirthDate) IN (null);�
>
> * If instead WHERE clause is translated into �WHERE YEAR(BirthDate) IN
> ();� then shouldn�t that be an error condition, since how can
> YEAR(BirthDate) value be compared to nothing?
>

It is translated to YEAR(BirthDate) IN (). This is not an error condition, SQL is set based language and an empty set is
just fine.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Issues with Select statement doing 'simple' math
Next: Linked Server Hanging