SQL Two SELECT vs. JOIN best performance?

Question

I wonder which has better performance in this case. First of all, I want to show to the user his medical information. I have two tables

user
-----
id_user | type_blood | number | ...
   1          O          123
   2          A+         442

user_allergies
-----------
id_user | name
  1       name1
  1       name2

I want to return:

JSON {id_user=1, type_blood=0, allergies=(name1,name2)}

So, Its better do a JOIN for user and user_allergies and iterate, or maybe two SELECT?

But if then I have another table like user_allergies, that the result can be:

user_another_table
-----------
id_user | name
  1       namet1
  1       namet2
  1       namet3

JSON {id_user=1, type_blood=0, allergies=(name1,name2), table=(namet1,namet2,namet3)}

It's better three SELECT or a JOIN, but then I have to iterate on the results and I can't imagine a esay way. A JOIN can give me a result like:

id_user | type_blood | allergy_name | another_table_name
   1          O           name1            namet1
   1          O           name1            namet2
   1          O           name1            namet3
   1          O           name2            namet1
   1          O           name2            namet2
   1          O           name2            namet3

Is there any way to extract:

id_user | type_blood | allergy_name | another_table_name
   1          O           name1            namet1
   1          O           name2            namet2
   1          O                            namet3

Thanks community, I'm newbie in SQL

I don't know how to put this, other than to say you'd `JOIN` as the situation dictates. If the result set needs information from multiple related tables you would `JOIN` them in one manner or another. — Yuck, Jun 23 '14 at 15:13

score 0 · Answer 1 · edited May 23 '17 at 11:49

Depending on the data - there is no way to get the 2nd set of results you've shown, if the 1st set of results shows the values. The 2nd one is throwing data away - in this case allergy 'name2' for another_table_name 'namet3'. This is why you get many rows back with repeated data.

You can use the group by clause to restrict this in some cases, but again - it won't let you throw away data like that.

You could try using the COALESCE clause, if your DB supports it.

If not, I think you're going to have to construct your JSON in some business logic, in which case its fine to read the data in a 3-way join. You order by the user id and either create or append the row data to the JSON document depending if a user record is present or not (if you order by user id, you only need to keep track of when the user id value changes).

Alternatively, you can read a list of users and single-item data in one query, and then ht the DB again for the repeating data.

SQL Two SELECT vs. JOIN best performance?

1 Answers1