Mysql 6.2: LEFT/RIGHT JOIN
Written by: maffelu , 2009-04-16 21:20:27
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).
There are no comments on this article.
Leave a message!
If you have any question or just want to leave a message, just fill out the form below!
Your e-mail will not be visible in your post, it is for validation reasons only
Maffelu