What is the best performance option? I do
select * from agenda, paciente where agenda.id_paciente = paciente.id_paciente
and pick up the data together from the agenda and patient or do first
select * from agenda
and then another select inside the loop
select * from paciente where id_paciente = agenda.id_paciente
Using two%% of separated% makes sense only if you are going to get data from different tables or very different criteria for each record.
Each time you do a query with PHP, a new request is made to the DB server, which has to do the planning and execution of the data. By sending a query only if it is lean (as a simple join ), planning is done once and the results are processed in a more optimized way by the DBMS.
In the example given, it looks like this:
SELECT
agenda.data,
paciente.nome
... etc, especificando os campos um a um ...
FROM
agenda
LEFT JOIN
paciente ON agenda.id_paciente = paciente.id_paciente
Note these points:
If you want performance, and data economy, you should not use SELECTS , but rather define the fields you will use, one by one.
It's okay to use * , as long as you have a real reason to use it, which does not look like the example; however, by specifying the fields manually, you have only the processing and traffic of the data you really need, and their order is predictable on the data return, even if the table will have new fields in the future.
In addition, since you have more than one table in use, typing the fields avoids ambiguities and mistakes that can go unnoticed when the two tables have fields of the same name.
If you specify the join type and use the ON clause, you have control over the way that join is done.
The way you did with * works, but then you're depending on the planner of DB to understand what you want, and it's not always the best way if the complexity of query is great.
See in this post the essential JOIN types to define the better for the real case.