SQL – Right Join (Right outer join)

Prev     Next

  • SQL RIGHT JOIN is used to combine the two tables together. RIGHT JOIN selects all records from Right table and also selects all matching records from the left table.
  • And, RIGHT JOIN selects all records from the right table, even though there are no matching records in the left table. In this scenario, all selected left column values will be returned as NULL.
  • RIGHT JOIN is also called as RIGHT OUTER JOIN.

SQL Syntax for Right Join (Right outer join):

SQL Syntax for RIGHT JOIN (RIGHT OUTER JOIN) SELECT table1.column1, table2.column2, etc

FROM table1

RIGHT JOIN table2


ON table1.column_name = table2.column_name;

Or

 

Another Equivalent SQL Syntax for RIGHT JOIN (RIGHT OUTER JOIN)

SELECT table1.column1, table2.column2, etc

FROM table1


RIGHT OUTER JOIN table2

ON table1.column_name = table2.column_name;

Please consider following 2 tables with few records as given below.

Table1:

Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows

sql-table1

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows

sql-table44

Example: how to use SQL Right Join (Right outer join):

SQL query:

SELECT student1. Student_name, student1.City, student2. Department, student2.Rank
FROM student1
RIGHT JOIN student2
ON student1.Student_ID = student2.Student_ID;

Or

SELECT student1.Student_name, student1.City, student2. Department, student2.Rank
FROM student1
RIGHT OUTER JOIN student2
ON student1.Student_ID = student2.Student_ID;

Description:

In above SQL RIGHT JOIN, all 5 records are returned from Right table ‘student2’. And, all matching records are selected from left table and displayed in the output. Also, please note that for the record ‘Student_ID’ = 5 in the right table, there is no matching record in the left table. So, NULL values are returned and displayed in the output.

RUN SQL

SQL query Output:

sql-table45

CONTINUE ON OTHER SQL JOINS:

Click on each SQL joins below for detailed description and example SQL queries.

Type of SQL JOINS Description
SQL – Inner Join (Simple Join) It is used to combine two or more tables together using the matching columns from both tables.
SQL – Left Join (Left outer join) LEFT JOIN selects all records from left table and also selects all matching records from the right table.
SQL – Right Join (Right outer join) RIGHT JOIN selects all records from right table and also selects all matching records from the left table.
SQL – Full Join (Full outer join) FULL JOIN selects and returns all records from both left and right tables.
SQL – Self Join Self Join is used to join a table to itself.
SQL – Cartesian Join or Cross Join Cartesian Join returns the Cartesian products of 2 or more tables joined together.

Prev     Next

Share this website !!!
Facebook Twitter Linkedin