Tasted itNOT EXISTS

There are three tables, which record the student, the course, and what course the student elects. How do you use NOT EXISTS to identify the student who elects all courses?

To avoid making up awkward student and course names, I simplified the structure of the tables in the problem sets, leaving only their ID columns. The construction sentences are as follows

- the student table
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));- schedule
CREATE TABLE `course` (
  `id` INT NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`));-- Elective relations
CREATE TABLE `elective` (
  `student_id` INT NOT NULL.`course_id` INT NOT NULL,
  FOREIGN KEY (`student_id`) REFERENCES `student`(`id`),
  FOREIGN KEY (`course_id`) REFERENCES `course`(`id`));Copy the code

They also need to be stuffed with some sample data

INSERT INTO `student` (`id`) VALUES (1), (2), (3), (4), (5);
INSERT INTO `course` (`id`) VALUES (1), (2);
INSERT INTO `elective` (`course_id`.`student_id`) VALUES (1.1), (2.1), (1.2), (2.3), (2.5), (1.5);
Copy the code

Obviously, only students with values 1 and 5 in the ID column are enrolled in all courses. The SQL statement written in NOT EXISTS is as follows

SELECT * 
FROM   `student` 
WHERE  NOT EXISTS (SELECT * 
                   FROM   `course` 
                   WHERE  NOT EXISTS (SELECT * 
                                      FROM   `elective` 
                                      WHERE  `student`.`id` = 
                                             `elective`.`student_id` 
                                             AND `course`.`id` = 
                                     `elective`.`course_id`)); 
Copy the code

The result after running in DBEaver is

The ids of the two students who took all the courses were correctly identified.

How to understand doubleNOT EXISTS

When first asked this question, I did NOT really understand the meaning of NOT EXISTS. It wasn’t until I looked at the EXISTS documentation that I had an Epiphany about the ABOVE SQL.

My understanding is to convert the double NOT EXISTS to a three-tier loop. Select * from the student table where there are no courses in the student table where there are no courses in the student table.

A three-layer loop would look something like this

for (const student of students) {
  // Are there any courses not taken by the student
  let existSuchCourse = false;
  for (const course of courses) {
    let existSuchElective = false;
    for (const elective of electives) {
      if (elective.student_id === student.id && elective.course_id === course.id) {
        existSuchElective = true;
        break; }}If the existSuchElective rate is still false after crossing the records of the imagetable, it means that there is indeed a course that has no elective record
    // Then it means "there is at least one course that the student currently traversed is not related to".
    if(! existSuchElective) { existSuchCourse =true;
      break; }}// If there are no "unenrolled" courses, the student has taken all of them
  if(! existSuchCourse) {console.log(student); }}Copy the code

NOT EXISTSThe nature of

It is possible to have MySQL explicitly tell you how double NOT EXISTS, even if you don’t force it. The result of explaining the above SQL with EXPLAIN is shown below

MySQL’s EXPLAIN command documentation explains how to interpret execution plans

EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. This means that MySQL reads a row from the first table, then finds a matching row in the second table, and then in the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

For example, MySQL reads a row from the student table, a row from the course table, and finally a row from the elective table to see if the WHERE clause can be satisfied. If you can, print the row read from the student table. The select_type lines 2 and 3 above are both DEPENDENT SUBQUERY, indicating that they depend on the “outer” query context — the WHERE clause depends on rows read from student and course.

Seems to be the same as the triple cycle.

Afterword.

I have never read or used a feature as “high-level” as NOT EXISTS in business code — let alone NOT EXISTS, and even subqueries are rare. After all, “serious Internet companies” only use MySQL as a sister, as a key-value database with sophisticated query capabilities (laughs)

I am more likely to write subquery-based solutions intuitively than dual NOT EXISTS

SELECT * 
FROM   `student` 
WHERE  `id` IN (SELECT `student_id` 
                FROM   `elective` 
                GROUP  BY `student_id` 
                HAVING( Count(0=))2); 
Copy the code

I even think someone could read rows from the database into memory and use application layer code to find out who took all the classes!

The full text.

Read the original