如何组合查询展示指定值并排除特定值（课程检索场景）

Got it, let's tackle this query problem using the bb table with ccode, course, and name fields. The goal is to find all courses that Lennart has taught, but that Alan and Kjell have never taught. Here are a few solid approaches, including the IN/NOT IN combination you're looking for:

Method 1: Direct IN + NOT IN with Subqueries #

This aligns perfectly with your request to use IN and NOT IN together:

SELECT DISTINCT course
FROM bb
WHERE name = 'Lennart'
AND course NOT IN (
    -- Subquery pulls all courses taught by Alan or Kjell
    SELECT course
    FROM bb
    WHERE name IN ('Alan', 'Kjell')
);

Breakdown: #

• The outer query first grabs every course Lennart has taught, using DISTINCT to avoid duplicate course entries (in case he taught the same course multiple times).
• The subquery generates a list of all courses that either Alan or Kjell have taught.
• The NOT IN clause filters out any of Lennart's courses that appear in that Alan/Kjell list, leaving only the courses that meet your criteria.

Method 2: Grouping with HAVING Clause #

If you prefer a more aggregated approach, this works just as well:

SELECT course
FROM bb
WHERE name IN ('Lennart', 'Alan', 'Kjell')
GROUP BY course
HAVING COUNT(DISTINCT name) = 1
AND MAX(name) = 'Lennart';

Breakdown: #

• We first narrow down the dataset to only records involving the three professors we care about.
• Grouping by course lets us analyze each course's instructor list.
• COUNT(DISTINCT name) = 1 ensures the course was only taught by one person, and MAX(name) = 'Lennart' confirms that person is Lennart.

A Quick Note on NULL Safety #

If your course field could ever be NULL, the NOT IN method can behave unexpectedly (since NULL comparisons return unknown). For a more robust solution, use NOT EXISTS instead:

SELECT DISTINCT course
FROM bb lennart_courses
WHERE lennart_courses.name = 'Lennart'
AND NOT EXISTS (
    SELECT 1
    FROM bb other_profs
    WHERE other_profs.course = lennart_courses.course
    AND other_profs.name IN ('Alan', 'Kjell')
);

NOT EXISTS checks for the absence of matching records, which isn't affected by NULL values in the course field.

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