MySQL incorrect nested query output when use table name

Question

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 bellow 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.

Answer 1

They are absolutely not the same query.

The first one refers only to the outer tables in its inner WHERE, but the second has a correlation instead

---

In the first query:

FROM course c,
     student s

This is a cross-join of both tables. Generally you should avoid this syntax, and favour explicit join syntax, this is really the root cause of your problems.

---

WHERE EXISTS
        ( SELECT *

There must be at least one row from the subquery. Note that SELECT * is the same as SELECT 1 or SELECT NULL here.

---

         FROM student
         WHERE s.courseNo = c.courseNo )

The WHERE only refers to the outer tables. And once you use explicit join syntax, it's effectively the same as

FROM course c
JOIN student s ON s.courseNo = c.courseNo
WHERE EXISTS ( SELECT 1
         FROM student)

The EXISTS just checks that at least one row exists anywhere in the student table, which is normally true anyway. It is unnecessary for your results, you can drop the whole WHERE

---

In the second query:

From course,
     student

Again you use old-style comma joins, when you shouldn't, and you have no alias

---

WHERE EXISTS
        ( SELECT *
         FROM student
         WHERE student.courseNo = course.courseNo )

Here the inner student table also has no alias. So when you write WHERE student.courseNo = course.courseNo you are correlating the inner student to the outer course table.

But the outer comma cross-join remains in place, and has no correlation. So you are cross-joining everything together, then ensuring that each course has at least one student. This is simply bogus.

---

Your query should really be this

SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       COUNT(*) AS 'Enrolled'  -- COUNT(notNullValue) is the same as COUNT(*)
FROM course c
JOIN student s ON s.courseNo = c.courseNo
GROUP BY
    c.courseNo,
    c.courseTitle;  -- always group by all non-aggregated columns

Alternatively

SELECT c.courseNo AS 'Course ID',
       c.courseTitle AS 'Course Title',
       s.Enrolled
FROM course c
JOIN (
    SELECT s.courseNo,
           COUNT(*) AS 'Enrolled'  -- COUNT(notNullValue) is the same as COUNT(*)    
    FROM student s
    GROUP BY
        s.courseNo
) s ON s.courseNo = c.courseNo;

Answer 2

In the second one, student.courseNo uses the column from the table in the subselect. You shouldn't use the subselect in none of the queries.