I have two tables

1) Id Name Id_Proffesia 1 Denis 2 2 Alex 2 2) Id Professia 1 Programmist 2 Ingener

The first database will be quite large (100.000 records). I need to display the id, the name of the person and his profession, like this:

  2 Alex Ingener

I have only one question: in what way is it better to do this (Join, a subquery or via the @ variable)?

  • this kind of query via JOIN to the table with entries> 12k took 0.0015 seconds from me ... and my queries go not to two, but to three tables ... with two join-s - Yokharny Babay
  • Also, for the Id_Proffesia field, an Id_Proffesia must be set, or, even better, a foreign key. - KiTE

2 answers 2

Like this:

 select u.id, u.Name, p.Professia from users as u left join prof as p on u.Id_Professia = p.id

This is the standard query type for such links. So if you, for example, still have a table with a formation, then add an additional left join .

Precisely left join . Because if you have one fine day that the profession is not set (null), then in the case of a bunch of join you will not see these users.

  • In general, it is clear. Thank you very much. - Arthur Lodenev

join.

 select tbl1.*, tbl2.Professia from tbl1 inner join tbl2 on tbl1.Id_Proffesia = tbl2.id limit 30;