1

what is the different between this queries? which one should I prefer and why?

SELECT
t1.*,
t2.x
FROM
t1,
t2
WHERE
t2.`id` = t1.`id`

or 

SELECT
t1.*,
t2.x
FROM
t1
INNER JOIN                                                  # LEFT JOIN ?
t2
ON t2.`id` = t1.`id`

Does using commas has the same effect than use LEFT JOINS?

That's embarrassing. It's the first time I asked myself about this for years. I ever used the first version, but now i'm feeling like I missed some lines in my first SQL induction. ;)

Tom
  • 303
  • 2
  • 5
  • 15
  • possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – Joe Stefanelli Apr 18 '12 at 18:49

4 Answers4

0

The queries are the same, the first is just short-hand syntaxfor the second. LEFT JOIN is something entirely different.

INNER JOIN only shows the records common to both tables. LEFT JOIN takes all records from the left table and match it to those of the right table. If no records in the right table match, NULL will be selected in their place.

kba
  • 19,333
  • 5
  • 62
  • 89
0

The "comma" syntax is equivalent to the INNER JOIN syntax. The query optimizer should be running the same query for you regardless of how you ask for it. There's a general recommendation to do your joins using the JOIN language and do your filtering using the WHERE language as it makes your intention more clear

Pavel Veller
  • 6,085
  • 1
  • 26
  • 24
0

inner joins are left joins that skip on empty (null) results in the second table.

query optimizers have more possibilites when everything is done using the where clause. some rdbms don't support commands like natural join and other joins, so using "where" is something one can really rely on.

Hajo
  • 849
  • 6
  • 21
0

The first query is called cartisian query and usually provide undesired results in the large database.

Instead using joins will produce exactly what you want.

Muhammad Raheel
  • 19,823
  • 7
  • 67
  • 103