SQL RIGHT JOIN


The SQL RIGHT JOIN keyword returns all records from the right side table (tableB), with the matching records in the left side table (tableA).

The query result is NULL in the left side table when there is no match records.


SQL RIGHT JOIN Syntax


SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
RIGHT JOIN table_name2
ON table1.column_name1 = table2.column_name1;

or

SELECT column_name1, column_name2, ...column_nameN
FROM table_name1
RIGHT OUTER JOIN table_name2
ON table1.column_name1 = table2.column_name1;

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

SQL RIGHT JOIN


Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
3 SQL Server 2008 Transact-Sql 200 2006 Administration Siva Kumar
4 Professional Microsoft SQL Server 90 2015 Database Vidyavathi
6 Simply MySql 190 2012 Optimization Nirmala
7 SQL Server Database Internals 150 2006 Security Dharan
8 Mastering Oracle SQL 136.33 2012 Performance Nirmala
9 Oracle Interview Questions 65 2015 Administration Chandra

Sample Database Table - BookOrder

BookID OrderDate Qty DeliveryDate
1 12-02-2005 16 18-03-2005
2 18-02-1996 2 21-03-1996
4 19-08-2007 18 25-08-2007
6 14-01-2004 10 17-02-2004
8 14-08-2008 1 24-08-2008
12 18-06-2002 6 25-06-2002

Note:- There must be a common columns while selecting columns from multiple tables. In the "BookOrder" and "Books" both tables contains a common column called "ID".


SQL RIGHT JOIN - Example

Notice that the "BookID" column in the "Books" table refers to the "BookID" in the "BookOrder" table. The relationship between the two tables above is the "BookID" column.

Then, if we run the following SQL statement (that contains an RIGHT JOIN):


SELECT 
BookOrder.BookID, BookOrder.BookName, Books.BookPrice, Books.RelYear, BookOrder.DeliveryDate
FROM Books
RIGHT JOIN BookOrder
ON Books.BookID = BookOrder.BookID;

The result of above query is:

BookID BookName BookPrice RelYear DeliveryDate
1 Securing SQL Server 18-03-2005
2 PHP And MySQL Bible 21-03-1996
4 Professional Microsoft SQL Server 90 2015 25-08-2007
6 Simply MySql 190 2012 17-02-2004
8 Mastering Oracle SQL 136.33 2012 24-08-2008
12 MySql Interview Questions 25-06-2002

Note: The SQL RIGHT JOIN keyword returns all the records from the right side table (Books), even if there are no matches in the left side table (BookOrder).