UNION Vs Inner join

Connectivity
N/A

UNION Vs Inner join

When I run #1 query it take several hrs or more than 1 day.
Records are new to 2.7 TB.

Query #1

SELECT DISTINCT
listing_ID,new_id
FROM tbllisting inner join rawdb.dbo.mapphysician on
new_id = rawdb.dbo.mapphysician.persistentrecordid
and ypheading1 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
or ypheading2 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
--or ypheading3 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')
or ypheading4 in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18')

When I run 2nd Query it takes less Hrs than Query #1.

Query #2
SELECT DISTINCT dbo.tbllisting.listing_id, dbo.tbllisting.new_id
FROM dbo.tbllisting INNER JOIN
rawdb.dbo.MapPhysician ON dbo.tbllisting.new_id = rawdb.dbo.MapPhysician.PERSISTENTRECORDID AND
rawdb.dbo.MapPhysician.YPHEADING1 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
UNION
SELECT DISTINCT tbllisting_1.listing_id, tbllisting_1.new_id
FROM dbo.tbllisting AS tbllisting_1 INNER JOIN
rawdb.dbo.MapPhysician AS MapPhysician_1 ON tbllisting_1.new_id = MapPhysician_1.PERSISTENTRECORDID AND
MapPhysician_1.YPHEADING2 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
union
SELECT DISTINCT dbo.tbllisting.listing_id, dbo.tbllisting.new_id
FROM dbo.tbllisting INNER JOIN
rawdb.dbo.MapPhysician ON dbo.tbllisting.new_id = rawdb.dbo.MapPhysician.PERSISTENTRECORDID AND
rawdb.dbo.MapPhysician.YPHEADING3 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')
UNION
SELECT DISTINCT tbllisting_1.listing_id, tbllisting_1.new_id
FROM dbo.tbllisting AS tbllisting_1 INNER JOIN
rawdb.dbo.MapPhysician AS MapPhysician_1 ON tbllisting_1.new_id = MapPhysician_1.PERSISTENTRECORDID AND
MapPhysician_1.YPHEADING4 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18')

i am little bit confuse at this time thats why asking with friends.
1 REPLY
SN
N/A

Re: UNION Vs Inner join

hi,

here's my suggestion...

-> Use 'GROUP BY' instead of DISTINCT
-> Use a volatile table to populate with records qualifying the conditions on ypheading1,ypheading2,ypheading3 and ypheading4.
Now, join the other table with the volatile table on the join column(s).

thx,