Search code examples
sqljoinduplicatesteradatavolatile

Join produces duplicated key (column) in Teradata SQL

In Teradata SQL I have two volatile tables that I would like to join. However, if I join them on colA, they both show up in the output. I just want this colA once in the output, not twice.

So I have:

Table1 with colA, colB, colC
Table2 with colA, colD, colE

My query:

SELECT * FROM Table1
JOIN Table2 ON Table1.colA = Table2.colA

gives me: colA, colB, colC, colA, colD, colE

while I would like to have: colA, colB, colC, colD, colE

Anyone knows how to solve this?

Solution

  • The best practice is to explicitly choose the columns you want -- with qualified names:

    SELECT t1.A, t1.B, t1.C, t2.D, t2.E
FROM Table1 t1 JOIN
     Table2 t2
     ON t1.colA = t2.colA;

    The ANSI syntax has the USING clause which does allow you to do:

    SELECT *
FROM Table1 t1 JOIN
     Table2 t2
     USING (colA);

    I don't believe Teradata supports this clause.