SQL RIGHT JOIN

What does SQL RIGHT JOIN do?

RIGHT JOIN returns all rows from the right table and the matching rows from the left table; non-matching left rows appear as NULL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL RIGHT JOIN: PostgreSQL, MySQL, SQL Server, Oracle, MariaDB, Snowflake, Redshift, DB2. Not supported in SQLite or Google BigQuery.

SQL RIGHT JOIN Full Explanation

RIGHT JOIN (also called RIGHT OUTER JOIN) is one of the ANSI-standard outer join operators. It combines each row of the right-hand table with matching rows from the left-hand table based on a join condition supplied in an ON clause or USING clause. If no match exists in the left table, the query still returns a row, filling the selected columns from the left table with NULLs. Conceptually, it is the mirror image of LEFT JOIN: swapping the table order in a LEFT JOIN produces the same result as a RIGHT JOIN.Key points:- Only rows from the right table are guaranteed to appear.- The keyword OUTER is optional and purely syntactic.- RIGHT JOINs can be chained with other joins and filtered with WHERE, HAVING, and window functions.- Performance depends on indexes on the join columns; OUTER joins are usually executed with hash or merge join strategies.- Some engines (notably SQLite) do not support RIGHT JOIN at all; use LEFT JOIN with table order reversed instead.

SQL RIGHT JOIN Syntax

SELECT select_list
FROM left_table
RIGHT [OUTER] JOIN right_table
     ON left_table.key = right_table.key;

SQL RIGHT JOIN Parameters

left_table (table or subquery) - produces the columns that may be NULL if no match is found
right_table (table or subquery) - every row from this table appears at least once in the result
ON clause (boolean expression) - defines how rows from both tables are matched
USING clause (alternative) - column list - shorthand when the join keys share the same name

Example Queries Using SQL RIGHT JOIN

-- Example 1: List all departments, even those without employees
SELECT d.department_id,
       d.department_name,
       e.employee_id
FROM   employees e
RIGHT JOIN departments d
       ON e.department_id = d.department_id;

-- Example 2: Show latest orders, keeping products that never sold
SELECT p.product_id,
       p.product_name,
       o.order_id,
       o.order_date
FROM   orders o
RIGHT OUTER JOIN products p
       ON o.product_id = p.product_id
WHERE  o.order_date >= CURRENT_DATE - INTERVAL '30 days';

Expected Output Using SQL RIGHT JOIN

Each result set contains every row from the right table (departments, products)
If no matching row exists in the left table, the columns from the left table (employees, orders) are NULL

Use Cases with SQL RIGHT JOIN

Reporting dimensions that must always appear (e.g., list of all departments regardless of staffing)
Auditing missing relationships (find products without sales)
Creating completeness checks by counting NULLs from the left side
Replacing LEFT JOIN when table order is fixed by legacy code or readability

Common Mistakes with SQL RIGHT JOIN

Forgetting that RIGHT JOIN preserves the right table, then wondering why NULLs appear on the left columns
Assuming RIGHT JOIN is faster than LEFT JOIN; they are logically equivalent when table order is swapped
Using RIGHT JOIN in SQLite, which raises a syntax error
Applying WHERE filters that inadvertently convert the outer join into an inner join (e.g., filtering on a column from the nullable side without IS NULL check)

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What is the difference between RIGHT JOIN and LEFT JOIN?

RIGHT JOIN preserves all rows from the right table, filling NULLs for non-matching rows on the left. LEFT JOIN does the opposite.

Does RIGHT JOIN work in SQLite?

No. SQLite lacks RIGHT JOIN. Use LEFT JOIN with the tables swapped.

When should I prefer RIGHT OUTER JOIN?

Use it when the natural reading of the query lists the retained table on the right or when refactoring legacy SQL that already relies on that order.

Can RIGHT JOIN hurt performance?

Generally no. Performance relies on indexes and the query planner, not the direction of the outer join.