MySQL多表查询不存在记录：获取未完成课程的5名学生

Got it, let's work through this problem. First, I'll make some reasonable assumptions about your table setup since you didn't share the exact schema—typically, you'd have a students table holding student details, a linking table (let's say student_course_enrollments) that connects students to the courses they're taking (with columns like student_id and course_id), and the completed table that tracks which student-course pairs are finished.

Here are a few solid MySQL query approaches to fetch 5 such students:

1. Using NOT EXISTS (Recommended for Performance) #

This is usually the most efficient option, especially if you have indexes on completed.student_id and completed.course_id. The subquery checks if there's no matching entry in completed for the student's course:

SELECT s.*
FROM students s
JOIN student_course_enrollments sce ON s.id = sce.student_id
WHERE NOT EXISTS (
    SELECT 1
    FROM completed c
    WHERE c.student_id = s.id
      AND c.course_id = sce.course_id
)
LIMIT 5;

2. Using LEFT JOIN + IS NULL #

A more readable alternative that works by joining all student-course pairs and filtering out those that exist in completed:

SELECT s.*
FROM students s
JOIN student_course_enrollments sce ON s.id = sce.student_id
LEFT JOIN completed c 
    ON c.student_id = s.id 
    AND c.course_id = sce.course_id
WHERE c.id IS NULL  -- Use any non-null column from `completed` here
LIMIT 5;

3. Using NOT IN #

Note: Avoid this if completed has any NULL values in student_id or course_id—NOT IN will return zero results in that case. But if your data is clean, it's a concise option:

SELECT s.*
FROM students s
JOIN student_course_enrollments sce ON s.id = sce.student_id
WHERE (s.id, sce.course_id) NOT IN (
    SELECT student_id, course_id
    FROM completed
)
LIMIT 5;

Quick Tips: #

• Swap out students.id, student_course_enrollments, and column names with your actual table/column labels if they differ.
• If you want unique students (in case a student has multiple uncompleted courses), add DISTINCT to the select: SELECT DISTINCT s.*
• Indexes on the join columns will drastically speed up the query for large datasets—make sure students.id, student_course_enrollments.student_id, completed.student_id, and completed.course_id are indexed.

内容的提问来源于stack exchange，提问作者Önder Erol