MySQL RIGHT JOIN: Keep All Rows from the Right Table

MySQL RIGHT JOIN returns every row from the right table and any matching rows from the left table. If no left-table row satisfies the ON condition, the selected left-table columns are returned as NULL.

SELECT p.p_id AS catalog_p_id,
       p.product AS catalog_product,
       s.sale_id,
       s.p_id AS sale_p_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
ORDER BY s.sale_id;

Every row from sales_v2 is preserved. Sales whose p_id is not present in products_v2 still appear, with NULL values for the selected product-table columns.

RIGHT JOIN Syntax Top ↑

SELECT left_table.column1,
       right_table.column2
FROM left_table
RIGHT JOIN right_table
  ON left_table.join_column = right_table.join_column;

The table written after RIGHT JOIN is the preserved table. Every one of its rows remains in the result even when the left table has no match.

Products and Sales Example Top ↑

The existing Plus2net sample uses products_v2 as the left table and sales_v2 as the right table.

SQL dump for products, sales and customers →

SELECT p.p_id AS catalog_p_id,
       p.product AS catalog_product,
       p.price,
       s.sale_id,
       s.c_id,
       s.p_id AS sale_p_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
ORDER BY s.sale_id;

Understanding the RIGHT JOIN Result Top ↑

Sales IDs 10, 11 and 12 do not have matching product IDs in products_v2. Because sales_v2 is the right table, those rows remain visible and the product-table columns become NULL.

Rewrite RIGHT JOIN as LEFT JOIN Top ↑

This RIGHT JOIN:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.p_id,
       s.qty
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id;

can be written as this LEFT JOIN by reversing the tables:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.p_id,
       s.qty
FROM sales_v2 AS s
LEFT JOIN products_v2 AS p
  ON p.p_id = s.p_id;

Both preserve every sales_v2 row. The second form is often easier to read because the preserved table appears first.

RIGHT JOIN with WHERE Top ↑

A WHERE clause filters the result after the join.

Filter by a right-table product ID Top ↑

SELECT p.p_id AS catalog_p_id,
       p.product AS catalog_product,
       s.sale_id,
       s.p_id AS sale_p_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
WHERE s.p_id = 2
ORDER BY s.sale_id;

This returns the three sales rows whose sales-side p_id is 2.

Filter by quantity Top ↑

SELECT p.product AS catalog_product,
       s.sale_id,
       s.product AS sold_product,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
WHERE s.qty = 1
ORDER BY s.sale_id;

Because qty belongs to the preserved right table, an unmatched sale such as the pendrive row can still be returned when its quantity is 1.

Find Right-table Rows with No Match Top ↑

To find sales whose product ID has no corresponding row in the product table, test the left-side primary key for NULL:

SELECT s.sale_id,
       s.p_id,
       s.product,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
WHERE p.p_id IS NULL
ORDER BY s.sale_id;

This is the RIGHT JOIN version of the anti-join pattern. Test a left-side column that cannot be NULL for a genuine match, such as the product primary key.

See SQL NULL values.

Return Only Matching Rows Top ↑

You can remove the unmatched right-side rows with:

SELECT p.p_id,
       p.product,
       s.sale_id,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
WHERE p.p_id IS NOT NULL
ORDER BY s.sale_id;

However, if the real requirement is simply "only rows that match in both tables," an INNER JOIN communicates that intent more directly.

ON vs WHERE in RIGHT JOIN Top ↑

Just as with LEFT JOIN, moving a condition between ON and WHERE can change which unmatched rows survive.

Filter left-table matches but preserve every right row Top ↑

SELECT p.product,
       p.price,
       s.sale_id,
       s.product AS sold_product,
       s.qty
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
 AND p.price > 70
ORDER BY s.sale_id;

All sales remain because the right table is preserved. A sale whose product fails the price condition behaves like an unmatched sale for this join and receives NULL product columns.

Filter the completed result Top ↑

SELECT p.product,
       p.price,
       s.sale_id,
       s.product AS sold_product,
       s.qty
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
WHERE p.price > 70
ORDER BY s.sale_id;

The WHERE condition rejects rows where p.price is NULL, so unmatched sales disappear. For that filter, the outer-join behavior is partly lost.

One-to-many Matches Top ↑

RIGHT JOIN preserves every right row, but it can still create several output rows from one right row if the ON condition matches several left rows.

For example, if the left table accidentally contains two product rows with the same join key, one sales row could match both and appear twice. This usually indicates that the relationship or expected uniqueness should be reviewed.

Likewise, repeated product values in the output are normal when several separate sales rows reference the same product. Do not use DISTINCT merely to hide valid one-to-many results.

NULL Join Keys Top ↑

An equality condition such as:

ON p.p_id = s.p_id

does not match NULL to NULL, because ordinary equality involving NULL does not evaluate to true.

If a preserved right-table row contains a NULL join key, it can still appear in the RIGHT JOIN result, but the left-table columns will be NULL because no equality match is found.

RIGHT JOIN More than Two Tables Top ↑

RIGHT JOIN can participate in a query with several tables, but mixed outer-join direction can become difficult to read. Use explicit aliases and parentheses/logical ordering carefully.

For example, preserve every sale while attaching both product and customer information:

SELECT s.sale_id,
       s.product AS sold_product,
       p.product AS catalog_product,
       c.customer,
       s.qty,
       s.store
FROM products_v2 AS p
RIGHT JOIN sales_v2 AS s
  ON p.p_id = s.p_id
LEFT JOIN customers_v2 AS c
  ON s.c_id = c.c_id
ORDER BY s.sale_id;

The first join preserves all sales. The second LEFT JOIN also starts from those sales and adds customer details when they exist.

RIGHT vs LEFT, INNER and CROSS JOIN Top ↑

Performance and Indexing Top ↑

• Use compatible datatypes for both sides of the join condition.
• Primary and UNIQUE keys are indexed automatically; frequently joined reference columns may also benefit from indexes.
• Outer-join semantics should be correct before performance tuning. Moving filters only to "make the query faster" can change the result.
• Explicitly select the columns the application needs rather than using SELECT *.
• Use EXPLAIN to inspect important join plans.
• Unexpected row multiplication usually comes from relationship cardinality or an incomplete ON condition.
• The optimizer may transform equivalent join forms internally, so choose RIGHT JOIN vs reversed LEFT JOIN primarily for correctness and readability, then measure performance if the query matters.

Common RIGHT JOIN Mistakes Top ↑

Forgetting which table is preserved Top ↑

RIGHT JOIN preserves the table written on the right side of the join operator.

Describing the result only as "common rows plus the second table" Top ↑

It is more precise to say that every right-table row is preserved and every matching left-row combination is attached.

Using SELECT * across joined tables Top ↑

Explicit columns avoid duplicate names such as two different p_id or product fields.

Putting a left-table filter in WHERE and accidentally removing unmatched right rows Top ↑

A condition such as WHERE p.price > 70 rejects NULL product rows after the join. Put it in ON when every right-table row must remain.

Using RIGHT JOIN when a reversed LEFT JOIN would be easier to read Top ↑

The two forms can express the same result. Choose the orientation that makes the preserved table and subsequent joins easiest to understand.

Using RIGHT JOIN + IS NOT NULL when only matches are required Top ↑

An INNER JOIN normally communicates a match-only requirement more clearly.

Adding DISTINCT to hide repeated matches Top ↑

Check the table relationship and ON condition first. Repeated values may represent valid separate sales rows.

Video Tutorial Top ↑

Related Tutorials and SQL Dump Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: