SQL课程作业疑问：基于Type_id关联Em_Type表筛选目标Em_num

Hey Joey, let's work through your problem and get that query right! First, let's recap your requirements to make sure we're on the same page:

• Pull Em_num from Em_Sum where Em_before is 4/5/6 AND Em_after is exactly 6
• Only include employees who have a Type_id of 1/2/3 in the Em_Type table

What's Off with Your Current Query? #

Your use of FULL JOIN is the main issue here. A FULL JOIN returns all records from both tables, even if there's no matching Em_num between them. That means you might end up with rows where Em_Type data is NULL (if the employee is only in Em_Sum) or Em_Sum data is NULL (if the employee is only in Em_Type)—neither of these fit your needs, since you want employees that exist in both tables and meet both sets of conditions.

Better Solutions #

Here are three solid approaches to get the results you want:

1. Inner Join (Most Straightforward)

An INNER JOIN only returns rows where there's a matching Em_num in both tables, which aligns perfectly with your requirement to filter employees present in both datasets.

SELECT es.Em_num
FROM Em_Sum es
INNER JOIN Em_Type et 
  ON es.Em_num = et.Em_num
WHERE et.Type_id IN (1, 2, 3)
  AND es.Em_before IN (4, 5, 6)
  AND es.Em_after = 6;

(Note: I used table aliases es and et to make the query cleaner—you can skip them if you prefer, but they make long queries easier to read.)

2. Subquery with IN

If you want to first narrow down the valid employees from Em_Type before checking Em_Sum, this approach makes the logic explicit:

SELECT Em_num
FROM Em_Sum
WHERE Em_before IN (4, 5, 6)
  AND Em_after = 6
  AND Em_num IN (
    SELECT Em_num
    FROM Em_Type
    WHERE Type_id IN (1, 2, 3)
  );

3. EXISTS Clause (Avoids Duplicates)

If an employee has multiple entries in Em_Type, INNER JOIN might return duplicate Em_num values. Using EXISTS checks for the existence of a matching record without duplicating results:

SELECT es.Em_num
FROM Em_Sum es
WHERE es.Em_before IN (4, 5, 6)
  AND es.Em_after = 6
  AND EXISTS (
    SELECT 1
    FROM Em_Type et
    WHERE et.Em_num = es.Em_num
      AND et.Type_id IN (1, 2, 3)
  );

All three of these will give you the exact set of Em_num values you're looking for—pick the one that makes the most sense for your database structure and readability preferences!

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