SQLServer Writing union in one statement
I have a request like below:
SELECT * FROM [PMDB_DEV].[dbo].[TRSRCFIN]
WHERE proj_id = 167592
AND taskrsrc_id NOT IN
(
SELECT taskrsrc_id FROM [PMDB_ARC].[dbo].[TRSRCFIN_TEST]
WHERE proj_id = 167592
)
UNION
SELECT * FROM [PMDB_DEV].[dbo].[TRSRCFIN]
WHERE proj_id = 167592
AND fin_dates_id NOT IN
(
SELECT fin_dates_id FROM [PMDB_ARC].[dbo].[TRSRCFIN_TEST]
WHERE proj_id = 167592
)
Basically the query returns all rows where either taskrsrc_id or fin_dates_id should not be present in the subquery data.
Can I do this without using UNION?
Thanks, Mahesh
+3
source to share
4 answers
I believe it would do:
SELECT *
FROM [PMDB_DEV].[dbo].[TRSRCFIN]
WHERE proj_id = 167592
and not exists (select null from [PMDB_ARC].[dbo].[TRSRCFIN_TEST]
WHERE proj_id = TRSRCFIN.proj_id
and (TRSRCFIN_TEST.taskrsrc_id = TRSRCFIN.taskrsrc_id
or TRSRCFIN_TEST.fin_dates_id = TRSRCFIN.fin_dates_id)
)
0
source to share
Perhaps something like this:
SELECT
*
FROM
[PMDB_DEV].[dbo].[TRSRCFIN]
WHERE
proj_id =167592
AND NOT EXISTS
(
SELECT
NULL
FROM
[PMDB_ARC].[dbo].[TRSRCFIN_TEST]
WHERE
[PMDB_ARC].[dbo].[TRSRCFIN_TEST].proj_id = [PMDB_DEV].[dbo].[TRSRCFIN].proj_id
AND
(
[PMDB_ARC].[dbo].[TRSRCFIN_TEST].taskrsrc_id= [PMDB_DEV].[dbo].[TRSRCFIN].taskrsrc_id
OR [PMDB_ARC].[dbo].[TRSRCFIN_TEST].fin_dates_id= [PMDB_DEV].[dbo].[TRSRCFIN].fin_dates_id
)
)
0
source to share
why?
if you have the correct indices (one on proj_id + taskrsrc_id and one on proj_id + fin_dates_id), most likely this UNION query will use both indices and be faster than a query that does not use UNION (and cannot use both indices). I would be more worried about optimization *
and NOT IN
.
0
source to share