inner joins in oracle

Question

I was thinking about the syntax of inner joins in Oracle's SQL implementation and here is something that seems a bit inconsistent:

Let's say you have two relations loan(loan_number, branch_name, amount) and borrower(customer_name, loan_number). loan_number is the attribute common to both tables. Now, Oracle gives you two ways to express an inner join:

select * 
from loan, borrower 
where loan.loan_number = borrower.loan_number;

The above statement is equivalent to:

select * 
from loan 
     inner join borrower 
     on loan.loan_number = borrower.loan_number;

However, when expressing a cross join there is only one way to express it:

select * 
from loan, borrower;

The following statement is syntactically incorrect:

select * 
from loan 
     inner join borrower; 

This is invalid; Oracle expects the ON... part of the clause

Given that an inner join is just a cross join with a filter condition, do you guys think that this is an inconsistency in Oracle's SQL implementation? Am I missing something? I'd be interested in hearing some other opinions. Thanks.

As David pointed out in his answer the syntax is:

select * 
from loan cross join borrower;

Even though I was not aware of the above syntax I still think it's inconsistent. Having the cross join keyword in addition to allowing inner join without a join condition would be fine. A cross join is in fact an inner join without a join condition, why not express it as an inner join without the join condition?

Answer 1

I would agree that it is not consistent.

But I would argue that the Oracle implementation is a good thing:

• when you do a join, you almost always want to include a filter condition, therefore the ON part is mandatory.
• If you really, really don't want to have a filter condition (are you really sure?), you have to tell Oracle explicitly with CROSS JOIN sytax.

Makes a lot of sense to me not to be 100% consistent - it helps to avoid you mistakes.

Answer 2

SELECT *
FROM Loan
CROSS JOIN Borrower

No inconsistency.