All Forums Database
anilk.zee 4 posts Joined 12/10
21 Feb 2011
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;

Jimm 298 posts Joined 09/07
21 Feb 2011

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.

anilk.zee 4 posts Joined 12/10
22 Feb 2011

let me try. Thanks for your help.

emilwu 72 posts Joined 12/07
22 Feb 2011

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

emilwu 72 posts Joined 12/07
22 Feb 2011

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 22 posts Joined 11/10
27 Feb 2011

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 ?

You must sign in to leave a comment.