MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 6.2: LEFT/RIGHT JOIN

2009-04-16 21:20:27 | 138 views | sql mysql table left right join exception matches

LEFT JOIN:



LEFT JOIN works just like INNER JOIN with one small exception; LEFT JOIN returns every row in the left table together with only the matches in the right table.

This will be our table 'users'

|--------------------+--------------------|
|id |name |
|____________________+____________________|
|1 Maffelu |
|2 Wimpy |
|3 Gh0ztK1ll4h |
|4 Nobou |
+-----------------------------------------+


This will be our table 'logins'

|--------------------+--------------------|
|userId |time |
|____________________+____________________|
|2 15:29 |
|2 18:29 |
|1 05:03 |
|4 09:20 |
+-----------------------------------------+



We want to join the two tables and show the time of login for each user.

Syntax:

SELECT table1.column, table2.colum
FROM table1
LEFT JOIN table2
ON condition


Example:

The query will look like this:


SELECT users.id AS UID, users.name AS Name, logins.time AS Time
FROM users
LEFT JOIN logins
ON users.id = logins.userId


|--------------------+--------------------+--------------------|
|UID |Name |Time |
|____________________+____________________+____________________|
|1 Maffelu 05:03 |
|2 Wimpy 15:29 |
|2 Wimpy 18:29 |
|3 Gh0ztK1ll4h NULL |
|4 Nobou 09:20 |
+--------------------------------------------------------------+


As we can see, we have a user, Wimpy, who't been logged in twice, but user Gh0ztK1ll4h hasn't been logged in at all.

RIGHT JOIN



This will be our table 'users'

|--------------------+--------------------|
|id |name |
|____________________+____________________|
|1 Maffelu |
|2 Wimpy |
|3 Gh0ztK1ll4h |
|4 Nobou |
+-----------------------------------------+


This will be our table 'logins'

|--------------------+--------------------|
|userId |time |
|____________________+____________________|
|2 15:29 |
|2 18:29 |
|1 05:03 |
|4 09:20 |
+-----------------------------------------+


With the LEFT JOIN method we showed all users and when they had logged in(somehwere), this time, we just want to show the logins, with the connected users, time to use the RIGHT JOIN.

In our example below, we will


SELECT users.id AS UID, users.name AS Name, logins.time AS Time
FROM users
RIGHT JOIN logins
ON users.id = logins.userId


|--------------------+--------------------+--------------------|
|UID |Name |Time |
|____________________+____________________+____________________|
|2 Wimpy 15:29 |
|2 Wimpy 18:29 |
|1 Maffelu 05:03 |
|4 Nobou 09:20 |
+--------------------------------------------------------------+


What we get here is a list of all logins, not all users.

The differance between LEFT and RIGHT join is stupid =/ so always use LEFT JOIN and just switch the table arguments. (SELECT * FROM users LEFT JOIN logins ---> SELECT * FROM logins LEFT JOIN users).



Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.