CODEWITHSUNDEEP
sqloracle-database
Parris Varney
Parris Varney

Reputation: 11488

Mixing ON and USING within one join

Is there a way to do the following in Oracle:

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

Upvotes: 1

Views: 149

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

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.

Upvotes: 1

achinda99
achinda99

Reputation: 5078

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

Upvotes: 0

Khb
Khb

Reputation: 1433

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

Regards K

Upvotes: 1

Related Questions