Latest web development tutorials

SQL RIGHT JOIN Keyword

SQL RIGHT JOIN Keyword

RIGHT JOIN keyword right table (table2) returns all rows, even if there is no match left table (table1) in. If there is no match 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.

SQL RIGHT JOIN


The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data:

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

Here is the data "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 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)


SQL RIGHT JOIN examples

The following SQL statement will return the site visit records.

The following examples access_log as we left the table, Websites as the right table:

Examples

SELECT Websites. Name, access_log. Count, access_log. Date FROM access_log RIGHT JOIN Websites ON access_log. site_id = Websites. id ORDER BY access_log. count DESC;

Execute the above SQL output results are as follows:

NOTE: RIGHT JOIN keyword right table (Websites) returns all rows, even if there is no match left table (access_log) in.