SQL RIGHT OUTER JOIN: Syntax, Logic & Conversion to LEFT JOIN

The RIGHT OUTER JOIN (or RIGHT JOIN) is the symmetrical mirror of the LEFT JOIN. It returns all rows from the right table (Table B), along with matching rows from the left table (Table A). If no match exists in the left table, NULL values are returned for Table A's columns.

Hands-on Query: Finding Departments with Zero Students

-- RIGHT JOIN preserves all departments, even Arts (dept_id 30) with 0 students
SELECT 
    d.dept_id,
    d.dept_name,
    s.student_id,
    s.first_name
FROM students s
RIGHT JOIN departments d ON s.dept_id = d.dept_id;

Converting RIGHT JOIN to LEFT JOIN (Industry Standard)

In professional engineering teams, coding standards prefer LEFT JOIN for readability. To convert any RIGHT JOIN, simply swap the order of the tables:

-- Identical query written cleanly as a LEFT JOIN:
SELECT 
    d.dept_id,
    d.dept_name,
    s.student_id,
    s.first_name
FROM departments d
LEFT JOIN students s ON d.dept_id = s.dept_id;

Next, let us learn how to retain all rows from both tables simultaneously using SQL FULL OUTER JOIN!

Frequently Asked Questions (FAQ)