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?

Ichroman Raditya
  • 3
  • 1
  • 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 04 '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 04 '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 04 '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 04 '17 at 13:59
  • The only thing that make them different might just the way to parse or achieve them – Ichroman Raditya May 04 '17 at 14:00

1 Answers1

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
Sajid
  • 190
  • 1
  • 9