4

imagine I have 2 simple tables

users (id,username,password)
shopping(user_id,product_id)

and I use inner join to find each username buys which product : 

select username,product_id
from shopping
inner join users
on users.id=shopping.user_id

but I can write a more simple query without using inner join and it works 

select username,product_id
from shopping,users
where shopping.user_id=users.id

and result is the same I wonder to know what's the advantage of using inner join !!

John Saunders
  • 160,644
  • 26
  • 247
  • 397
alisharifi01
  • 49
  • 1
  • 3
  • 6
    `INNER JOIN` is the **proper**, ISO/ANSI Standard defined way of joining two tables. The old way of just having a comma-separated list of tables has been **discontinued** with the SQL-1992 standard **more than 20 years ago** - stop using it, please! – marc_s Dec 15 '13 at 13:40
  • 1
    One of the main advantages of the new standard JOIN syntax to me is the fact that you are forced to define the **join condition** right the `JOIN` and there's less chance to forget that join condition (which would result in a **cartesian product** - not something you want to have when you're dealing with large tables). Also it makes the `WHERE` clause less cluttered, since the JOIN conditions aren't intermixing with the "normal" WHERE conditions your query has – marc_s Dec 15 '13 at 13:42
  • describe and analyse functions (depending on your sql engine) will explain things as well. – ddoor Dec 15 '13 at 13:43
  • You can use the Execution Plan to see how the two queries are executed "Under the Covers" but as marc_s points out, the syntax is deprecated and you should use the Inner Join. – John Bartels Dec 15 '13 at 13:45

1 Answers1

1

Inner Join is Used to Extract the Data's from one or more tables, that's why we are going for inner join instead of Where condition

SQL statements are synonymous, though specifying the INNER JOIN is the preferred method and follows ISO format. I prefer it as well because it limits the plumbing of joining the tables from your where clause and makes the goal of your query clearer.

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115