CODEWITHSUNDEEP
mysqlnullfull-outer-join
Matin Lotfaliee
Matin Lotfaliee

Reputation: 1615

Full outer join on mysql?

i have 2 tables that i want to make them full outer join.

i am using the following query:

select * from permissions 
left join user_news_permission on permissions.userID = user_news_permission.userID
union
select * from permissions 
right join user_news_permission on permissions.userID = user_news_permission.userID

but it makes two userID columns that some are null and some are not.

how can i make it one userID column that all of them are not null?

Upvotes: 0

Views: 62

Answers (2)

Ali Ather
Ali Ather

Reputation: 1

I am not a professional But try this

SELECT* FROM permissions
RIGHT JOIN user_news_permission
ON permissions.userID = user_news_permission.userID;

Upvotes: 0

neverendingqs
neverendingqs

Reputation: 4276

When you do a left join, any records found in the left table but not in the right table will result in all columns in the right table replace by nulls (and vice versa for a right join).

If you are seeing nulls, either a record in the left table exists with a given userID that does not exist in the right table, or a record exists in the right table that does not exist in the left table.

If you are looking for user IDs from permissions and any information from user_news_permission, the former half of the union is sufficient:

select * from permissions 
left join user_news_permission on permissions.userID = user_news_permission.userID

If you are looking for user IDs from user_news_permission and any information from permissions, the latter half of the union is sufficient:

select * from permissions 
right join user_news_permission on permissions.userID = user_news_permission.userID

If you just care about the users in one table, a join isn't necessary. Just select all records from that table.

Upvotes: 0

Related Questions