I have a X table that has my user's data, and I need to return data related to that user to the Y and Z table to the client.
I can do this using JOIN or:
SELECT attr1, attr2 FROM Y WHERE X_id = id
SELECT attr1, attr2 FROM Z WHERE X_id = id
Which is more efficient?
Depends on the database, depends on the data in the tables, depends on the configuration of the table, depends on other specific factors.
It might be the same, it may be that JOIN is faster because it is more optimized, but it may end up doing some operations because it has a relationship that would not be made in the separate query.
You can not say this except to say that you have to measure your specific case with the current data. And it may change in the future.
If I have to kick I would say JOIN will be faster, but I do not rely on kicks.
I think the best solution would be to group everything into a single query:
SELECT Y.*, Z.*
FROM X
JOIN Y on Y.id = X.id
JOIN Z on Z.attr1 = Y.attr1
WHERE X.id = @id
You could remove the X table from the join and use a subselect , but I believe the above solution performs better, / em> for the X.id comparison.