Oracle SQL中FROM子查询失效及关联数据丢失问题排查

Hey there, let's break down the problems with your SQL queries and get them working correctly:

1. Why the Original Query Failed to Filter Non-Student Groups Properly #

Your original query tried to filter for student_group='Student' by adding a subquery s.id in(SELECT id from rg_student where student_group='Student') to each LEFT JOIN's ON clause. This approach has two big issues:

• Redundancy: You're running the same subquery multiple times, which wastes database resources.
• Logical Misplacement: LEFT JOIN's ON clause controls which rows from the joined table are matched, not which rows from the main table (rg_student) are kept. While this subquery might filter out some mismatches, it doesn't cleanly exclude non-Student rows from your final result set.

2. Why Simplifying the Query Lost contact_3 and contact_4 Data #

Looking at your simplified FROM clause, there's a critical typo that's breaking your joins:

In the join for table b, you wrote a.addr_code='P2' instead of b.addr_code='P2'!

This mistake tells the database to match rows where the first parent address (a) has code P2, not the second parent address (b). Unsurprisingly, this returns no valid rows for b—and if you repeated this typo for contact_3/contact_4 (using a.addr_code='P3' instead of c.addr_code='P3', etc.), that's exactly why those fields are missing data.

The Corrected Query #

Here's a cleaned-up, working version that filters correctly and preserves all parent contact data (including contact_3 and contact_4):

SELECT
    s.id AS student_id,
    -- Replace with the actual contact fields you need (phone, email, etc.)
    a.phone AS contact_1_phone,
    a.email AS contact_1_email,
    b.phone AS contact_2_phone,
    b.email AS contact_2_email,
    c.phone AS contact_3_phone,
    c.email AS contact_3_email,
    d.phone AS contact_4_phone,
    d.email AS contact_4_email
FROM rg_student s
-- Join for contact_1 (P1)
LEFT JOIN rg_addr a
    ON s.id = a.id
    AND a.addr_code = 'P1'
    AND a.rg_active = 'Y'
-- Join for contact_2 (P2)
LEFT JOIN rg_addr b
    ON s.id = b.id
    AND b.addr_code = 'P2'  -- Fixed: use b's addr_code, not a's
    AND b.rg_active = 'Y'
-- Join for contact_3 (P3)
LEFT JOIN rg_addr c
    ON s.id = c.id
    AND c.addr_code = 'P3'
    AND c.rg_active = 'Y'
-- Join for contact_4 (P4)
LEFT JOIN rg_addr d
    ON s.id = d.id
    AND d.addr_code = 'P4'
    AND d.rg_active = 'Y'
-- Filter main table to only current students (clean and efficient)
WHERE s.student_group = 'Student';

Key Improvements: #

• Single, Efficient Filter: The WHERE clause directly filters the rg_student table to only include current students, avoiding redundant subqueries.
• Correct Join Conditions: Each rg_addr join uses its own table's addr_code (e.g., b.addr_code='P2'), ensuring you match the right parent contact.
• Preserves All Rows: LEFT JOIN ensures that even if a student doesn't have contact_3 or contact_4 data, their row stays in the result set (with NULL values for missing fields) instead of being dropped.

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