Comparing Query Accomplishment Speed By Using JOIN or not

optimization

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?

Best Answer

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
Related Question