1

What is the difference between ?

SELECT u.id,u.name,u.email,d.city,d.state 
from users u, userdetails d 
where u.id=d.uid

SELECT u.id,u.name,u.email,d.city,d.state 
from users u join userdetails d on u.id=d.uid

Are they both different style of writing join ? which one is faster ?

John Woo
  • 258,903
  • 69
  • 498
  • 492
Ace
  • 841
  • 9
  • 23
  • 1
    the first is ansi 86 syntax and the second is ansi 92, look http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax – rray Nov 04 '13 at 11:50
  • @今草顿웃 How do you know that a question is duplicate? Do u search stackoverflow for questions resembling this one or one with higher rep has some options where they get a hint that this may be duplicate? – Ace Nov 04 '13 at 11:59
  • 1
    i have bookmarked this page long time ago `:)` – John Woo Nov 04 '13 at 12:00

4 Answers4

0

You would want to go for the joining option as it gives you more control, look in to the different types of join (Inner, Left etc) and you can work out exactly what you need for your example.

(I suspect Inner would be best)

http://www.w3schools.com/sql/sql_join.asp

Sam Hodges
  • 9
  • 3
0

This:

SELECT u.id,u.name,u.email,d.city,d.state 
FROM users u, userdetails d

Is Cartesian product (the set of all ordered pairs).

When you add the where clause:

SELECT u.id,u.name,u.email,d.city,d.state 
FROM users u, userdetails d 
WHERE u.id=d.uid

You get Join (combining fields from two tables by using values common to each), which is equivalent to:

SELECT u.id,u.name,u.email,d.city,d.state 
FROM users u join userdetails d on u.id=d.uid
Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
0

The variables may not have the same name in each data set, so a natural join can’t be used. It is also quite possible to join more than two tables in the same statement. You can even do transformations and conditional logic.

Anitha Mani
  • 863
  • 1
  • 9
  • 17
0

The two options do the same, but the second is an improved syntax (ANSI SQL-92) and the first is a syntax oldest. Whenever your database system used allows, it is better to use the syntax of the second option.

David Miró
  • 2,694
  • 20
  • 20