Incorrect nested query output when use table name-CodePudding

I have encountered a very odd situation with MySQL (replicated with same results in PSQL) when a query return the right result when is used a table name alias but wrong result when is used full table name.

Please see below for full details:

We have the following 2 table COURSE and STUDENT
COURSE table has 2 records
STUDENT table have 12 records
(I left out the FKs and any other attribute for example replication)

A live sample or code below

-- Table structure for course
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `courseNo` int NOT NULL,
  `courseTitle` varchar(20) NOT NULL,
  PRIMARY KEY (`courseNo`)
);

-- Records of course
INSERT INTO `course` VALUES (1000, 'Software Engineering');
INSERT INTO `course` VALUES (1001, 'Internet Systems');

-- Table structure for student
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `studentid` int NOT NULL,
  `courseNo` int NOT NULL,
  PRIMARY KEY (`studentid`)
);

-- Records of student
INSERT INTO `student` VALUES (3000, 1000);
INSERT INTO `student` VALUES (3001, 1001);
INSERT INTO `student` VALUES (3002, 1000);
INSERT INTO `student` VALUES (3003, 1000);
INSERT INTO `student` VALUES (3004, 1001);
INSERT INTO `student` VALUES (3005, 1000);
INSERT INTO `student` VALUES (3006, 1001);
INSERT INTO `student` VALUES (3007, 1001);
INSERT INTO `student` VALUES (3008, 1001);
INSERT INTO `student` VALUES (3009, 1000);
INSERT INTO `student` VALUES (3010, 1001);
INSERT INTO `student` VALUES (3011, 1001);

If I run the following nested query

SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
FROM course c,
     student s
WHERE EXISTS
        ( SELECT *
         FROM student
         WHERE s.courseNo = c.courseNo )
GROUP BY c.courseNo;

The result is correct 5 students are enrolled in course 1000, and 7 in course 1001.

Course ID	Course Title	Enrolled
1000	Software Engineering	5
1001	Internet Systems	7

However if I run the following nested query

Select course.courseNo as 'Course ID',
       course.courseTitle as 'Course Title',
       COUNT(student.studentID) AS 'Enrolled'
From course,
     student
WHERE EXISTS
        ( SELECT *
         FROM student
         WHERE student.courseNo = course.courseNo )
Group by course.courseNo;

The result is

Course ID	Course Title	Enrolled
1000	Software Engineering	12
1001	Internet Systems	12

They are exactly the same queries. The only difference is the alias in the table names.
If I do as a normal JOIN both versions are returning the correct result.

If anyone can explain this behavior of the nested query will be highly appreciated.

CodePudding user response：

Binding starts from most local.

So in the second query student.courseNo binds to student from the subquery (as this is more local) whereas in the first query s.courseNo binds to student (aliased s) from the outer query.

So no, the queries are not "exactly the same".

To illustrate that you can use an alias in the subquery too. Then the second query would become

Select c.courseNo as 'Course ID',
       c.courseTitle as 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
From course c,
     student s
WHERE EXISTS
        ( SELECT *
         FROM student s2
         WHERE s2.courseNo = c.courseNo )
Group by c.courseNo;

and the first

Select c.courseNo as 'Course ID',
       c.courseTitle as 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
From course c,
     student s
WHERE EXISTS
        ( SELECT *
         FROM student s2
         WHERE s.courseNo = c.courseNo )
Group by c.courseNo;

(Notice that courseNo in the subquery references s2 in the second and s in the first query.)

CodePudding user response：

your last query makes a cross join beteenn student and courses

From course,
 student

And then takes this cross join(try the result of that and count it yourself) and uses that to compare the data to a new student table not the one from the "FROM" clause and that are all rows

Use a proper JOIN to avoid confusion, with the same table name.

As the frist query you use the where compare the data from both tables in the3 "FROM" Clause and doesn't use the student table in the EXISTS select

SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
FROM course c INNER JOIN 
     student s
ON  s.courseNo = c.courseNo 
GROUP BY c.courseNo;

Course ID | Course Title         | Enrolled
--------: | :------------------- | -------:
     1000 | Software Engineering |        5
     1001 | Internet Systems     |        7

EXPLAIN SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(s.studentID) AS 'Enrolled'
FROM course c INNER JOIN 
     student s
ON  s.courseNo = c.courseNo 
GROUP BY c.courseNo;

id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                     
-: | :---------- | :---- | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :-----------------------------------------
 1 | SIMPLE      | c     | null       | ALL  | PRIMARY       | null | null    | null |    2 |   100.00 | Using temporary                           
 1 | SIMPLE      | s     | null       | ALL  | null          | null | null    | null |   12 |    10.00 | Using where; Using join buffer (hash join)

EXPLAIN
SELECT course.courseNo AS 'Course ID',
       course.courseTitle AS 'Course Title',
       COUNT(student.studentID) AS 'Enrolled'
FROM course  INNER JOIN 
     student 
ON  student.courseNo = course.courseNo 
GROUP BY course.courseNo;

id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                     
-: | :---------- | :------ | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :-----------------------------------------
 1 | SIMPLE      | course  | null       | ALL  | PRIMARY       | null | null    | null |    2 |   100.00 | Using temporary                           
 1 | SIMPLE      | student | null       | ALL  | null          | null | null    | null |   12 |    10.00 | Using where; Using join buffer (hash join)

Select course.courseNo as 'Course ID',
       course.courseTitle as 'Course Title',
       COUNT(student.studentID) AS 'Enrolled'
From course,
     student
WHERE EXISTS
        ( SELECT *
         FROM student s
         WHERE courseNo = course.courseNo )
Group by course.courseNo;

Course ID | Course Title         | Enrolled
--------: | :------------------- | -------:
     1001 | Internet Systems     |       12
     1000 | Software Engineering |       12

db<>fiddle here