SQL RIGHT JOIN

SQL RIGHT JOIN Statement

The SQL RIGHT JOIN statement is used to return all records from the right table and the matched records from the left table. If there is no match, the result is NULL from the left table.

---

Syntax

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

The RIGHT JOIN statement has the following components:

• column1, column2, ...: The columns to be retrieved.
• table1: The name of the first table (left table).
• table2: The name of the second table (right table).
• table1.column = table2.column: The condition to join the tables.

---

Example SQL RIGHT JOIN Statement Queries

Let's look at some examples of SQL RIGHT JOIN statement queries:

1. Basic RIGHT JOIN Example

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
RIGHT JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from the employees_2 table and the matched records from the employees_1 table. If there is no match, the result is NULL from the employees_1 table.

2. RIGHT JOIN with WHERE Clause

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
RIGHT JOIN employees_2
ON employees_1.id = employees_2.id
WHERE employees_2.department_name IS NOT NULL;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from the employees_2 table and the matched records from the employees_1 table where the department_name is not null.

---

Full Example

Let's go through a complete example that includes creating tables, inserting data, and querying the tables with the RIGHT JOIN statement.

Step 1: Creating Tables

This step involves creating new tables named employees_1 and employees_2 to store employee data.

CREATE TABLE employees_1 (
    id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

CREATE TABLE employees_2 (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

In this example, we create two tables: employees_1 with columns for id and first_name, and employees_2 with columns for id and department_name.

Step 2: Inserting Data into the Tables

This step involves inserting some sample data into the employees_1 and employees_2 tables.

INSERT INTO employees_1 (id, first_name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Jim');

INSERT INTO employees_2 (id, department_name)
VALUES (1, 'Engineering'), (2, 'HR'), (4, 'Marketing');

Here, we insert data into the employees_1 and employees_2 tables.

Step 3: Querying the Tables with RIGHT JOIN

This step involves selecting the data from the employees_1 and employees_2 tables and joining them using the RIGHT JOIN statement.

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
RIGHT JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves all the rows from the employees_2 table and the matched rows from the employees_1 table. The result will be:

id  first_name  department_name
--- ----------- ---------------
1   John        Engineering
2   Jane        HR
NULL NULL       Marketing

Conclusion

The SQL RIGHT JOIN statement is a powerful tool for returning all records from the right table and the matched records from the left table. Understanding how to use the RIGHT JOIN statement and its syntax is essential for effective data retrieval and analysis in SQL databases.