SQL Optimization WHERE vs JOIN

Question

I am using mysql and this is a query that I quickly wrote, but I feel this can be optimized using JOINS. This is an example btw.

users table:
id user_name first_name last_name email         password
1  bobyxl    Bob        Cox       bob@gmail.com pass

player table
id role player_name user_id server_id racial_name 
3  0    boby123     1       2         Klingon
1  1    example     2       2         Race
2  0    boby2       1       1         Klingon

SQL

SELECT `player`.`server_id`,`player`.`id`,`player`.`player_name`,`player`.`racial_name` 
FROM `player`,`users` 
WHERE `users`.`id` = 1
and `users`.`id` =  `player`.`user_id`

I know I can use a left join but what are the benefits

SELECT `player`.`server_id`,`player`.`id`,`player`.`player_name`,`player`.`racial_name` 
FROM `player` 
LEFT JOIN `users` 
ON `users`.`id` =  `player`.`user_id`
WHERE `users`.`id` = 1

What are the benefits, I get the same results ether way.

score 5 · Accepted Answer · answered Nov 30 '13 at 01:41

5

Your query has a JOIN in it. It is the same as writing:

SELECT `player`.`server_id`,`player`.`id`,`player`.`player_name`,`player`.`racial_name` 
FROM `player`
INNER JOIN `users` ON `users`.`id` =  `player`.`user_id`
WHERE `users`.`id` = 1

The only reason for you to use left join is if you want to get data from player table even when you don't have matches in users table.

answered Nov 30 '13 at 01:41

Filipe Silva

21,189
5
53
68

The player table will have multiple "characters" per user. – c3cris Nov 30 '13 at 01:49
Yes, but if you have a match between `users`.`id` = `player`.`user_id`, you will always get the same information from an inner join and a left join in this case. See here for a quick example of the difference. http://sqlfiddle.com/#!2/7e6b0/3 – Filipe Silva Nov 30 '13 at 01:55
@CrisG. having multiple players per user doesn't change anything if you always have a match in users.id = player.user_id. You can use an inner join in that case. – Filipe Silva Nov 30 '13 at 01:57
1

@CrisG. in the left join you get the players even if they don't belong to that user. That's why i'm saying that you want an INNER JOIN. See [this schemma](http://stackoverflow.com/a/16598900/1385896) for a good visualization of what a left join is. – Filipe Silva Nov 30 '13 at 02:12
Thank you, So you are talking about the NULL in the 3rd query where player 3 has no character correct? http://sqlfiddle.com/#!2/61572/1 – c3cris Nov 30 '13 at 02:31
@CrisG. Yes. That's the one that LEFT JOIN returns that INNER JOIN does not. That's what the LEFT does. Returns always the value in the left even when there's no match in the right, and returns null for the "right" values in the query. – Filipe Silva Nov 30 '13 at 02:34

score 0 · Answer 2 · answered Nov 30 '13 at 01:49

LEFT JOIN will get data from the left table even if there's no equal data from the right side table. I guess at one point, that player table's data will not be equivalent to users table specially if the data on users table has not been inserted into player table.

Your first query might return null on cases that the 2nd table (player) has no equivalent data corresponding to users table.

Also, IMHO, setting up another table for servers is a good idea in terms of complying to the normalization rules in database structure. After all, what details of the server_id is the column on player table pointing to.

EDITED the question : The player table will have multiple "characters" per user. — c3cris, Nov 30 '13 at 01:53

score 0 · Answer 3 · answered Nov 30 '13 at 02:07

The first solution makes a direct product (gets and connects everything with everything) then drops away the bad results. If you have a lot of rows this will be very slow!

The left join gets first the left table then put only the matching rows from the right (or null).

In your example you don't even need join. :) This'll give you the same result and it'll be good until you just check for user id:

SELECT `player`.`server_id`,`player`.`id`,`player`.`player_name`,`player`.`racial_name` 
FROM `player`
WHERE `player`.`user_id` = 1

Another solution if you want more conditions, without join could be something like this:

SELECT * FROM player WHERE player.user_id IN (SELECT id FROM user WHERE ...... )

Yeah, but what would you like to know? The nested one selects and gives back all the user ids what you want, just give it a condition, then the outer one selects the players via that user id list. Similar examples: http://www.tutorialspoint.com/mysql/mysql-in-clause.htm — Gerifield, Nov 30 '13 at 11:03

SQL Optimization WHERE vs JOIN

3 Answers3