SQLite RIGHT JOIN

Summary: in this tutorial, you will learn how to use the SQLite RIGHT JOIN to retrieve data from two related tables.

Introduction to the SQLite RIGHT JOIN clause

In SQLite, the RIGHT JOIN clause allows you to combine rows from two tables based on a related column between them.

The RIGHT JOIN clause returns all rows from the right table and matching rows from the left table. For non-matching rows in the left table, it uses NULL values.

Here’s the syntax of the RIGHT JOIN clause:

SELECT
  select_list
FROM
  table1
  RIGHT JOIN table2 ON table1.column_name1 = table2.column_name2;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• table1 and table2 are the left and right tables respectively.
• column_name1 and column_name2 are the related column that links the two tables. Note that they may have the same name.

The RIGHT JOIN clause will return all rows from the right table (table2) and matching rows from the left table (table1).

For rows from the right table (table2) that do not have matching rows from the left table( table1), it uses NULL values for columns of the left table (table1).

If table1 and table2 have the same column_name, you can use the USING syntax:

SELECT
  select_list
FROM
  table1
  RIGHT JOIN table2 USING (column_name);Code language: SQL (Structured Query Language) (sql)

Notice that USING (column_name) and ON table1.column_name = table2.column_name are the equivalent.

To find rows from the right table (table2) that does not have matching rows in the left table (table1), you can check if the column_name IS NULL in a WHERE clause as follows:

SELECT
  select_list
FROM
  table1
  RIGHT JOIN table2 USING (column_name)
WHERE
  column_name IS NULL;Code language: SQL (Structured Query Language) (sql)

SQLite RIGHT JOIN clause example

Let’s take an example of using the RIGHT JOIN clause.

First, create new tables called departments and employees:

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY(department_id) 
        REFERENCES departments(department_id) ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

In these tables, the employees table has the department_id column that references the department_id column of the departments table. This relationship is established via a foreign key constraint.

Second, insert rows into the departments and employees tables:

INSERT INTO departments (department_name ) 
VALUES ('HR'),
       ('IT');

INSERT INTO employees (employee_name , department_id ) 
VALUES 
   ('John', 1),
   ('Jane', 2),
   ('Alice', NULL);Code language: SQL (Structured Query Language) (sql)

In this example, we’re using only three rows for clarity purposes.

Third, use the RIGHT JOIN to retrieve all employees and their respective departments:

SELECT
  employee_name,
  department_name
FROM
  departments
  RIGHT JOIN employees ON employees.department_id = departments.department_id;Code language: SQL (Structured Query Language) (sql)

Output:

employee_name  department_name
-------------  ---------------
John           HR
Jane           IT
Alice          nullCode language: SQL (Structured Query Language) (sql)

Since both employees and departments tables have the department_id column, you can use the USING clause:

SELECT
  employee_name,
  department_name
FROM
  departments
  RIGHT JOIN employees USING (department_id);Code language: SQL (Structured Query Language) (sql)

It should return the same result set of the query that uses the ON clause.

Finally, find all employees who do not have a department using the IS NULL condition in a WHERE clause:

SELECT
  employee_name,
  department_name
FROM
  departments
  RIGHT JOIN employees ON employees.department_id = departments.department_id
WHERE
  department_name IS NULL;Code language: SQL (Structured Query Language) (sql)

Output:

employee_name  department_name
-------------  ---------------
Alice          nullCode language: SQL (Structured Query Language) (sql)

Summary

• Use SQLite RIGHT JOIN clause to combine rows from two tables based on a related column.