RIGHT JOIN
In this page:
What is a RIGHT JOIN?
A RIGHT JOIN keeps all rows from the right table regardless of whether a match exists on the left. It matches them with rows from the left table where possible. If there is no match, the left side returns NULL, mirroring how LEFT JOIN treats the right side.
Example: What is a RIGHT JOIN?
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1), (102, 2);
SELECT customers.name, orders.order_id
FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;RIGHT JOIN vs LEFT JOIN Equivalence
Every RIGHT JOIN can be rewritten as an equivalent LEFT JOIN. You just swap the table order in your query. Most style guides prefer sticking to LEFT JOIN everywhere for consistency, since RIGHT JOIN is used far less often and can confuse readers.
Example: RIGHT JOIN vs LEFT JOIN Equivalence
CREATE TABLE customers (customer_id INT, name TEXT);
CREATE TABLE orders (order_id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
-- RIGHT JOIN
SELECT customers.name FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
-- Equivalent LEFT JOIN (tables swapped)
SELECT customers.name FROM orders LEFT JOIN customers ON customers.customer_id = orders.customer_id;Identifying Orphans with RIGHT JOIN
You can find rows in the right table that have no matching records on the left side, such as products that have never been ordered. To do this, check for left-side NULLs after the join, the mirror image of the LEFT JOIN orphan-finding pattern.
Example: Identifying Orphans with RIGHT JOIN
CREATE TABLE products (product_id INT, name TEXT);
CREATE TABLE orders (order_id INT, product_id INT);
INSERT INTO products VALUES (1, 'Keyboard'), (2, 'Mouse');
INSERT INTO orders VALUES (101, 1);
SELECT products.name
FROM orders RIGHT JOIN products ON orders.product_id = products.product_id
WHERE orders.order_id IS NULL;Right Joining Multiple Tables
You can chain multiple RIGHT JOIN statements to gather information from several tables at once. This gathers information while preserving the rightmost table's records, ensuring nothing from that final table gets dropped.
Example: Right Joining Multiple Tables
CREATE TABLE categories (category_id INT, name TEXT);
CREATE TABLE products (product_id INT, category_id INT, name TEXT);
CREATE TABLE orders (order_id INT, product_id INT);
INSERT INTO categories VALUES (1, 'Electronics');
INSERT INTO products VALUES (10, 1, 'Keyboard');
INSERT INTO orders VALUES (101, 10);
SELECT categories.name, products.name, orders.order_id
FROM orders
RIGHT JOIN products ON orders.product_id = products.product_id
RIGHT JOIN categories ON products.category_id = categories.category_id;RIGHT JOIN Aggregate Computations
Combining a RIGHT JOIN with aggregation allows you to display total figures, including zeroes for unmatched rows, such as showing every product's sales count even for products that have never sold.
Example: RIGHT JOIN Aggregate Computations
CREATE TABLE products (product_id INT, name TEXT);
CREATE TABLE orders (order_id INT, product_id INT);
INSERT INTO products VALUES (1, 'Keyboard'), (2, 'Mouse');
INSERT INTO orders VALUES (101, 1);
SELECT products.name, COUNT(orders.order_id) AS sales_count
FROM orders RIGHT JOIN products ON orders.product_id = products.product_id
GROUP BY products.name;Chapter Quiz — Complete all 10 topics to unlock
0/10 topics done
Complete these topics first: