🎓 A1Learner
🔍

SQL Right and Full Join

A right join is a join that keeps every row from the right table. A full join keeps every row from both tables.

A right join keeps the products

Products form the right table here. The Mouse Pad has no orders, so its order id comes back NULL.

SELECT orders.order_id, products.name
FROM orders
RIGHT JOIN products ON orders.product_id = products.product_id
ORDER BY products.product_id, orders.order_id;

A right join keeps the customers

Customers form the right table in this query. Hana Sato has no orders, so her order id comes back NULL.

SELECT customers.name, orders.order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY customers.customer_id, orders.order_id;

A full join keeps the products whole

The full join keeps every product row. The Mouse Pad stays in the result with a NULL order id.

SELECT products.name, orders.order_id
FROM products
FULL JOIN orders ON products.product_id = orders.product_id
ORDER BY products.product_id, orders.order_id;

Example

SELECT customers.name AS customer, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id, orders.order_id;

Key points

  • RIGHT JOIN: a join that keeps every row from the table named after it
  • FULL JOIN: a join that keeps every row from both tables
  • Columns from an unmatched side come back as NULL.
  • Use RIGHT JOIN to keep the second table whole.
  • Use FULL JOIN to keep both tables whole in one result.

In this example, the full join keeps every customer and every order. Every order holds a customer id that matches, so no order row needs NULL columns. Hana Sato has no orders, so her row carries a NULL order id.

Note

SQLite learned RIGHT JOIN and FULL JOIN in version 3.39. A LEFT JOIN with the tables swapped does the right join's job on older engines.

Try it Yourself

Edit the code, then press Run.

Running is simulated · DB Fiddle

Exercise

What does a full join keep in its result?