CODEWITHSUNDEEP
mysqlsql
coiso
coiso

Reputation: 7479

right join even if row on second table does not exist

I want to join two tables even if there is no match on the second one.

table user:

uid | name
1     dude1
2     dude2

table account:

uid | accountid | name
1     1           account1

table i want:

uid  | username | accountname
1      dude1      account1
2      dude2      NULL

the query i'm trying with:

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user RIGHT JOIN account ON user.uid=accout.uid

what i'm getting:

uid  | username | accountname
1      dude1      account1

Upvotes: 34

Views: 39933

Answers (3)

Gonzalo.-
Gonzalo.-

Reputation: 12682

use Left Join instead

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user LEFT JOIN account ON user.uid=account.uid

Upvotes: 38

Brett Rossier
Brett Rossier

Reputation: 3482

Right join keeps all results from the 2nd table (it keeps all results on the right-hand table), you want a left join, or swap the positions of user and account in the join clause.

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user LEFT JOIN account ON user.uid=account.uid

I believe that should do it.

Upvotes: 5

Fabio
Fabio

Reputation: 23510

Try with a LEFT JOIN query

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user 
LEFT JOIN account 
ON user.uid=accout.uid

I'd like you have a look at this visual representation of JOIN query

Upvotes: 5

Related Questions