-1

I have a users table and a books table. Two tables have a pretty standard One-to-Many relationship. Their data and structure are as follow:

users table:
enter image description here

books table:
enter image description here

To get the information of what books does each user have, I would normally do a standard JOIN query:

SELECT * FROM users JOIN books ON users.id = books.user_id

At the same time, I came across with another query, that seems to do the same work, which writes as:

SELECT * FROM users, books WHERE users.id = books.id

These two queries seems to give the exactly same result as follow enter image description here

The second query without the JOIN clause I see in one of Standford's online courses and it seems to work fine. However I was taught to use, and saw other developers using the JOIN as the approach to this type of relationship. My questions are:

  1. Are those two queries equivalent in terms of the query result?
  2. Is there a reason why we should use one over the other?
  3. Do the SQL engine perform different when executing these two ?
Cheng Shi
  • 160
  • 3
  • 7
  • Does this answer your question? [INNER JOIN ON vs WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) – philipxy Jan 14 '22 at 05:42
  • (Clearly,) This is a faq. Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect your research. See [ask], [Help] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. – philipxy Jan 14 '22 at 05:43
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please in a post ask exactly 1 specific researched non-duplicate question. Please in code questions give a [mre]--obligatory if debugging. – philipxy Jan 14 '22 at 05:43

2 Answers2

1

1. Result:

Both the queries would definitely give you the same result

2. Which is better

It is recommended to use the first one. Because it is safer in many ways. For instance, if we have to perform INNER JOIN, using the second syntax:

SELECT * 
FROM users, books 
WHERE users.id = books.id

If you forgot the WHERE users.id = books.id, the query won't fail. But it will return a cross join and we may not notice this error.

However, using the first syntax:

SELECT * 
FROM users 
JOIN books ON users.id = books.user_id

If we forget the ON condition, we will get a syntax error. Moreover, doing a CROSS JOIN will be very comprehensive in first syntax:

SELECT *
FROM users CROSS JOIN books

Than the second syntax.

Refer this Blog.

3. Performance:

As far as I'm aware, there is no performance difference between both the queries

Additional info:

The difference between the two queries is the JOIN syntax:

  • The first one use the ANSI-92 JOIN syntax.
  • The second one use the old ANSI-89 JOIN syntax.
Arun
  • 1,071
  • 3
  • 13
0

I would say there is no difference between the two queries besides readability. Since you are joining two tables here it makes more sense to use join instead of where clause.

qTzz
  • 126
  • 6