Join or multiple sql queries

Question

I'm wondering what is the most effective(fast and less work for database) way to retrieve data. I have 2 tables:

users

ID | NAME 
1  | John
2  | Mike
3  | Jack

data

USER_ID | DATA
2       | some_data_1
3       | some_data_2

I need to get value by user name and as i understand i have two options:

select id from users where name ='some_name' and then select from data table by id.

or

select id from users join data on users.id = data.user_id where user.name = 'some_name'

Also, i guess it's important to note: those are example of real tables with thousands of rows and few more columns, there is an index on user_id column in data table, I'm using jdbc driver under and it's a network call, and it's postgreSQL if it matters.

upd. Also, any links to researches or any kind of papers/docs/speeches would be very interesting to read

Gordon Linoff · Accepted Answer · 2020-07-09 11:37:38Z

In general, you want to let the database do as much work as possible. In general, databases run on more sophisticated servers and there have been zillions of person-years dedicated to the optimizers and to making the code run effectively.

In addition, there is overhead for each query that you send to the database. The query needs to be parsed and compiled before it can be run. Each query also has back-and-forth communication. So, one query is typically better than two.

The generic answer to your question is to use a single query, the one with the join.

There are some circumstances where multiple queries have better performance than one query. But in your case, you are looking for only one row or a handful of rows. And the database is well-designed for the join. So, use the single query.

Popeye · Accepted Answer · 2020-07-09 11:39:04Z

Join is better option as it will give you data in one shot.

If your application is calling the two queries then it will be the waste of the resources twice (your application's resources, network time, DB parses, and other stuff) which can easily be avoided using a single query. Resources will be used only once.

So it is better to use one query rather than two queries to achieve the same purpose.

Go with JOIN:

select id 
  from users 
  join data on users.id = data.user_id 
 where user.name = 'some_name'

Collectives™ on Stack Overflow

Join or multiple sql queries

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related