SQL RIGHT JOIN – Syntax and Examples

SQL RIGHT JOIN

The SQL RIGHT JOIN clause returns all records from the right table (second table), and the matched records from the left table (first table). If there is no match, NULL values are returned for columns from the left table.

The RIGHT JOIN is useful when you want to include all entries from one table and match them with data from another table if it exists, prioritizing records from the right table.

In this tutorial, we will go through SQL RIGHT JOIN, its syntax, and how to use this join in SQL statements, with the help of well detailed examples.

---

Syntax of SQL RIGHT JOIN

The basic syntax of the SQL RIGHT JOIN clause is:

SELECT table1.column1, table2.column2, ...
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from each table.
• FROM: Specifies the first (left) table in the join operation.
• RIGHT JOIN: Specifies the second (right) table, which will be joined with the first table.
• ON: Specifies the condition for the join, which is the common column shared between the two tables.

Step-by-Step Examples with MySQL

We’ll go through examples demonstrating the RIGHT JOIN in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and grades tables with fields student_id, name, age, and subject.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age)
VALUES
('Alice', 14),
('Bob', 15),
('Charlie', 14);

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(1, 'Science', 'B'),
(2, 'Math', 'A'),
(2, 'History', 'C');

---

Examples: Using RIGHT JOIN in Queries

Now, let’s explore different scenarios of using SQL RIGHT JOIN with these tables.

---

1. Retrieving All Grades with Corresponding Students

To retrieve a list of all grades and the corresponding student names, including grades without a matching student record:

SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades
ON students.student_id = grades.student_id;

This query returns all grades with student names where available. For grades without a matching student, NULL is shown for the name column.

---

2. Filtering RIGHT JOIN Results with WHERE Clause

To retrieve only grades that do not have corresponding student records, use a WHERE clause to filter for NULL values:

SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades
ON students.student_id = grades.student_id
WHERE students.student_id IS NULL;

This query returns only grades that do not have matching students in the students table.

Reference: SQL WHERE Clause

---

3. RIGHT JOIN with Additional Conditions

To find all grades for “Math” with corresponding student names:

SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades
ON students.student_id = grades.student_id
WHERE grades.subject = 'Math';

This query returns all grades in “Math” with corresponding students. If any Math grade doesn’t have a matching student, NULL will be displayed for the name column.

---

FAQs for SQL RIGHT JOIN

1. What does the SQL RIGHT JOIN do?

The RIGHT JOIN returns all rows from the right table and matches from the left table. Rows in the right table without a match in the left table will have NULL values for columns from the left table.

2. How is RIGHT JOIN different from LEFT JOIN?

RIGHT JOIN includes all rows from the right table even without a match in the left table, while LEFT JOIN includes all rows from the left table and only matches from the right table.

3. Can RIGHT JOIN be used with multiple tables?

Yes, you can use RIGHT JOIN to join multiple tables by specifying additional RIGHT JOIN clauses and conditions.

4. Can RIGHT JOIN have conditions in the WHERE clause?

Yes, you can use the WHERE clause with RIGHT JOIN to filter the result set further, including conditions on columns from either table.

5. What happens if there is no match in RIGHT JOIN?

If there is no match, RIGHT JOIN returns NULL for columns from the left table for those rows from the right table without matches.