SQL RIGHT JOINKeywords

RIGHT JOIN is a join keyword in SQL used to retrieve data from multiple tables.

Similar to LEFT JOIN, but its behavior is the opposite: RIGHT JOIN returns all records from the right table, even if there are no matching records in the left table.

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 are no matches in the left table, the result is NULL.

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

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

• table1: left table.
• table2: right table,RIGHT JOINall records from that table will be retained.
• ON table1.column_name=table2.column_name: specifies the join condition, usually the common field of the two tables.

Features

• Retain all records from the right table: Even if there are no matching records in the left table, all records from the right table will be included in the result.
• Fill left table columns when no matchNULL: If there is no corresponding record in the left table, useNULLto fill the left table columns.

Suppose there are two tables:EmployeesandDepartments。

Employees table：

Departments table：

Query using RIGHT JOIN

SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Query output result：

Explanation：RIGHT JOINReturnedDepartmentsall records in the table. ForDepartmentID = 30records, sinceEmployeesthere is no matching data in the table, itsNamecolumn isNULL。

andLEFT JOINDifferences

• LEFT JOIN: returns all records from the left table, even if there are no matches in the right table.
• RIGHT JOIN: returns all records from the right table, even if there are no matches in the left table.

Demo Database

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

Before proceeding, first add a record to the access_log table that has no corresponding data in the Websites table:

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

Below is data selected 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  | Example      | http://www.example.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is data from the "access_log" website access log 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 website.

In the following 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 outputs the following result:

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).