CODEWITHSUNDEEP
mysqlsqlperformancejoin
qlt
qlt

Reputation: 55

MySQL using select with 2 queries, subquery or join?

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
    (SELECT `user_name` FROM `users` 
     WHERE `user_id` = table2.user_id) AS `user_name`
    , `value1`
    , `value2`
FROM
    `table2`
....

Would it be "better" to use a separate query for the result from table1 and another for table2 (doubles the connections, but no need to cross tables), or should I even use a JOIN to get the results in a single query?

I don't have much experience with JOINS and subqueries yet, so I'm not sure if a JOIN would be "too much" in this case, because I really just need one name connected to an ID (or maybe count the number of rows from a table), or if it doesn't matter, because the select-in-select is treated like some kind of JOIN, too..

Solution with JOIN could look like this:

SELECT
    users.user_name , table2.value1, table2.value2
FROM
    `table2`
INNER JOIN
    `users`
ON
    users.user_id = table2.user_id
....

And if I should prefer JOIN, which one would be best in this case: left join, inner join or something else?

Upvotes: 3

Views: 2540

Answers (1)

NedStarkOfWinterfell
NedStarkOfWinterfell

Reputation: 5153

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 join, 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.

Upvotes: 6

Related Questions