Which is more optimal? Join 2 tables Or Use an aditional column in a table

Question

Table A1:

| id | name  | code | level |
|  1 | Anton | A111 |     2 |
|  2 | Berta | A112 |     5 |

Table B1:

| id | name  | code |
|  1 | Anton | A111 |
|  2 | Berta | A112 |

Table B2:

| id | code | level |
|  1 | A111 |     2 |
|  2 | A112 |     5 |

Which is more optimal?

use Table A1 (level column):

SELECT 'id,name' FROM A1 WHERE level=2;

OR

use Table B1 and Table B2 by Join (code column):

SELECT 'id,name' FROM B1 LEFT JOIN B2 ON B1.code=B2.code WHERE level=2;

thank u.

Answer 1

The general answer to your question is that it is preferable to work with the two separate tables, rather than the joined/combined version A1. The reason for this is the principle of normalizaion. Consider the situation where a given name/code matches to more than one level, e.g. this B2:

| id | code | level |
|  1 | A111 |     2 |
|  1 | A111 |     3 |
|  1 | A111 |     4 |
|  2 | A112 |     5 |

Now joining would produce the following table:

| id | name  | code | level |
|  1 | Anton | A111 |     2 |
|  1 | Anton | A111 |     3 |
|  1 | Anton | A111 |     4 |
|  2 | Berta | A112 |     5 |

Note carefully that the above table contains repeated information. We are replicating/storing the name and code three times. In practice, if we use the two table version, we avoid this extra storage cost.

One possible justification for going with the A1 table would be if you needed a lightning fast query to extract your information, and you wanted to avoid a join. But, before you hit that case, you would first look into things like indices, which can usually speed things up enough before one resorts to denormalized table designs.