SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), even if there are no matches in the left table (table1). If there is no match, the result is NULL on the left side.

SQL RIGHT JOIN Syntax

Or:

Note: In some databases, RIGHT JOIN is called RIGHT OUTER JOIN.

Demonstration Database

In this tutorial, we will use the tutorialpro sample database.

Before proceeding, add a record to the access_log table, which does not have a corresponding record in the Websites table:

INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`) VALUES ('10', '6', '111', '2016-03-09');

Below are the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | tutorialpro.org      | http://www.tutorialpro.org/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below are the data from the "access_log" website access record table:

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
|  10 |       6 |   111 | 2016-03-19 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

SQL RIGHT JOIN Example

The following SQL statement will return the access records of the websites.

In this example, we use Websites as the left table and access_log as the right table:

Example

SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

Executing the above SQL will produce the following output: Note: The RIGHT JOIN keyword returns all rows from the right table (access_log), even if there are no matches in the left table (Websites).