inner join vs normal query

Question

Possible Duplicate:
ANSI joins versus “where clause” joins

We can query two tables using below approaches right?

SELECT customer.name
FROM customer
INNER JOIN order
ON customer.cus_id=order.cus_id
ORDER BY customer.name


SELECT c.name
FROM customer c, order o
WHERE 
c.cus_id=o.cus_id
ORDER BY c.name

What is the advantage of using join over latter one?

They are both joins - the latter just uses implicit join notation. See: http://en.wikipedia.org/wiki/Join_(SQL)#Inner_join — nickhar, Nov 27 '12 at 17:09

score 4 · Answer 1 · answered Nov 27 '12 at 17:05

4

There is no difference in execution. Those two are the same queries just written differently.

answered Nov 27 '12 at 17:05

sufleR

2,865
17
31

score 2 · Answer 2 · answered Nov 27 '12 at 17:24

For those two exact queries there is no difference. The query planner will create the exact same query plan for them.

Generally speaking, the use of join is more flexible, apart from inner join you also have left outer join, right outer join and cross join.

There is syntax for those variations for the 'old style' joins, but that is non-standard, and for example deprecated in SQL Server 2005.

score 1 · Answer 3 · edited Aug 27 '22 at 02:14

For what it's worth, the distinction of INNER JOIN vs "normal" queries is inaccurate.

Comma-style join syntax was defined in the ANSI SQL-89 standard. Then they realized that they couldn't do some types of joins with that syntax. As @Guffa mentions, Oracle and Sybase invented their own proprietary enhancements to join syntax to handle outer joins.

In ANSI SQL-92, the standard introduced the INNER/OUTER JOIN syntax options. But the old syntax was still supported for the sake of backward compatibility, and it's still in the specification to this day. So they're both standard.

It has been many years since the explicit join syntax became part of the official SQL standard. It's time you accept that as the "normal" syntax.

Which performs better? They both perform exactly the same. All reasonable SQL implementations handle joins the same way, and both syntax forms invoke the same code.

I find the SQL-92 JOIN syntax is more clear. If you have a join of many tables, you can place the join expressions adjacent to the joined table. If you use the old syntax, then the WHERE clause gets cluttered with many terms, and it's hard to sort out which joined table each term goes with.

Some people insist the old SQL-89 style is more clear, but they are wrong. :-)

inner join vs normal query

3 Answers3