Slow Join Query - Can it be improved? 1

[forumposters]

Here's my SQL:

SELECT *
FROM A, B
WHERE A.address IN ('X')
OR B.address IN ('X')
ORDER BY B.address, A.address,B.[date], A.[date]

Is there anything that sticks out here that could be improved so that this query takes less time to run?

 

[r937]

well, something sticks out a lot, but by its absence -- the join condition

look up cross join and cartesian product

r937.com | rudy.ca

 

[forumposters]

Thanks for bringing that to my attention. So, I actually don't want to join the two tables and I titled this thread wrong, sorry about that. I just want to get records from both of the tables. The SQL statement produces the results I want, but it takes a long time. Is there anyway to rewrite that SQL statement to do the same thing, but to run faster?

 

[r937]

maybe this is what you want?

SELECT address
     , [date]
     , [i]othercolumns[/i]
     , 'A' AS source
  FROM A
 WHERE address IN ('X')
UNION ALL
SELECT address
     , [date]
     , [i]othercolumns[/i]
     , 'B' AS source
  FROM B
 WHERE address IN ('X')
ORDER 
    BY address
     , [date]

r937.com | rudy.ca

 

[forumposters]

That's exactly what I needed. Thanks!

One more question:
If I want to also select a field from from table A that does not exist in table B, how can I do that? Is there some SQL code that will add this field to table B for this query and add a default value of my choice? So, I would have something like this(psuedocode):

SELECT fieldOnlyinTableA, address
..
..
FROM A

UNION ALL 

SELECT (some SQL code here that adds the field fieldOnlyinTableA so that an error doesn't occur  )
...
...

Or, is there another way to do this? I suppose that I can add the fieldOnlyinTableA to the B table and set a default value if I have too, but I'd rather not have to do that.

 

[r937]

i'm not sure i understand your question...

perhaps you mean this:

SELECT tableA.foo
     , COALESCE(tableB.bar,'default if no B for this A') AS bar
  FROM ...

r937.com | rudy.ca

 

[RyanEK]

I suggest you don't use the IN condition. It may not substantially affect your query but it is generally a performance killer.

 

[forumposters]

Let me try and explain the problem in another way. The UNION ALL only works if the two tables have the same columns. If one table has an extra column and you want to select it, then an error occurs. I want to have that extra column in table A in the rows returned from table A.

 

[r937]

when you have an extra column in one table that you want returned, but no corresponding column in the other...

... use NULL

SELECT x, y
FROM table1
UNION ALL
SELECT z, NULL
FROM table2



r937.com | rudy.ca