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

SELECT *
FROM Loan
CROSS JOIN Borrower

No inconsistency.

Answer 2

This way of expressing inner joins:

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

is not recommended for almost 20 years. It was kept because it is simply a valid expression that happens to convey an inner join. I would concentrate in using the version closer to the current standard, minimizing the chances for misunderstanding and flat out errors.

Answer 3

Oracle also supports the natural join syntax, which joins two tables on the basis of shared column name(s). This would work in your case because both tables have a column called LOAN_NUMBER.

SELECT *
FROM Loan
NATURAL JOIN Borrower

Now, your same argument could be made in this case, that the use of the keyword natural is strictly unnecessary. But if we follow the logic we end up with a situation in which this statement could be either a cross join or a natural join, depending on the column names:

SELECT *
FROM Loan
JOIN Borrower

This is clearly undesirable, if only because renaming LOAN.LOAN_NUMBER to LOAN_ID would change the result set.

So, there's your answer: disambiguation.

Answer 4

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.