CODEWITHSUNDEEP
sqloracle-databasesyntaxinner-join
Will
Will

Reputation: 2978

Oracle SQL Syntax: Inner Join

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

Upvotes: 3

Views: 13338

Answers (4)

APC
APC

Reputation: 146239

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.

Upvotes: 7

Conrad Frix
Conrad Frix

Reputation: 52645

You're missing ON

Like 

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

Upvotes: 9

Olaf
Olaf

Reputation: 6289

Yes, you have to specify join condition:

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

Upvotes: 1

cmutt78
cmutt78

Reputation: 859

You will need to add an ON clause

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

Upvotes: 2

Related Questions