a substitute for UNION?

[bbla32@op.pl]

I'd like to change query:

SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)

to one SELECT like this

SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)

but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:

SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2

or (say DM has columns A, B, C):

SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, C

[David Portas]

Re: a substitute for UNION?

On 19 Feb, 20:46, bbl...@op.pl wrote:

I'd like to change query:
>
SELECT DM.*, 'condition1', NULL FROM DM
WHERE (condition1)
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE (condition2)
>
to one SELECT like this
>
SELECT DM.*, WasCondition1, WasCondition2 FROM DM
WHERE (condition1) or (condition2)
>
but how to fill in the WasConditionX column?
The UNION version was bad because in case a row fulfilled 2
conditions, it was repeated instead of joining them, such like this:
>
SELECT DM.*, 'condition1', 'condition2' FROM DM
WHERE condition1 AND condition2
UNION
SELECT DM.*, 'condition1', NULL FROM DM
WHERE condition1 AND NOT condition2
UNION
SELECT DM.*, NULL, 'condition2' FROM DM
WHERE NOT condition1 AND condition2
>
or (say DM has columns A, B, C):
>
SELECT A, B, C, SUM(cond1), SUM(cond2) FROM
(
SELECT DM.*, 1 AS cond1, 0 AS cond2 FROM DM
WHERE condition1
UNION
SELECT DM.*, 0 AS cond1, 1 AS cond2 FROM DM
WHERE condition2
) AS DM
GROUP BY A, B, C

Use a CASE expression. To adapt your second example:

SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;

Always avoid using SELECT *.


--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[bbla32@op.pl]

Re: a substitute for UNION?

Use a CASE expression. To adapt your second example:

>
SELECT col1, col2, col3,
CASE WHEN (condition1) THEN 'Y' ELSE 'N' END AS WasCondition1,
CASE WHEN (condition2) THEN 'Y' ELSE 'N' END AS WasCondition2
WHERE (condition1) OR (condition2) ;

Well, then each condition will be checked twice -- performance
decreases (the conditions use subqueries)?

Always avoid using SELECT *.

Why?

[bbla32@op.pl]

Re: a substitute for UNION?

I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):

DECLARE @pp INT
SET @pp = 7


SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]

FROM (

SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPers onID = @pp

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepa rtmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelSt ationsResponsib ility PS
WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)

UNION

SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepa rtmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionN r IN
(SELECT I.InstructionNr FROM WSDMS..Instruct ionsNewest I
WHERE I.PUID = @pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
)

)

GROUP BY [ID]

[Tibor Karaszi]

Re: a substitute for UNION?

You are missing naming the derived table. Note the AS tbl at the end of the inner query:

SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

--
Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://sqlblog.com/blogs/tibor_karaszi

<bbla32@op.plwr ote in message news:1171920105 .335960.271700@ m58g2000cwm.goo glegroups.com.. .

I'll show you my query which doesn't want to compile for some reason
(Incorrect syntax near the keyword GROUP):
>
DECLARE @pp INT
SET @pp = 7
>
>
SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
FROM (
>
SELECT 1 AS Sent, 0 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.SenderID = @pp
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverPers onID = @pp
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepa rtmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.StationID IN
(SELECT PS.StationID FROM CF..PersonnelSt ationsResponsib ility PS
WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)
>
UNION
>
SELECT 0 AS Sent, 1 AS Received,
DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
AND DM.ReceiverDepa rtmentID IN
(SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
AND DM.InstructionN r IN
(SELECT I.InstructionNr FROM WSDMS..Instruct ionsNewest I
WHERE I.PUID = @pp AND I.PUID IN
(SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
)
>
)
>
GROUP BY [ID]
>

[bbla32@op.pl]

Re: a substitute for UNION?

On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.n o.email_kara... @hotmail.nomail .comwrote:

You are missing naming the derived table. Note the AS tbl at the end of the inner query:
>
SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

Thanks!

Can I write a faster query?

[Gert-Jan Strik]

Re: a substitute for UNION?

bbla32@op.pl wrote:

>
On Feb 19, 10:31 pm, "Tibor Karaszi"
<tibor_please.n o.email_kara... @hotmail.nomail .comwrote:

You are missing naming the derived table. Note the AS tbl at the end of the inner query:

SELECT ...
FROM
(
SELECT ... FROM...
) AS tbl
WHERE ...
GROUP BY...

>
Thanks!
>
Can I write a faster query?

A few tips:
- Only select the columns that you need in the derived table. IOW,
change "DM.*" to "DM.ID"

- Make sure you have proper indexes in place. For example on
DocumentMessage s(SenderID,Obso leteDate) and on
DocumentMessage s(ReceiverPerso nID,ObsoleteDat e)

- Remove unnecessary IN clauses

Gert-Jan

[Roy Harvey]

Re: a substitute for UNION?

Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.

SELECT SUM(Sent) AS Sent,
SUM(Received) AS Received,
[ID]
FROM (SELECT CASE WHEN DM.SenderID = @pp
THEN 1
ELSE 0
END AS Sent,
CASE WHEN DM.ReceiverPers onID = @pp
THEN 1
WHEN DM.ReceiverDepa rtmentID NOT IN
(SELECT PD.DepartmentID
FROM CF..PersonnelDe partment PD
WHERE PD.PersonID = @pp
AND PD.ObsoleteDate IS NULL)
THEN 0
WHEN DM.StationID IN
(SELECT PS.StationID
FROM CF..PersonnelSt ationsResponsib ility PS
WHERE PS.PersonID = @pp
AND PS.ObsoleteDate IS NULL)
THEN 1
WHEN DM.InstructionN r IN
(SELECT I.InstructionNr
FROM WSDMS..Instruct ionsNewest I
WHERE I.PUID = @pp
AND I.PUID IN
(SELECT PU.ID
FROM WSDMS..PUs PU))
THEN 1
ELSE 0
END AS Received,
DM.*
FROM WSDMS..Document Messages DM
WHERE DM.ObsoleteDate IS NULL) as X
GROUP BY [ID]

I left all the IN tests as IN tests, (though one was reversed to a NOT
IN). Another change that could be worth trying is to rewrite each one
as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.

Roy Harvey
Beacon Falls, CT

On 19 Feb 2007 13:21:45 -0800, bbla32@op.pl wrote:

>I'll show you my query which doesn't want to compile for some reason
>(Incorrect syntax near the keyword GROUP):
>
>DECLARE @pp INT
>SET @pp = 7
>
>
>SELECT SUM(Sent) AS Sent, SUM(Received) AS Received, [ID]
>
>FROM (
>
>SELECT 1 AS Sent, 0 AS Received,
> DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.SenderID = @pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverPers onID = @pp
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepa rtmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
>AND DM.StationID IN
> (SELECT PS.StationID FROM CF..PersonnelSt ationsResponsib ility PS
> WHERE PS.PersonID = @pp AND PS.ObsoleteDate IS NULL)
>
>UNION
>
>SELECT 0 AS Sent, 1 AS Received,
> DM.* FROM WSDMS..Document Messages DM WHERE DM.ObsoleteDate IS NULL
>AND DM.ReceiverDepa rtmentID IN
> (SELECT PD.DepartmentID FROM CF..PersonnelDe partment PD
> WHERE PD.PersonID = @pp AND PD.ObsoleteDate IS NULL)
>AND DM.InstructionN r IN
> (SELECT I.InstructionNr FROM WSDMS..Instruct ionsNewest I
> WHERE I.PUID = @pp AND I.PUID IN
> (SELECT PU.ID FROM WSDMS..PUs PU WHERE PU.PersonID = @pp)
> )
>
>)
>
>GROUP BY [ID]

[bbla32@op.pl]

Re: a substitute for UNION?

On Feb 19, 11:33 pm, Roy Harvey <roy_har...@sne t.netwrote:

Assuming I didn't make a mistake - the code is untested of course -
the following might perform slightly better. Or not, a lot depends on
the indexing and actual data.

Great example!

as an EXISTS test. In any case the use of subqueries in the CASE
should limit the number of times the subqueries are executed, which is
where any performance gain will come from.

Well, wouldn't it execute slower since the subquery is within CASE?
I have indices only on ID columns for all tables.

[Roy Harvey]

Re: a substitute for UNION?

On 19 Feb 2007 15:01:46 -0800, bbla32@op.pl wrote:

>On Feb 19, 11:33 pm, Roy Harvey <roy_har...@sne t.netwrote:

>as an EXISTS test. In any case the use of subqueries in the CASE
>should limit the number of times the subqueries are executed, which is
>where any performance gain will come from.

>
>Well, wouldn't it execute slower since the subquery is within CASE?

No reason why that should be an issue.

The best way to make the subqueries faster is to run them less. If
the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.

Roy Harvey
Beacon Falls, CT

[bbla32@op.pl]

Re: a substitute for UNION?

The best way to make the subqueries faster is to run them less. If

the first WHEN is satisfied the subqueries are not run at all. If the
first subquery matches, the second subquery is not run at all. In the
original query every subquery was run against every row in the table -
and every row was processed as many times as there were UNIONed
SELECTs. So the idea behind moving them into the CASE is to run them
once per incoming row, and then as few of them as is required.

That makes sense. I have a problem with your query though: in the
construct like below

SELECT CASE
WHEN DM.ReceiverDepa rtmentID NOT IN (1)
THEN 0
ELSE 1
END

when DM.ReceiverDepa rtmentID is null, it returns 1 instead of 0?

[bbla32@op.pl]

Re: a substitute for UNION?

when DM.ReceiverDepa rtmentID is null, it returns 1 instead of 0?


I changed to

WHEN DM.ReceiverDepa rtmentID IS NULL OR DM.ReceiverDepa rtmentID NOT
IN ...

and

WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...

I suppose it should be ok now.

[Roy Harvey]

Re: a substitute for UNION?

On 19 Feb 2007 15:53:06 -0800, bbla32@op.pl wrote:

>when DM.ReceiverDepa rtmentID is null, it returns 1 instead of 0?

>
>
>I changed to
>
WHEN DM.ReceiverDepa rtmentID IS NULL OR DM.ReceiverDepa rtmentID NOT
>IN ...
>
>and
>
WHEN DM.StationID IS NOT NULL AND DM.StationID IN ...
>
>I suppose it should be ok now.

That is the problem with not haveing the real table definitions and
test data to work with. Your correction is fine.

Roy Harvey
Beacon Falls, CT