SQL Right Join

In databases, data is stored in multiple tables and sometimes it is required to combine data from two tables to get the desired data. In SQL databases, Joins enable the merging of multiple tables based on the common columns. 

In this article, we are going to explore SQL Right JOIN Keyword which is a type of Outer Join in SQL. We will cover everything about the Right JOIN in detail and also perform some queries in examples for better understanding.

Right JOIN Keyword

The RIGHT JOIN keyword in SQL returns a table that contains all the records from the right table and only matching records from the left table.

In simple words, if a certain row is present in the right table but not in the left, the result will include this row but with a NULL value in each column from the left. If a record from the left table is not on the right, it will not be included in the result.

The visual representation of Right JOIN is shown below:

RIGHT JOIN

Syntax

SELECT column_name(s)
FROM tableA 
RIGHT JOIN tableB ON tableA.column_name = tableB.column_name;

SQL RIGHT JOIN Examples

In this example we will consider two tables employee table containing details of the employees working in the particular department the and department table containing the details of the department

employee table :

emp_no	emp_name	dept_no
E1	Varun Singhal	D1
E2	Amrita Aggarwal	D2
E3	Ravi Anand	D3

SQL Query to create employee table:

MySQL

CREATE TABLE employee (
  emp_no CHAR(3) PRIMARY KEY,  -- Adjust length if needed for employee numbers
  emp_name VARCHAR(50) NOT NULL,
  dept_no CHAR(2)
);
INSERT INTO employee (emp_no, emp_name, dept_no)
VALUES ('E1', 'Varun Singhal', 'D1'),
       ('E2', 'Amrita Aggarwal', 'D2'),
       ('E3', 'Ravi Anand', 'D3');

department table :

dept_no	d_name	location
D1	IT	Delhi
D2	HR	Hyderabad
D3	Finance	Pune
D4	Testing	Noida
D5	Marketing	Mathura

SQL Query to Create department table:

MySQL

CREATE TABLE department (
  dept_no CHAR(2) PRIMARY KEY, -- Adjust length if needed for department codes
  d_name VARCHAR(20) NOT NULL,
  location VARCHAR(50)
);

INSERT INTO department (dept_no, d_name, location)
VALUES ('D1', 'IT', 'Delhi'),
       ('D2', 'HR', 'Hyderabad'),
       ('D3', 'Finance', 'Pune'),
       ('D4', 'Testing', 'Noida'),
       ('D5', 'Marketing', 'Mathura');

Example: Performing Right Join in SQL

To perform right- join on these two tables we will use the following SQL query:

SELECT emp_no , emp_name ,d_name, location 
FROM employee 
RIGHT  JOIN dept on employee.dept_no = department.dept_no;

Output:

emp_no	emp_name	d_name	location
E1	Varun Singhal	IT	Delhi
E2	Amrita Aggarwal	HR	Hyderabad
E3	Ravi Anand	Finance	Pune
[NULL]	[NULL]	Testing	Noida
[NULL]	[NULL]	Marketing	Mathura

Explanation: As right join gives the matching rows and the rows that are present in the right table but not in the left table. Here in this example, we see that the department that contains no employee contains [NULL] values of emp_no and emp_name after performing the right join.

Applications of SQL RIGHT JOIN

• Allows to merge data from different tables in database.
• A RIGHT JOIN ensures that all records from the right table are included in the result, even if there are no corresponding matches in the left table
• Allows to look for missing values in one of the table. For example, combining customer and orders table allows to look at customers and their orders.
• Useful in finding patterns and relations between data.

Key Takeaways:

• Right JOIN allows to join two table, keeping all the data or right table and only matching data of left table.
• Right JOIN is a type of outer join in SQL.
• It allows us to deal with missing values in database and also helps in analyzing relationships between data.