Related to my last question (MySQLi performance, multiple (separate) queries vs subqueries) I came across another question.
Sometimes I'm using a subquery to select the value from another table (eg. the username connected to an ID), but I'm not sure about the select-in-select, because it doesn't seem to be very clean and I'm not sure about the performance.
The subquery could look like this:
(SELECT `user_name` FROM `users`
WHERE `user_id` = table2.user_id) AS `user_name`
users.user_name , table2.value1, table2.value2
users.user_id = table2.user_id
The very fact that you are asking whether to use
inner join or
left join indeed shows that you haven't done much work with them.
The purposes of these two are entirely different,
inner join is used to return columns from two or more tables where some columns have matching values.
left join is used when you want the rows from the table specified left in the join clause to return even when there is no matching column in the other tables. It depends on your application. If one table has names of players, and another table contains details of penalties paid by them, then you will most certainly want to use
left join, to account for players without a penalty, and thus without a record in the 2nd table.
Regarding whether to use subquery or
joins can be much faster when properly used. By properly I mean, when there are indices on the join columns, the tables are specified in increasing order of the number of containing rows (generally. There might be exceptions), the join columns have similar data-types, etc. If all these conditions match,
join would be the better option.