SQL JOIN INNER or OUTER

I have two tables: users: id username clan clans: id clan crest

I trying to do the search by username with output the clan of username.

Query:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` JOIN `clans` ON `users`.`username` LIKE '%gu%' 

Output:

id  username    clan       crest       
2   gusar                  26_760.gif 
3   gusar11     Tong       26_760.gif 

If clan is empty crest will be empty.. But ... (

I want following result:

id  use开发者_如何学Gorname    clan       crest       
2   gusar                  
3   gusar11     Tong       26_760.gif 

You're joining from one table to another using a LIKE, typically there is little reason why you would do this. Instead you likely want that in a WHERE clause, and you should be joining between the table using a foreign key.

You want to use a LEFT OUTER JOIN (or LEFT JOIN for short) to attempt to match all of the rows in the second table, but to show NULL values if there was no match:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest`
FROM `users` 
LEFT JOIN `clans` ON `users`.`clan` = `clans`.`clan`
WHERE `users`.`username` LIKE '%gu%'

Mind you, in your users table, ideally you should hold the clan id and not the clan title. You would then use this query to perform your search:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest`
FROM `users` 
LEFT JOIN `clans` ON `users`.`clanid` = `clans`.`id`
WHERE `users`.`username` LIKE '%gu%'

If you want results from the first table, even when there are NO matches in the second table, you want to use a LEFT OUTER JOIN.

Try

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` LEFT JOIN `clans` ON `users`.`username` LIKE '%gu%' 

You need to join the table using some condition. You're using an ON where you should use a WHERE. I assume users.clan relates to clans.clans? Try this:

SELECT `users`.`id`, `users`.`username`,`users`.`clan`, `clans`.`crest` FROM `users` LEFT JOIN `clans` ON `users`.`clan` = `clans`.`clan` WHERE `users`.`username` LIKE '%gu%'