Mixing ON and USING within one join

Question

Is there a way to do the following in Oracle:

SELECT *
FROM   Tbl1
JOIN   Tbl2 
USING  (col1)
AND ON Tbl1.col2 = Tbl2.col3

Answer 1

In standard SQL, you can use either USING or ON but not both. If you could use both, you'd omit the AND from your example.

---

PMV commented (more accurately, asked - see also the poster's answer):

My problem is I've got a bunch of tables, all with USING joins. If I try to add a table with different key names I get an ambiguous column error. Is there a graceful way to do this without converting all my joins to ON join?

Well, the best way to limit the damage imposed by the sadly misnamed columns is probably (and roughly - I've not debugged the SQL below):

SELECT *
    FROM (SELECT *
             FROM Tbl1
                  JOIN Tbl2 USING (col1, col2, col3, col4)
                  JOIN Tbl3 USING (col2, col4, col6, col23)
                  JOIN Tbl4 USING (col2, col8, col3, col23)
          ) AS SystematicNaming
          JOIN Tbl5
              ON  SystematicNaming.Col1 = Tbl5.Col1
              AND SystematicNaming.ColA = Tbl5.PoorlyNamedColA
              AND SystematicNaming.ColB = Tbl5.PoorlyNamedColB

This preserves the convenient and systematic USING notation for as long as possible, while managing the join with Tbl5 in the way that it has to be managed given the non-uniform column names required to join with Tbl5.

I'm not sure that's the best query layout, though I think it is reasonable.

Answer 2

SELECT * FROM TBL1 JOIN TBL2 ON TBL1.COL1 = TBL2.COL1 AND TBL1.COL2 = TBL2.COL3

Regards K

Answer 3

The query I have is more like:

SELECT *
FROM   Tbl1
JOIN   Tbl2 USING (col1, col2, col3, col4)
JOIN   Tbl3 USING (col2, col4, col6, col23)
JOIN   Tbl4 USING (col2, col8, col3, col23)
JOIN   Tbl5 USING (col1)
  ON   Tbl1.colA = Tbl5.PoorlyNamedColA
 AND   Tbl2.colB = Tbl5.PoorlyNamedColB

I'm trying to avoid changing all of the USING joins to ON joins for readability sake. Can I do this, or do I have to leave the problem with the next developer who looks at the code?

Thanks in advance for the help.

Answer 4

SELECT *
FROM   Tbl1
       INNER JOIN Tbl2
ON     Tbl1.col1 = Tbl2.col1
   AND Tbl1.col2 = Tbl2.col3