Which join is better, inner join or equi join

[jagadeesh kumar]

While joining like the below,

select * from EMPLOYEE e , position p where 
e.empid=p.empid
 
select  * from EMPLOYEE e inner join position p where on e.empid=p.empid
 

performance wise, which one is better, and why?

I didn't really understand the difference, some are saying 1st case is not a proper join. is that true?
Why it is not good.

[Atli]

Hey.

jagadeesh kumar: I didn't really understand the difference, some are saying 1st case is not a proper join. is that true?

Not it's not true. It is a proper join, just a very "wide" join.

When you specify a list of table names, e.g: FROM tbl1, tbl2, tbl3, it is the equivelant of using a JOIN without an ON clause. - The result is the Cartesian product of the tables (that is: each row in all tables is joined with every row in all the other tables), which in most cases will result in a rather huge result set. The WHERE clause is then applied to that massive result set to get you your results.

Specifying the ON clause will cause MySQL to choose the rows for the joined set more carefully, resulting in a smaller result. The filters are applied before the joined set is compiled, which means the massive result set is never created.

I do not know exact details about the speed gains -- MySQL may well employ methods to reduce the loss of the Cartesian result set -- but it is generally advisable to use an ON clause.

Just to clarify, this query:

SELECT * FROM tbl1, tbl2
WHERE tbl1.id = tbl2.id
 

is exactly equivalent to this query:

SELECT * FROM tbl1 JOIN tbl2
WHERE tbl1.id = tbl2.id
 

And both of those should rather be written like this:

SELECT * FROM tbl1
JOIN tbl2 ON tbl1.id = tbl2.id
 

Also note that JOIN, INNER JOIN and CROSS JOIN are all exactly the same thing. The reason why they all exists, yet are all the same, is to comply with the standards and to provide compatibility with other SQL systems.

[ck9663]

If you have the necessary index, it should not matter that much. And for INNER JOIN, the syntax #1 should work perfectly. However, for other type of joins, you might need to adjust the compatibility of your server.

The syntax #2 is more based on ANSI Standards.

JOIN, INNER JOIN, CROSS JOIN exists because they all work differently. Although a JOIN and INNER JOIN are the same, it's only because INNER is the default join if nothing is specified. CROSS JOIN is designed to produce a produce a Cartesian Product, by default. Unless you specify a WHERE condition, which now becomes similar to INNER JOIN. A CROSS JOIN with a WHERE condition is a filtered Cartesian Product. An INNER JOIN is a filtered result even before it goes to the Cartesian Product. Using an INNER JOIN is also safer than CROSS JOIN if the intention is to do an INNER JOIN. This is because, a missing condition in INNER JOIN will not work, while a missing condition in CROSS JOIN will work but will produce unintended results...

Here's a good article I found...

Happy Coding!!!

~~ CK

[Atli]

ck9663: JOIN, INNER JOIN, CROSS JOIN exists because they all work differently.

This.

Ignore what I said earlier. I had MySQL in mind when I wrote that. (I could have sworn this was in the MySQL forum when I first read it! :P)