rewrite a sql query

Database
Enthusiast

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
Senior Apprentice

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

Enthusiast

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,

Enthusiast

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?

Enthusiast

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