Your code is doing exactly what you are telling it to do!
<TL;DR>
There are three possible solutions to the problem outlined here:
add additional records with NULL entries to "force" the tables to JOIN,
rewrite the SQL (making it less performant - see performance analysis section at the end),
change the schema (normalisation) - this is optimal IMHO - better performance and a better respresentation of reality.
</TL;DR>
I adapted your schema to make it more legible for my tastes (the fiddle for the first part of this analysis is available here):
CREATE TABLE level_quiz
(
id INTEGER NOT NULL,
level_title VARCHAR (50) NOT NULL,
quiz_desc VARCHAR (200) NOT NULL,
overall_quest VARCHAR (250) NOT NULL
);
CREATE TABLE student_points
(
student_no INTEGER NOT NULL,
level_id INTEGER NOT NULL,
points INTEGER NULL, -- << have to make NULLable, see below
ts TIMESTAMP NULL -- << renamed timestamp to ts!
);
Two points to note:
declaring a field as INT(x) where x is a number is pointless unless you need ZEROFILL (or here & here) - plus LPAD will do the same thing - plus it makes your code non-portable (see below),
you should never use an SQL keyword (TIMESTAMP in this case) as a table or column name - it's bad for debugging, produces confusing error messages and is generally bad practice.
To make the results simpler, I've truncated fields as follows:
INSERT INTO level_quiz (id, level_title, quiz_desc, overall_quest)
VALUES
(1, 'Sets', 'The purpose of...', 'Suppose we have... '), -- << truncated strings
(2, 'Seqs', 'sequences desc...', 'overall question... '),
(3, 'Prop Logic', 'logic desc ...', 'overall quest... '),
(4, 'Pred Logic', 'pred desc 1 ...', 'predicase quest...');
and there are two extra records which I'll INSERT later on in my analysis.
INSERT INTO student_points (student_no, level_id, points, ts)
VALUES
(12345678, 1, 80, '2021-01-15 16:07:43'),
(12345678, 2, 25, '2021-01-13 17:15:10'),
(12345678, 3, 90, '2021-01-17 22:41:55'),
(12345678, 4, 90, '2021-01-17 22:41:55'),
(40204123, 1, 80, '2021-01-12 15:37:11'),
(40204123, 2, 75, '2021-01-12 15:38:06'),
(40204123, 3, 30, '2021-01-13 22:13:13'),
-- (40204123, 4, NULL, NULL), -- <<< -- see below what happens when this
-- record is inserted
(40213894, 1, 90, '2021-01-14 21:52:00'),
(40213894, 2, 95, '2021-01-17 22:42:50'),
(40213894, 4, 100, '2021-01-17 22:42:50');
-- (40213894, 4, NULL, NULL), -- <<< see below also
Now, your code:
SELECT *
FROM level_quiz
LEFT JOIN student_points
ON level_quiz.id = student_points.level_id
WHERE student_points.student_no = 40204123
OR student_points.student_no IS NULL -- <<-- Makes NO difference
Result (see fiddle for better formatting):
id level_title quiz_desc overall_quest student_no level_id points ts
1 Sets The purpose of... Suppose we have... 40204123 1 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 2 75 2021-01-12 15:38:06
3 Prop Logic logic desc ... overall quest... 40204123 3 30 2021-01-13 22:13:13
But, you only have 3 records - no corresponding NULL for student_no 40204123 for quiz level 4!
Now, when I obtain a strange result, my "go-to" reflex is to check what PostgreSQL does in the same situation. I have always found PostgreSQL to be superior in virtually every respect to MySQL.
So rather than rushing to report bugs to MySQL (good luck with that... - they have so many!), you should try checking on other servers - it's unlikely that a fundamental bug in something so basic as a LEFT JOIN would have gone undetected for long! The result is here and it can be seen that the data returned by PostgreSQL for the same query is identical!
So, what's going on?
Well, we'll now take a look at @nbk's answer here.
--
-- Solution proposed by nbk - NULLs in the result as desired!
--
SELECT
lq.id, lq.level_title, lq.quiz_desc, lq.overall_quest,
sp.student_no, sp.level_id, sp.points, sp.ts
FROM
level_quiz lq
LEFT JOIN
(
SELECT * FROM student_points
WHERE student_no = 40204123
) sp
ON lq.id = sp.level_id;
Result (better viewed on the fiddle):
id level_title quiz_desc overall_quest student_no level_id points ts
1 Sets The purpose of... Suppose we have... 40204123 1 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 2 75 2021-01-12 15:38:06
3 Prop Logic logic desc ... overall quest... 40204123 3 30 2021-01-13 22:13:13
4 Pred Logic pred desc 1 ... predicase quest... NULL NULL NULL NULL
So now, we apparently have the correct result - with NULLs for quiz level 4! However, now let's look at what happens when we run the same query for **2** students!
--
-- bb25's original SQL - with 2 students - but no NULLs in the result!
--
SELECT *
FROM level_quiz
LEFT JOIN student_points
ON level_quiz.id = student_points.level_id
WHERE student_points.student_no IN (40204123, 40213894)
OR student_points.student_no IS NULL -- << Makes NO difference!
Result:
id level_title quiz_desc overall_quest student_no level_id points ts
1 Sets The purpose of... Suppose we have... 40204123 1 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 2 75 2021-01-12 15:38:06
3 Prop Logic logic desc ... overall quest... 40204123 3 30 2021-01-13 22:13:13
1 Sets The purpose of... Suppose we have... 40213894 1 90 2021-01-14 21:52:00
2 Seqs sequences desc... overall question... 40213894 2 95 2021-01-17 22:42:50
4 Pred Logic pred desc 1 ... predicase quest... 40213894 4 100 2021-01-17 22:42:50
Unexpectedly there are no NULLs - we have quiz results for 1, 2 & 3 for student 40204123 and quizzes 1, 2 & 4 for student 40213894.
Next, we reexamine nbk's answer.
--
-- SQL proposed by nbk - with 2 students - but again no NULLs in the result!
--
SELECT
lq.id, lq.level_title, lq.quiz_desc, lq.overall_quest,
sp.student_no, sp.level_id, sp.points, sp.ts
FROM
level_quiz lq
LEFT JOIN
(
SELECT * FROM student_points
WHERE student_no IN (40204123, 40213894)
) sp
ON lq.id = sp.level_id;
Result:
id level_title quiz_desc overall_quest student_no level_id points ts
1 Sets The purpose of... Suppose we have... 40204123 1 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 2 75 2021-01-12 15:38:06
3 Prop Logic logic desc ... overall quest... 40204123 3 30 2021-01-13 22:13:13
1 Sets The purpose of... Suppose we have... 40213894 1 90 2021-01-14 21:52:00
2 Seqs sequences desc... overall question... 40213894 2 95 2021-01-17 22:42:50
4 Pred Logic pred desc 1 ... predicase quest... 40213894 4 100 2021-01-17 22:42:50
Again there are no NULLs anywhere to be seen! The result for @nbk's answer is identical to that of the OP's SQL
Solution 1: - Add some (2) records!
So, we do this:
INSERT INTO student_points VALUES
(40204123, 4, NULL, NULL), -- <<< NOW we INSERT these records!
(40213894, 3, NULL, NULL);
Now, we have records for all students for all quiz levels - but obviously there can't be points for levels the students haven't completed a level (points = NULL), nor can there be a TIMESTAMP for something that hasn't happened (ts = NULL)!
So, basically, bb25's (i.e. the OP's) SQL works for this scenario (as does nbk's) and both pieces of SQL work for two students as well as for one - so, adding these records can solve the issue!
I'm only showing the OP's original SQL here (for 2 students) - more is shown on the fiddle.
--
-- bb25 original SQL - 2 students - NULLs NOW in the result
--
SELECT *
FROM level_quiz
LEFT JOIN student_points
ON level_quiz.id = student_points.level_id
WHERE student_points.student_no IN (40204123, 40213894)
ORDER BY student_points.student_no, level_quiz.id;
Result (better viewed on fiddle):
id level_title quiz_desc overall_quest student_no level_id points ts
1 Sets The purpose of... Suppose we have... 40204123 1 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 2 75 2021-01-12 15:38:06
3 Prop Logic logic desc ... overall quest... 40204123 3 30 2021-01-13 22:13:13
4 Pred Logic pred desc 1 ... predicase quest... 40204123 4
1 Sets The purpose of... Suppose we have... 40213894 1 90 2021-01-14 21:52:00
2 Seqs sequences desc... overall question... 40213894 2 95 2021-01-17 22:42:50
3 Prop Logic logic desc ... overall quest... 40213894 3
4 Pred Logic pred desc 1 ... predicase quest... 40213894 4 100 2021-01-17 22:42:50
and now we do have NULL in the appropriate places.
Solution 2 - change the SQL to work with the original dataset:
A better solution might be to actually get the SQL to produce the desired data without having to add supplementary records - esp. records with NULLs - which many find problematic.
So, here I just perform a CROSS JOIN on student_no in the student_points table with the ids of the level_quiz table to get all the possible combinations of students with quiz...
So, first we DELETE the records which we inserted in order for Solution 1 to work.
DELETE FROM student_points WHERE points IS NULL;
and then run this SQL:
SELECT distinct sp1.student_no, t1.id
FROM student_points sp1
CROSS JOIN
(
SELECT distinct lq.id
FROM level_quiz lq
) AS t1
ORDER BY sp1.student_no, t1.id;
Result:
student_no id
12345678 1
12345678 2
12345678 3
12345678 4
40204123 1
40204123 2
40204123 3
40204123 4
40213894 1
40213894 2
40213894 3
40213894 4
12 rows
Then, we have to JOIN these records back to their original tables thus:
SELECT
t2.id,
SUBSTRING(lq2.level_title, 1, 6) AS "LT:", lq2.quiz_desc, lq2.overall_quest,
t2.student_no, COALESCE(sp2.points, 0) AS "Points:", sp2.ts
FROM
(
SELECT distinct sp1.student_no, t1.id
FROM student_points sp1
CROSS JOIN
(
SELECT distinct lq1.id
FROM level_quiz lq1
) AS t1
) AS t2
LEFT JOIN student_points sp2
ON t2.student_no = sp2.student_no
AND t2.id = sp2.level_id
JOIN level_quiz lq2
ON t2.id = lq2.id
WHERE t2.student_no IN (40204123, 40213894)
ORDER BY t2.student_no, t2.id;
Result:
id LT: quiz_desc overall_quest student_no Points: ts
1 Sets The purpose of... Suppose we have... 40204123 80 2021-01-12 15:37:11
2 Seqs sequences desc... overall question... 40204123 75 2021-01-12 15:38:06
3 Prop L logic desc ... overall quest... 40204123 30 2021-01-13 22:13:13
4 Pred L pred desc 1 ... predicase quest... 40204123 0
1 Sets The purpose of... Suppose we have... 40213894 90 2021-01-14 21:52:00
2 Seqs sequences desc... overall question... 40213894 95 2021-01-17 22:42:50
3 Prop L logic desc ... overall quest... 40213894 0
4 Pred L pred desc 1 ... predicase quest... 40213894 100 2021-01-17 22:42:50
We can see that we have 0 points for NULLs as a result of the COALESCE function, but that now our missing records have "reappeared".
Solution 3: Redesign the schema:
If, say, we had a student who hadn't take any quizzes (and recalling my student days, this is eminently possible!), how would we deal with this scenario?
We can improve the schema (fiddle here).
Relations (tables) are entities (things) - my (particularly brilliant) synopsis of relational theory! :-). Now, a quiz is a "thing" which implies that it must correspond to a relation (i.e. table) in our database of relations. A student is a "thing" also - so, a student table is required.
The "tricky" bit is this - the relationship between the student and the quiz is also a "thing" and hence, deserves to be a table! Entities such as these are called Associative Entities and their corresponding tables are called associative tables - but much more frequently joining or linking tables (in fact, there are 17 names for them on the link.
New Schema:
So, my own recommendation is that you do the following:
CREATE TABLE student
(
s_id INTEGER NOT NULL,
s_name VARCHAR (20) NOT NULL,
CONSTRAINT student_pk PRIMARY KEY (s_id)
);
CREATE TABLE quiz
(
q_id INTEGER NOT NULL,
q_title VARCHAR (50) NOT NULL,
CONSTRAINT ql_pk PRIMARY KEY (q_id)
);
CREATE TABLE student_score
(
ss_s_id INTEGER NOT NULL,
ss_q_id INTEGER NOT NULL,
score INTEGER NOT NULL,
ts TIMESTAMP NOT NULL,
CONSTRAINT sp_pk PRIMARY KEY (ss_s_id, ss_q_id),
CONSTRAINT sp_s_no_fk FOREIGN KEY (ss_s_id) REFERENCES student (s_id),
CONSTRAINT sp_ql_id FOREIGN KEY (ss_q_id) REFERENCES quiz (q_id)
);
This answer is becoming rather long, so I'll just give the final SQL here (some intermediate steps are shown in the fiddle) :
SELECT
q.q_id, q.q_title,
s.s_id, s.s_name, COALESCE(ss.score, 0) AS score
FROM quiz q
CROSS JOIN student s
LEFT JOIN student_score ss
ON ss.ss_s_id = s.s_id
AND ss.ss_q_id = q.q_id
ORDER BY s.s_id, q.q_id;
Result (note the 4 0s for student 4!):
q_id q_title s_id s_name score
1 Quiz 1 12345678 Student1_name 80
2 Quiz 2 12345678 Student1_name 25
3 Quiz 3 12345678 Student1_name 90
4 Quiz 4 12345678 Student1_name 90
1 Quiz 1 40204123 Student2_name 80
2 Quiz 2 40204123 Student2_name 75
3 Quiz 3 40204123 Student2_name 30
4 Quiz 4 40204123 Student2_name 0
1 Quiz 1 40213894 Student3_name 90
2 Quiz 2 40213894 Student3_name 95
3 Quiz 3 40213894 Student3_name 0
4 Quiz 4 40213894 Student3_name 100
1 Quiz 1 98765432 Student4_name 0
2 Quiz 2 98765432 Student4_name 0
3 Quiz 3 98765432 Student4_name 0
4 Quiz 4 98765432 Student4_name 0
Performance analysis:
Using MySQL 8's EXPLAIN ANALYZE functionality, we see that the working SQL using the old schema produces the following plan (see fiddle here):
EXPLAIN
-> Sort: t2.student_no, t2.id (actual time=0.184..0.185 rows=8 loops=1)
-> Stream results (cost=32.42 rows=320) (actual time=0.133..0.170 rows=8 loops=1)
-> Left hash join (sp2.level_id = lq2.id), (sp2.student_no = t2.student_no) (cost=32.42 rows=320) (actual time=0.125..0.148 rows=8 loops=1)
-> Nested loop inner join (cost=5.85 rows=32) (actual time=0.082..0.100 rows=8 loops=1)
-> Table scan on lq2 (cost=0.65 rows=4) (actual time=0.005..0.015 rows=4 loops=1)
-> Index lookup on t2 using <auto_key2> (id=lq2.id) (actual time=0.001..0.002 rows=2 loops=4)
-> Materialize (cost=4.60 rows=8) (actual time=0.020..0.021 rows=2 loops=4)
-> Table scan on <temporary> (actual time=0.000..0.001 rows=8 loops=1)
-> Temporary table with deduplication (cost=4.60 rows=8) (actual time=0.062..0.063 rows=8 loops=1)
-> Inner hash join (no condition) (cost=4.60 rows=8) (actual time=0.046..0.049 rows=24 loops=1)
-> Table scan on t1 (cost=1.48 rows=4) (actual time=0.000..0.001 rows=4 loops=1)
-> Materialize (cost=0.65 rows=4) (actual time=0.021..0.022 rows=4 loops=1)
-> Table scan on <temporary> (actual time=0.000..0.001 rows=4 loops=1)
-> Temporary table with deduplication (cost=0.65 rows=4) (actual time=0.016..0.017 rows=4 loops=1)
-> Table scan on lq1 (cost=0.65 rows=4) (actual time=0.004..0.008 rows=4 loops=1)
-> Hash
-> Filter: (sp1.student_no in (40204123,40213894)) (cost=1.25 rows=2) (actual time=0.010..0.016 rows=6 loops=1)
-> Table scan on sp1 (cost=1.25 rows=10) (actual time=0.005..0.014 rows=10 loops=1)
-> Hash
-> Table scan on sp2 (cost=0.16 rows=10) (actual time=0.015..0.026 rows=10 loops=1)
The same SQL using PostgreSQL's EXPLAIN (ANALYZE, BUFFERS, COSTS, TIMING) functionality shows a very complex plan (see bottom of this fiddle).
The fiddle for the SQL with the revised schema is following (see bottom of this):
EXPLAIN
-> Nested loop left join (cost=2.05 rows=4) (actual time=0.018..0.031 rows=4 loops=1)
-> Table scan on q (cost=0.65 rows=4) (actual time=0.011..0.015 rows=4 loops=1)
-> Single-row index lookup on ss using PRIMARY (ss_s_id=40204123, ss_q_id=q.q_id) (cost=0.28 rows=1) (actual time=0.003..0.003 rows=1 loops=4)
and just checking the PostgreSQL one here:
QUERY PLAN
Hash Left Join (cost=14.64..46.31 rows=540 width=188) (actual time=0.077..0.083 rows=4 loops=1)
Hash Cond: ((s.s_id = ss.ss_s_id) AND (q.q_id = ss.ss_q_id))
Buffers: shared hit=5
-> Nested Loop (cost=0.15..28.97 rows=540 width=184) (actual time=0.032..0.035 rows=4 loops=1)
Buffers: shared hit=3
-> Index Scan using student_pk on student s (cost=0.15..8.17 rows=1 width=62) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: (s_id = 40204123)
Buffers: shared hit=2
-> Seq Scan on quiz q (cost=0.00..15.40 rows=540 width=122) (actual time=0.008..0.009 rows=4 loops=1)
Buffers: shared hit=1
-> Hash (cost=14.37..14.37 rows=8 width=12) (actual time=0.027..0.027 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Buffers: shared hit=2
-> Bitmap Heap Scan on student_score ss (cost=4.21..14.37 rows=8 width=12) (actual time=0.017..0.019 rows=3 loops=1)
Recheck Cond: (ss_s_id = 40204123)
Heap Blocks: exact=1
Buffers: shared hit=2
-> Bitmap Index Scan on sp_pk (cost=0.00..4.21 rows=8 width=0) (actual time=0.006..0.007 rows=3 loops=1)
Index Cond: (ss_s_id = 40204123)
Buffers: shared hit=1
Planning Time: 0.254 ms
Execution Time: 0.188 ms
22 rows
So, the motto appears to be that a well-normalised schema produces a) - the correct results, or at least the correct results more easily and is more performant! Good to know!
