0

For example select * from a inner join b on a.i=b.i ; is definitely inner join.

But can you let me know whether the below statements belongs to cross join or inner join ?

select * 
from a ,b 
where a.i=b.i;

select * 
from a ,b 
on a.i=b.i;

Thanks,
Kasi.

0

It's a matter of definition.

select * 
from a ,b

is a CROSS JOIN.

select * 
from a ,b 
where a.i=b.i;

is equivalent with both:

select * 
from a
cross join b 
where a.i=b.i;

and:

select * 
from a
inner join b 
    on a.i=b.i;

so in a sense, it is both an INNER JOIN and a CROSS JOIN with a filtering WHERE clause.

  • inner join performance is greater than cross join right. So select * from a inner join b on a.i=b.i; performance is better than performance of select * from a,b where a.i=b.i; Am i correct? – kasi Feb 21 at 10:59
  • No, I guess most DBMS will treat then equally performance wise. The benefit of using JOIN instead of "," is clarity and maintainability. As soon as a query get more than a couple of joins - especially if join condition contains several columns - it is very easy to miss something if everything is located in the WHERE clause. – Lennart Feb 21 at 14:39
0

The first query is the same as an INNER JOIN, the second query is not valid because it is using ON without a JOIN statement.

From a similar question:

The comma operator is equivalent to an [INNER] JOIN operator. The comma is the older style join operator. The JOIN keyword was added later, and is favored because it also allows for OUTER join operations

Source

Testing db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.