create a sql join with more columns

Question

i want create a sql statemnt (in PL SQL Developer) with a join with comma seperated?

SELECT * FROM TABLE1 t1 JOIN TABLE2 t2 ON t1.tab_id, second_id = t2.tab_id, second_id;

I always get a ORA-00920 Exception. If i change it to two Rows:

t1.tab_id = t2.tab_id AND t1.second_id = t2.second_id;

Then i get rows.

Can some say me if i can use the first step with coma seperated columns?

Greetz

No you can't use the first step. Where in the documentation does it say the first step would work??? Have you read the documentation at all??? — Eric, Apr 27 '18 at 21:51
Not really, but i think it is possible because oracle have nice easter eggs :) — Artist_Styler_57, Apr 27 '18 at 22:07

Gordon Linoff · Accepted Answer · 2018-04-28T22:25:09.660

1

You need a valid condition:

SELECT *
FROM TABLE1 t1 JOIN
     TABLE2 t2
     ON t1.tab_id = t2.tab_id AND t1.second_id = t2.second_id;

I think Oracle will also let you do:

SELECT *
FROM TABLE1 t1 JOIN
     TABLE2 t2
     ON (t1.tab_id, t1.second_id) in ( (t2.tab_id, t2.second_id) );

Or even:

SELECT *
FROM TABLE1 t1 JOIN
     TABLE2 t2
     USING (tab_id, second_id);

This works because the JOIN keys have the same names in the two tables.

edited Apr 28 '18 at 22:25

answered Apr 27 '18 at 21:46

Gordon Linoff

Thank you for the fast reply !!! But if i have more joins: SELECT * FROM tab1 t1 JOIN tab2 t2 ON t1.rab_id = t2.tab_id JOIN tab3 t3 ON t2.second_id,t2.third_id = t3.second_id,t3.third_id... Can i use your solution? – Artist_Styler_57 Apr 27 '18 at 21:50
@Artist_Styler_57 . . . Each `ON`/`USING` clause is its own condition. Each can take any of these three forms. – Gordon Linoff Apr 27 '18 at 21:54
if i use using -> is working ! :) But if i use ON (t1.tab_id, t1.second_id) = .... -> I get ORA-00920 :( – Artist_Styler_57 Apr 27 '18 at 21:59
@Artist_Styler_57 . . . For some reason, Oracle supports tuples with `IN` but not `=`. It is a nuance that tend to forget. I updated the answer. – Gordon Linoff Apr 28 '18 at 22:25

1 Answers1