Hi,

I have a query like this :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...

The problem is that I don't want to return the results where x3 is
NULL.

Writing :

SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ... AND x3 IS NOT NULL

doesn't work.

The only solution I found is to write :

SELECT * FROM
(
(SELECT
x1,
x2,
( SELECT ... FROM ... WHERE ...
UNION
SELECT ... FROM ... WHERE ...) as x3
FROM ...
WHERE ...
) AS R1
)
WHERE R1.x3 IS NOT NULL

Is there a better solution? Can I use an EXISTS clause somewhere to
test if x3 is null without having to have a 3rd SELECT statement?
There's probably a very simple solution to do this, but I didn't find
it.

Thanks

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

FROM ... ,
Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thanks for your answers

P. Ratchev :

Fortunately, it's impossible that the UNION returns more than 1
value ! Thanks for your explanations...


S. Rielau :

Unfortunately, this can't work in my case, because, in my case, I
would have :
SELECT ...
FROM X,
(SELECT ... FROM Y WHERE Y.a=X.a
UNION
............)

The 2nd "FROM" depends on the 1st.

Sorry, I shoud be more precise.

Hiho

I fail to see the problem...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Perhaps you mean something like this (SQL Server 2005 required):

CREATE TABLE Foo (x CHAR(1));
CREATE TABLE Bar (x CHAR(1), y CHAR(1));

INSERT INTO Foo VALUES ('a');
INSERT INTO Foo VALUES ('b');

INSERT INTO Bar VALUES ('a', NULL);
INSERT INTO Bar VALUES ('b', '1');

SELECT x, y
FROM Foo AS F
CROSS APPLY (SELECT y FROM Bar AS B1 WHERE B1.x = F.x
UNION
SELECT y FROM Bar AS B2 WHERE B2.x = F.x) AS B
WHERE y IS NOT NULL;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Yeah, that's it !
But I'm under SQL Server 2000.... I think I'll keep the first
solution.

S. Rielau : the problem is the "WHERE B1.x=F.x".
If I write SELECT X FROM F, (SELECT ... FROM B1 WHERE B1.x=F.x) AS F1
SQL Server doesn't recognize F.x.

Hiho wrote:
You can put the WHERE on the outer SELECT.

Teh SQL Standard to make what you tried work is:
FROM X, LATERAL(SELECT ..... WHERE X.blah = ...)
It's called lateral correlation.
Incidently CROSS APPLY Seems to be a funny invention to do SQL Standard:
CROSS JOIN LATERAL(...).

Vendor lock in SQL dialect for no good reason... grmbl grmbl...
Where is Celko when he's needed. ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge Rielau wrote:
Assuming the WHERE clause can be pulled up.
E.g. if you repeated it in both branches of the UNION ALL (which I
forgot about)
If you can't pull the WHERE up the correlated join (APPLY/LATERAL) is
your only efficient way to wrote it.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Just for the sake of replicating what you were trying to do, in SQL Server
2000 you can use UDF:

CREATE FUNCTION dbo.B
( @x CHAR(1) )
RETURNS CHAR
AS
BEGIN
SET @x = (SELECT y FROM Bar WHERE x = @x
UNION
SELECT y FROM Bar WHERE x = @x)
RETURN @x
END

Then you can write the query like:

SELECT x, dbo.B(x)
FROM Foo
WHERE dbo.B(x) IS NOT NULL

HTH,

Plamen Ratchev
http://www.SQLStudio.com