rewrite a sql query

Database

rewrite a sql query

Hello, let's say we have this query.

SELECT    

    A.___

,   B.___

,   B.__

  FROM table_1 A

  INNER JOIN  table_2 B

                 ON  B.clm3 ='xxxx'

                 AND (A.clm1=B.clm1     OR    B.clm1 = 'xxxx' )

How can we rewrite it by replacing the or operator?

Because TD makes a horible plan.

Is it possible with a left Join.

Thank you very much for your time.

4 REPLIES
N/A

Re: rewrite a sql query

It's a horrible plan, because it's a horrible query :-)

Your query asks for a kind of Cross Join, there's no way to avoid the product join using a Left Join.

Usually an ORed join condition is replaced by UNION ALL, but your query is really strange, could you please elaborate what you actually want to achieve?

Dieter

Re: rewrite a sql query

Hi,

thank you very much for your help....and your time...

Unfortuantely, some developers implement this thing and now everybody is running.

This query is running for almost 3 hours , and the tables are about some hundrend thousand rows.

I make a first approach like this , and it takes about 9 minutes,but i am not very sure if it is correct....

  AND (

     A.clm1=B.clm1

      or

      (  B.clm1 in  ( case when B.Clm1  is null then null else '*' end)

Thanks very much,

Re: rewrite a sql query

I could not understand the purpose of this query? Can you please explain what is the data that you are trying retrieve?

Re: rewrite a sql query

Hello,

To be honest , I am not the one who wrote this strange query , they just gave me to see if i can tune it.

I was informed that it has been produced by a tool .  I am still trying to understund their business logic.

Where I conclude,was that even a union is not helping,just this syntax (according always to the kind of data they have)

AND A.clm1=CASE WHEN B.clm1='*' THEN A.clm1 ELSE B.clm1 END