Inner join on multiple Columns best way?(It's a bit urgent)

Database

Inner join on multiple Columns best way?(It's a bit urgent)

I have TABLE A and TABLE B

I'm trying to select reg_no.... inner join ON A.id=B.id1,Id2,Id3......Id20. And i have 3 conditions in Where clause.
(A.Id could be equal to any of 20 colums, some times 1 or 4 or 5 of 20 columns too)

I'm writing as
ON (A.Id=B.Id1
OR A.Id=B.Id2
.............
OR A.Id=B.Id20)

I have 7 millions of rows, but after where clause, rows are not more than few thousands can any one plz suggest me whether i'm doing right or any other simple way for my problem.

sample query:

SELECT reg_no
FROM A
INNER JOIN B
ON (A.Id=B.Id1
OR A.Id=B.Id2
.............
OR A.Id=B.Id20)

WHERE
XXXXXX AND
YYYYYY AND
ZZZZZZ;

5 REPLIES
N/A

Re: Inner join on multiple Columns best way?(It's a bit urgent)

Always avoid OR conditions in joins.

Use a union:

SELECT reg_no
FROM A
INNER JOIN B
ON A.Id=B.Id1
WHERE
XXXXXX AND
YYYYYY AND
ZZZZZZ

UNION

SELECT reg_no
FROM A
INNER JOIN B
ON A.Id=B.Id2
WHERE
XXXXXX AND
YYYYYY AND
ZZZZZZ

UNION
.............
ON A.Id=B.Id20)

WHERE
XXXXXX AND
YYYYYY AND
ZZZZZZ;

And make sure you have stats on your join columns.
The only way it can join in your original query is to create a spool of table B on every VPROC and do a product join.

Re: Inner join on multiple Columns best way?(It's a bit urgent)

let me try. Thanks for your help.
N/A

Re: Inner join on multiple Columns best way?(It's a bit urgent)

SELECT ...
FROM TABLE A INNER JOIN

(SELECT COL1 AS JOIN_COL, B.*
FROM TABLEB B
UNION
SEL COL2 AS JOIN COL, B.*
FROM TABLEB B
.....
SEL COL20, B.*
FROM TABLEB B
)
ON A.COL1 = B.JOINCOL
N/A

Re: Inner join on multiple Columns best way?(It's a bit urgent)

Benefit of approach I adopted is to have ONE redistribute step and one join step instead of potential 20 redistribution steps and 20 joins,.

and you can compare the performance by testing both . i believe mine approach here works better.
gkk
N/A

Re: Inner join on multiple Columns best way?(It's a bit urgent)

Hai Anil ,
i feel emilwu approach is good . But, Can you tell me where in real we need to join on multiple columns ? For example in your project where you got this situation ?