CODEWITHSUNDEEP
mysqlsql
JoeFrizz
JoeFrizz

Reputation: 711

2 Queries vs inner join with where

I have 2 tables: Articles and Comments; "Comments.articleID" is a foreign key.

I want to query the database to compose a website that shows the article text of a certain article (given an articleID) and all the article's comments.

I can think of 2 ways to query the data:

  1. Use 2 separate queries: 

    SELECT articles.text FROM articles where id = givenArticleID

SELECT comments.* FROM comments where comments.articleID = givenArticleID

  2. Use an Inner join:

     SELECT articles.text, comments.* 
 FROM articles 
 INNER JOIN comments on articles.id = comments.articleID 
 WHERE articles.id = givenArticleID

The first option only returns the data I am interested in - that is good.

The second option returns all data I am interested in, but much more data than necessary. Every row in the result set contains the article.text column, that could be a lot of (unnecessary) data.

I think that the join would be better for certain queries, that do not require a WHERE condition (thus containing different articles).

Which way would you generally prefer in the situation above? Or is there an even better alternative...?

Upvotes: 0

Views: 53

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246318

Option 2 is probably better, because it is only one client-server round trip.

Also don't forget that each query has to be parsed by the database server.

I'd recommend that you benchmark both versions and see which one performs better.

Upvotes: 1

Related Questions