SQL, case expressions returns different results. Why?-CodePudding

The two sqls retruns different results because I use different case expressions，What's the deep difference behind them?

select course_name,
case when course_id in (select course_id from OpenCourses where month =200706) then 'o' else 'x' end as JUNE,
case when course_id in (select course_id from OpenCourses where month =200707) then 'o' else 'x' end as JULY,
case when course_id in (select course_id from OpenCourses where month =200708) then 'o' else 'x' end as AUG
from CourseMaster

select course_name,
case when course_id  =200706 then 'o' else 'x' end as JUNE,
case when course_id  =200707 then 'o' else 'x' end as JULY,
case when course_id  =200708 then 'o' else 'x' end as AUG
from CourseMaster

Create Tables

CREATE TABLE CourseMaster
(course_id   INTEGER PRIMARY KEY,
 course_name VARCHAR(32) NOT NULL);

INSERT INTO CourseMaster VALUES(1, 'English');
INSERT INTO CourseMaster VALUES(2, 'Math');
INSERT INTO CourseMaster VALUES(3, 'Pyhsics');
INSERT INTO CourseMaster VALUES(4, 'Chemistry');

CREATE TABLE OpenCourses
(month       INTEGER ,
 course_id   INTEGER ,
    PRIMARY KEY(month, course_id));

INSERT INTO OpenCourses VALUES(200706, 1);
INSERT INTO OpenCourses VALUES(200706, 3);
INSERT INTO OpenCourses VALUES(200706, 4);
INSERT INTO OpenCourses VALUES(200707, 4);
INSERT INTO OpenCourses VALUES(200708, 2);
INSERT INTO OpenCourses VALUES(200708, 4);

CodePudding user response：

Looking at the contents of the CourseMaster table, the only values for course_id are 1, 2, 3, 4. Meanwhile in your second select with case, you check if course_id is 200706, 200707, 200708. That condition is not true on any of the records in CourseMaster, ergo all your case statements evaluate to the else branch, which is 'x'.

The two selects are not equivalent, logically. The first one checks if the course record you are looking at is listed in OpenCourses for the given month, the second one compares the course_id to the year-month value. The second select doesn't touch the OpenCourses table at all. The desired outcome of the SQL is not stated in the question, but the first select makes sense while the second one does not. The second SQL compares apples and oranges, in a sense - course_id's come from a different box of values (that's a metaphor, not a technical term) than your year-month values.