0

Can following Query be optimised for Teradata?

We need all records from small table A, plus all corresponding records from large table B, that match on a nonunique key

Or, in other words: everything except all from B that has no match in A.

Maybe something with a JOIN? Or a Subselect that is a non-correlated Query, does that also apply to Teradata?

SELECT a.nonunique
       , a.colX
  FROM small_tab a
 UNION ALL
SELECT b.nonunique
       , b.colY
  FROM large_tab b
 WHERE EXISTS (
                SELECT 1
                  FROM small_tab a
                 WHERE a.nonuniqe = b.nonunique
              );

Thanks for the help!

=========UPDATE====
based on quanos answer in this MySQL question, would following statement with a noncorrelated subquery be faster also in Teradata?

SELECT a.nonunique
       , a.colX
  FROM small_tab a
 UNION ALL
SELECT b.nonunique
       , b.colY
  FROM large_tab b
 WHERE b.nonunique IN
 (
    SELECT DISTINCT nonunique
      FROM small_tab
  GROUP BY nonunique
)

I cannot test in Teradata currently, only have an Oracle instance at home..

Community
  • 1
  • 1
Rusty75
  • 477
  • 7
  • 19
  • Do you really need `UNION`? Distinct processing is a huge overhead, `UNION ALL` would be faster. – dnoeth Apr 10 '17 at 22:45
  • thanks, I oversaw that. It comes from another select that I first used, which I think is less efficient (involving a join that returned duplicates). Updated.. – Rusty75 Apr 10 '17 at 22:52

1 Answers1

0

I'm not sure whether it is a typo, but you have a redundant select query after WHERE clause. Also, you will have to use the same column name in SELECT query that is being used in WHERE Claue. Below query works fine in Teradata. 

SELECT a.nonunique, a.colX
FROM small_tab a
UNION ALL
SELECT b.nonunique, b.colY
FROM large_tab b
WHERE b.id IN(
    SELECT **id**
    FROM small_tab)

Hope it helps. if any query on above query, please let me know.

ManishPrajapati
  • 459
  • 1
  • 5
  • 16
  • Hi. Not a typo but intentional - the additional subselect forces the select inside to be a noncorrelated subquery, please see the mentioned link. This works in MySQL and I was wondering if it is also more performant in Teradata.. – Rusty75 Apr 11 '17 at 06:55
  • actually I made another typo, I didnt want to match on id, but on nonunique.. hence I added DISTINCT – Rusty75 Apr 11 '17 at 10:33
  • ok another comment - I am now in doubt as to whether we really need this additional subselect as mentioned in that post... I see your point, it seems a bit useless to me...? Maybe it is just a MySQL flaw. Removed it.. – Rusty75 Apr 11 '17 at 10:36
  • As per the requirement you have mentioned i dont think it is necessary..but if this is not giving output as required then could you pls update the requirement. – ManishPrajapati Apr 11 '17 at 10:46