0

I have been searching about this, and still didn't get satisfying answer. My problem is, I have 2 tables, for example table A and table B.

Table A consists of: id (pk), name, address Table B consists of: b_id (pk), id(fk - from table A), tools

The relationship between these 2 tables is one to many. So each row of A could have more than one items in table B.

Commonly, I always use this way:

  • I select one row from table A

    SELECT * FROM A WHERE id = 1
    
  • Then I select more data from table B

    SELECT * FROM B WHERE b.id = 1
    

Is this way effective? Is that better than using JOIN, like

SELECT A.id, A.name, A.address, B.b_id, B.tools 
FROM a 
  LEFT JOIN b ON a.id = b.id 
WHERE a.id = 1

should I better use JOIN instead?

  • Have you compared the execution plans to see what the differences are? If you're doing the queries separately how are you joining the data after the fact? – Rich Benner May 4 '17 at 10:12
  • Hi @RichBenner . The differences I got are just the way to achieve the result. I mean, I want to know how effective the way I've used from the machine side. I join the data by hard-coded in the program source. Select the first data and put it in a variable and select the second data (usually as a list or vector) and put it in a collection variable – Ichroman Raditya May 4 '17 at 10:31
  • Try benchmarking it both ways. It sounds like you're attempting to replicate in code a JOIN in the DB. Databases are very good at joins, especially if they can use an index for a couple rows. – CalZ May 4 '17 at 12:05
  • I did tried the benchmarking and I found that those 2 ways didn't have a big differences. The load time seems normal and almost same each other. – Ichroman Raditya May 4 '17 at 13:59
  • The only thing that make them different might just the way to parse or achieve them – Ichroman Raditya May 4 '17 at 14:00
0

I would recommend to use JOIN for the following reasons:

  1. It is optimised especially if Index properly designed and also if query get complicated later on.
  2. Easy to read & understand, tidier code and one result-set
  3. It is way the professionals do
  4. select the only columns which are required not everything which could increase network traffic
| improve this answer | |
  • Thanks! Those reasons seems make sense to me. This will be noted – Ichroman Raditya May 4 '17 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.