SQL RIGHT JOIN

Learn how to combine records from two tables while keeping all records from the right table using SQL RIGHT JOIN.

What is RIGHT JOIN?

The RIGHT JOIN statement returns all records from the right table and the matching records from the left table. When a matching record does not exist in the left table, the left-side columns contain NULL.

However, SQLite does not support RIGHT JOIN in older SQLite versions. To make every Test My Code example work reliably in SQLite, RIGHT JOIN can be rewritten by reversing the table order and using LEFT JOIN.

Basic RIGHT JOIN Concept

In databases that support RIGHT JOIN, the following concept keeps every record from the right table. The SQLite-compatible version reverses the tables and uses LEFT JOIN.

CREATE TABLE students (
  student_id INTEGER,
  student_name TEXT
);

CREATE TABLE courses (
  course_id INTEGER,
  course_name TEXT,
  student_id INTEGER
);

INSERT INTO students VALUES
  (1, 'Aarav'),
  (2, 'Meera'),
  (3, 'Kabir');

INSERT INTO courses VALUES
  (101, 'Physics', 1),
  (102, 'Chemistry', 2),
  (103, 'Biology', 5);

SELECT
  c.course_name,
  s.student_name
FROM courses AS c
LEFT JOIN students AS s
  ON c.student_id = s.student_id
ORDER BY c.course_id;

RIGHT JOIN with Courses and Instructors

A RIGHT JOIN keeps every record from the right table, even when there is no matching record in the left table. SQLite can achieve the same result by reversing the tables and using a LEFT JOIN.

CREATE TABLE course_assignments (
  assignment_id INTEGER,
  instructor_id INTEGER,
  course_name TEXT
);

CREATE TABLE instructors (
  instructor_id INTEGER,
  instructor_name TEXT
);

INSERT INTO course_assignments VALUES
  (101, 1, 'Data Science'),
  (102, 2, 'Web Development'),
  (103, 1, 'Database Systems');

INSERT INTO instructors VALUES
  (1, 'Anika'),
  (2, 'Vivek'),
  (3, 'Sana'),
  (4, 'Rohan');

SELECT
  i.instructor_name,
  c.course_name
FROM instructors AS i
LEFT JOIN course_assignments AS c
  ON i.instructor_id = c.instructor_id
ORDER BY i.instructor_id, c.assignment_id;

Keeping All Products

A RIGHT JOIN can be useful when every product from the right-side table must appear, even when there is no corresponding order.

CREATE TABLE orders (
  order_id INTEGER,
  product_id INTEGER,
  customer_name TEXT
);

CREATE TABLE products (
  product_id INTEGER,
  product_name TEXT
);

INSERT INTO orders VALUES
  (501, 10, 'Anaya'),
  (502, 20, 'Rahul'),
  (503, 10, 'Kavya');

INSERT INTO products VALUES
  (10, 'Wireless Mouse'),
  (20, 'Desk Lamp'),
  (30, 'USB Hub');

SELECT
  p.product_name,
  o.customer_name
FROM products AS p
LEFT JOIN orders AS o
  ON p.product_id = o.product_id
ORDER BY p.product_id;

Finding Right-Side Records Without a Match

By checking for NULL values on the left-side table, you can find records from the preserved table that have no matching record.

CREATE TABLE payments (
  payment_id INTEGER,
  customer_id INTEGER,
  amount INTEGER
);

CREATE TABLE customers (
  customer_id INTEGER,
  customer_name TEXT
);

INSERT INTO payments VALUES
  (101, 1, 500),
  (102, 2, 750);

INSERT INTO customers VALUES
  (1, 'Isha'),
  (2, 'Arjun'),
  (3, 'Maya'),
  (4, 'Rohan');

SELECT
  c.customer_name
FROM customers AS c
LEFT JOIN payments AS p
  ON c.customer_id = p.customer_id
WHERE p.payment_id IS NULL
ORDER BY c.customer_id;

RIGHT JOIN with Multiple Matching Rows

If multiple records in the left table match one record in the right table, the right-side record can appear multiple times.

CREATE TABLE artists (
  artist_id INTEGER,
  artist_name TEXT
);

CREATE TABLE songs (
  song_id INTEGER,
  artist_id INTEGER,
  song_title TEXT
);

INSERT INTO artists VALUES
  (1, 'Mira'),
  (2, 'Karan'),
  (3, 'Tara');

INSERT INTO songs VALUES
  (101, 1, 'Morning Light'),
  (102, 1, 'Open Sky'),
  (103, 2, 'New Road');

SELECT
  a.artist_name,
  s.song_title
FROM artists AS a
LEFT JOIN songs AS s
  ON a.artist_id = s.artist_id
ORDER BY a.artist_id, s.song_id;

RIGHT JOIN with COUNT()

You can combine the RIGHT JOIN concept with COUNT() to count related records while preserving every record from the right-side table.

CREATE TABLE instructors (
  instructor_id INTEGER,
  instructor_name TEXT
);

CREATE TABLE classes (
  class_id INTEGER,
  instructor_id INTEGER,
  class_name TEXT
);

INSERT INTO instructors VALUES
  (1, 'Neel'),
  (2, 'Pia'),
  (3, 'Arman');

INSERT INTO classes VALUES
  (101, 1, 'Photography'),
  (102, 1, 'Editing'),
  (103, 2, 'Drawing');

SELECT
  i.instructor_name,
  COUNT(c.class_id) AS class_count
FROM instructors AS i
LEFT JOIN classes AS c
  ON i.instructor_id = c.instructor_id
GROUP BY i.instructor_id, i.instructor_name
ORDER BY i.instructor_id;

RIGHT JOIN with Multiple Conditions

Multiple conditions can be used in the ON clause. The SQLite-compatible approach is to put the table that must be preserved first and use LEFT JOIN.

CREATE TABLE deliveries (
  delivery_id INTEGER,
  warehouse_id INTEGER,
  item_name TEXT,
  quantity INTEGER
);

CREATE TABLE warehouses (
  warehouse_id INTEGER,
  warehouse_name TEXT
);

INSERT INTO deliveries VALUES
  (101, 1, 'Laptop', 12),
  (102, 1, 'Monitor', 4),
  (103, 2, 'Keyboard', 15),
  (104, 3, 'Mouse', 3);

INSERT INTO warehouses VALUES
  (1, 'North Warehouse'),
  (2, 'South Warehouse'),
  (3, 'East Warehouse'),
  (4, 'West Warehouse');

SELECT
  w.warehouse_name,
  d.item_name,
  d.quantity
FROM warehouses AS w
LEFT JOIN deliveries AS d
  ON w.warehouse_id = d.warehouse_id
  AND d.quantity >= 10
ORDER BY w.warehouse_id;

RIGHT JOIN with COALESCE()

When no matching left-side record exists, its values are NULL. The COALESCE() function can replace NULL with a more useful value.

CREATE TABLE ratings (
  rating_id INTEGER,
  movie_id INTEGER,
  score INTEGER
);

CREATE TABLE movies (
  movie_id INTEGER,
  movie_title TEXT
);

INSERT INTO ratings VALUES
  (1, 10, 8),
  (2, 20, 9),
  (3, 10, 7);

INSERT INTO movies VALUES
  (10, 'Skyline'),
  (20, 'The Journey'),
  (30, 'Hidden World');

SELECT
  m.movie_title,
  COALESCE(r.score, 0) AS score
FROM movies AS m
LEFT JOIN ratings AS r
  ON m.movie_id = r.movie_id
ORDER BY m.movie_id;

RIGHT JOIN vs LEFT JOIN

Feature	LEFT JOIN	RIGHT JOIN
Preserved table	Left table	Right table
Matching rows	Included	Included
Unmatched preserved rows	Included	Included
SQLite-compatible approach	LEFT JOIN	Reverse tables + LEFT JOIN

Advantages of RIGHT JOIN

Keeps every record from the right table.
Includes matching records from the left table.
Useful when the right table is the primary table of interest.
Can help identify unmatched records.
Can be combined with filtering and aggregate functions.

Best Practices

Remember that RIGHT JOIN support depends on the database system.
For SQLite compatibility, reverse the table order and use LEFT JOIN.
Use clear table aliases when joining multiple tables.
Use IS NULL to find unmatched records.
Place JOIN-specific conditions in the ON clause when appropriate.
Use COALESCE() when NULL values need a default value.

The RIGHT JOIN keeps every record from the right table and adds matching information from the left table. Since SQLite compatibility is important for the interactive editor, the examples above use the equivalent LEFT JOIN approach with the tables reversed.

Ctrl + Enter to run SQL code Esc to close editor

🧪 Test Your SQL Code

Edit the SQL code on the left and click “Run Code” to see the result on the right.

📝 SQL Code

👁️ Preview (query result)

Click “Run Code” to see the result here.