为什么在mysql中链接表？ [关闭]

Question

Such a good day, everyone is well, the question is the following.

I think a couple of tables in mysql, I sometimes get data from both research I come across the typical "link two tables", "make two queries to two tables", but this has me confused.

That is, if we have two tables, as follows:

Tabla1
| id |  nick  |  
|-------------
| 1 | admin|   

tabla2
| id_post | content | autor |
|---------------------------------
|   100     | asdasd |   1      |

Why relate from mysql, but when you query you can do:

select tabla1.nick, tabla2.* from tabla1, tabla2, where id="1" and tabla2.autor = tabla1.id

What is the difference between the two?, Or what is the benefit to having one or the other?

Answer 1

If I understand your question correctly, you're asking about constraints, why actually make column X on table A refer to column Y on table B, when you can just join the two tables in a SELECT query?

This is to enforce referential integrity, to reduce redundancy, etc. Doing so makes the data itself reliable so that when you use joins in your SELECT statement, they work as they should.

If you had an ASSIGNMENTS and a SUPERVISORS table, for instance, and each assignment is always assigned to a supervisor on a supervisors table, a foreign key constraint between the supervisor field on ASSIGNMENTS and SUPERVISORS will ensure that happens. It also gives you flexibility as to what should occur if the supervisor value changes on one table (should it be restricted? should the change be carried through to the other table? etc.)

Without the relationship being defined, an assignment might be assigned to a supervisor who does not even exist. And then the results of your SELECT statements won't be all that reliable...