Join on multiple columns 1

[dswitzer]

-- sample data
CREATE TABLE #A ( refNum int,idA1 varchar(3),idA2 varchar(3))
CREATE TABLE #B ( refNum int,idB1 varchar(3),idB2 varchar(3),idB3 varchar(3),idB4 varchar(3),idB5 varchar(3))

insert into #A select 1,'001','999'
insert into #A select 2,'002','999'
insert into #A select 3,'003','999'
insert into #A select 4,'999','004'

insert into #B select 1,'000','000','000','000','000'
insert into #B select 2,'000','000','000','000','001'
insert into #B select 3,'000','002','000','000','000'
insert into #B select 4,'000','000','004','000','000'

-- retrieval option A
SELECT *
FROM #A left join #B
on #A.idA1=#B.idB1
or #A.idA1=#B.idB2
or #A.idA1=#B.idB3
or #A.idA1=#B.idB4
or #A.idA1=#B.idB5
or #A.idA2=#B.idB1
or #A.idA2=#B.idB2
or #A.idA2=#B.idB3
or #A.idA2=#B.idB4
or #A.idA2=#B.idB5

-- retrieval option B
SELECT *
FROM #A left join #B
on #A.idA1 in (#B.idB1,#B.idB2,#B.idB3,#B.idB4,#B.idB5)
or #A.idA2 in (#B.idB1,#B.idB2,#B.idB3,#B.idB4,#B.idB5)

-- Results are what I want
-- Which is more efficient? Retrieval A or B?
-- Is there a better way?

 

[gmmastros]

Both of your queries are exactly the same from the query optimizer's perspective. To see this yourself, load the code you have in a new query window within SQL Server Management Studio. Press CTRL-M (nothing will appear to happen). Then press F5 (or click the execute button). CTRL-M will turn on the actual execution plan. After running the query, you will see an "Execution Plan" tab. As you can see, the cost for both queries are the same, and the break down for each will also be the same.

Bascially, behind the scenes, SQL Server is processing the queries in exactly the same way, so it does not matter.

Personally, I don't like either query. It's "ugly" and probably won't execute efficiently in either case, but since your data is not normalized, this is the kind of mess you have to deal with.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom