SQL Right Join

A RIGHT JOIN returns every row from the right table and fills in matching left-table columns, using NULL where no match is found.

What RIGHT JOIN does

A RIGHT JOIN (also written RIGHT OUTER JOIN) is the mirror image of a LEFT JOIN. It keeps every row from the second, or right, table and attaches matching data from the left table where it exists. Rows in the right table without a match still appear, with the left table's columns set to NULL. This is useful when the right table is the one you must fully account for.

Every order, with its customer if known

SELECT Customers.CustomerName, Orders.OrderID, Orders.Amount
FROM Customers
RIGHT JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID;

With Orders as the right table, all four orders appear. Orders 101, 102, and 103 match their customers normally. Order 104 references CustomerID 5, which has no matching customer, so its CustomerName comes back as NULL. Customers 3 and 4 never appear, because they belong to the left table and placed no orders.

CustomerNameOrderIDAmount
Anita Rao101250
Anita Rao10290
Ben Carter103400
NULL104150
Note: Any RIGHT JOIN can be rewritten as a LEFT JOIN simply by swapping the order of the two tables. Many teams standardize on LEFT JOIN for readability, but RIGHT JOIN is perfectly valid and sometimes reads more naturally.

Spotting orphaned rows

Just as a LEFT JOIN can find left-table rows with no match, a RIGHT JOIN can surface right-table rows that reference something missing. Order 104 is an orphan: it points to a customer that does not exist. Filtering for the NULL left side reveals such data quality problems.

Orders pointing to a missing customer

SELECT Orders.OrderID, Orders.CustomerID
FROM Customers
RIGHT JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerID IS NULL;
  • The table listed after RIGHT JOIN is the right table, and all of its rows are preserved.
  • Unmatched left-table columns are returned as NULL.
  • RIGHT JOIN and LEFT JOIN are interchangeable if you swap the table order.
  • Not every database engine supports RIGHT JOIN; a few older ones only offer LEFT JOIN.

Keep Going

SQL Interview QuestionsPractice real questions asked for SQLDatabase Administrator SalarySee what Database Administrator roles pay in the marketAuto Apply for JobsApply to open roles related to SQL and tweak your resume