右外连接（Right Outer Join）重复数据问题及SQL查询修复求助

Let’s walk through why you’re hitting these issues and how to fix them—plus how to stop duplicates from popping up in the first place.

First, let’s look at your original query:

select id, descr from table1 a right join table2 b on a.id = b.id

Why descr is returning NULL #

A RIGHT JOIN pulls all rows from the right table (table2) and only matching rows from the left table (table1). So if there are rows in table2 where id doesn’t exist in table1, there’s no descr value to pull—hence the NULLs. That’s expected behavior for this join type, but it might not be what you actually need.

Why you’re getting duplicate rows #

Duplicates almost always come from duplicate id values in one (or both) of your tables:

• If table2 has 3 rows with the same id, and table1 has 1 matching row, the join will spit out 3 copies of that table1 row.
• If table1 has multiple rows for the same id, that’ll also multiply your results when joined to table2.

Quick Fixes for Your Query #

Let’s adjust the query based on what you likely want:

1. Get only matching rows (no NULLs)

If you don’t need rows from table2 that have no match in table1, swap to an INNER JOIN—this only returns rows where id exists in both tables, so descr will never be NULL:

select a.id, a.descr 
from table1 a 
inner join table2 b on a.id = b.id

2. Keep all table2 rows but remove duplicates

If you need to retain all table2 rows, use DISTINCT to eliminate duplicates, or aggregate data if you need to combine multiple descr values:

-- Remove duplicates with DISTINCT
select distinct b.id, a.descr 
from table1 a 
right join table2 b on a.id = b.id

-- Or combine multiple descr values (e.g., concatenate) and replace NULLs
select b.id, COALESCE(GROUP_CONCAT(a.descr SEPARATOR ', '), 'No description') as descr
from table1 a 
right join table2 b on a.id = b.id
group by b.id

The COALESCE here replaces NULLs with a user-friendly message—totally optional but helpful for readability.

Root Cause Fix: Stop Duplicates Before They Happen #

To avoid this problem long-term, you need to enforce data integrity in your tables:

• Add a Primary Key/Unique Constraint: Ensure id is unique in at least one table. For example, if table1 is a lookup table for descriptions, set id as its primary key to guarantee one row per id:

  ALTER TABLE table1 ADD PRIMARY KEY (id);
  

• Clean Existing Duplicates: If duplicates already exist, remove them first. Here’s how to delete duplicates from table2 (keeping the first occurrence):

  DELETE FROM table2
  WHERE id IN (
    SELECT id FROM (
      SELECT id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY id) as row_num
      FROM table2
    ) t WHERE row_num > 1
  );
  

• Use Foreign Keys: Link table2’s id to table1’s id with a foreign key constraint. This ensures table2 can only have id values that exist in table1 (if that’s your goal), which also eliminates orphaned rows that cause NULLs:

  ALTER TABLE table2 ADD CONSTRAINT fk_table2_table1 FOREIGN KEY (id) REFERENCES table1(id);
  

• Validate Data Upfront: Make sure any app inserting data checks for existing id values before adding new rows—this prevents accidental duplicates at the source.

内容的提问来源于stack exchange，提问作者Giri Dharan