Oracle SQL Syntax: Inner Join

Question

I don't have access to an Oracle Database right now, so I'm posting my question here:

Is the following statement valid Oracle SQL Syntax?

SELECT a1
FROM t1 INNER JOIN t2

I'm particularly wondering whether we need to specify a join attribute for the inner join.

Best, Will

Please avoid natural join. As APC says below, "it is a bug waiting to happen." — Shannon Severance
– Shannon Severance, Commented May 11, 2011 at 17:58

chris · Accepted Answer · 2011-05-11 17:32:59Z

9

You're missing ON

Like

SELECT a1
FROM t1 INNER JOIN t2
ON t1.SomeID = t2.SomeID

edited May 11, 2011 at 17:32

chris

37.7k55 gold badges161 silver badges245 bronze badges

answered May 11, 2011 at 17:31

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

APC · Accepted Answer · 2011-05-11 17:40:14Z

So, this is the query you're thinking of....

SQL> select e.ename
  2         , d.dname
  3  from emp e inner join dept d
  4  /
from emp e inner join dept d
                           *
ERROR at line 3:
ORA-00905: missing keyword


SQL>

As we can see, it fails. The INNER JOIN syntax demands that we provide columns to join on ...

SQL> select e.ename
  2         , d.dname
  3  from emp e inner join dept d
  4      on ( d.deptno = e.deptno )
  5  /

ENAME      DNAME
---------- --------------
SCHNEIDER  ACCOUNTING
BOEHMER    ACCOUNTING
KISHORE    ACCOUNTING
ROBERTSON  RESEARCH
...
FEUERSTEIN HOUSEKEEPING
PODER      HOUSEKEEPING
TRICHLER   HOUSEKEEPING

21 rows selected.

SQL>

There is an alternative syntax, the NATURAL JOIN. This syntax will automatically join the two tables on the basis of all columns which share the same name.

SQL> select e.ename
  2         , d.dname
  3  from emp e natural join dept d
  4  /

ENAME      DNAME
---------- --------------
SCHNEIDER  ACCOUNTING
BOEHMER    ACCOUNTING
KISHORE    ACCOUNTING
ROBERTSON  RESEARCH
...
FEUERSTEIN HOUSEKEEPING
PODER      HOUSEKEEPING
TRICHLER   HOUSEKEEPING

21 rows selected.

SQL>

This is a neat trick but really shouldn't be relied upon in production code; it is a bug waiting to happen.

cmutt78 · Accepted Answer · 2011-05-11 17:31:52Z

2

You will need to add an ON clause

SELECT a1
FROM t1 INNER JOIN t2 on t1.a1=t2.a1

answered May 11, 2011 at 17:31

cmutt78

8591 gold badge9 silver badges18 bronze badges

Comments

Olaf · Accepted Answer · 2011-05-11 17:32:53Z

1

Yes, you have to specify join condition:

FROM t1 INNER JOIN t2 on t1.f = t2.f

answered May 11, 2011 at 17:32

Olaf

6,2991 gold badge21 silver badges38 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Syntax: Inner Join

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related