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?

5
  • 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? Commented May 4, 2017 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 Commented May 4, 2017 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
    Commented May 4, 2017 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. Commented May 4, 2017 at 13:59
  • The only thing that make them different might just the way to parse or achieve them Commented May 4, 2017 at 14:00

1 Answer 1

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
1
  • Thanks! Those reasons seems make sense to me. This will be noted Commented May 4, 2017 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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