|
|
Posted by Plamen Ratchev on 01/23/08 14:44
Is this a valid query? I mean if the UNION of the two SELECT statements
returns more than a single value then you will get an error (at least on SQL
Server).
Assuming your WHERE conditions guarantee a single value from the UNION, then
you could check that in the main query WHERE:
SELECT 'a' AS a,
(SELECT NULL
UNION
SELECT NULL) AS x
WHERE (SELECT NULL
UNION
SELECT NULL) IS NOT NULL
But I really do not think it is a better solution than using a derived
table. Derived tables are a good tool, and there is no penalty for using
them. They are virtual and not materialized physically, and the optimizer
will generate the same plan with or without them. I find it more intuitive
when written like this:
SELECT a, x
FROM (SELECT 'a',
(SELECT NULL
UNION
SELECT NULL)) AS T(a, x)
WHERE x IS NOT NULL
The logical query processing order is as follows:
1) FROM
2) ON
3) OUTER
4) WHERE
5) GROUP BY
6) HAVING
7) SELECT
8) ORDER BY
As you can see SELECT is processed after WHERE, and this is why you cannot
use the column alias for 'x' (which is defined only after the SELECT is
processed) in the WHERE filter.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|