MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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,
  ts         TIMESTAMP NULL  -- << renamed timestamp to ts!
);
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...');

SELECT * FROM level_quiz;



id
level_title
quiz_desc
overall_quest


1
Sets
The purpose of...
Suppose we have...

2
Seqs
sequences desc...
overall question...

3
Prop Logic
logic desc    ...
overall quest...

4
Pred Logic
pred desc 1   ...
predicase quest...


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
--
-- bb25 original SQL - 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 = 40204123 
OR student_points.student_no IS NULL  -- <<-- Makes NO difference


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


--
-- Solution proposed by nbk - a NULL 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 = 40204123
 ) sp
 ON lq.id = sp.level_id;


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


--
-- 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!


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

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...
40213894
4
100
2021-01-17 22:42:50


--
-- Solution proposed by nbk - with 2 students - but 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;


id
level_title
quiz_desc
overall_quest
student_no
level_id
points
ts


1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

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...
40213894
4
100
2021-01-17 22:42:50


--
-- simple CROSS JOIN
--

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
CROSS JOIN student_points sp
WHERE sp.student_no = 40204123  -- try without the WHERE clause!
ORDER BY sp.student_no, lq.id, sp.level_id;
--
-- You can JOIN on whatever criteria you want - this is **ALL** the data you can get out
-- of this table for student_no = 40204123 by doing a CROSS JOIN

-- Notice that you can **only** ever have records with student_no = 40204123 (4 x 3)


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

1
Sets
The purpose of...
Suppose we have...
40204123
2
75
2021-01-12 15:38:06

1
Sets
The purpose of...
Suppose we have...
40204123
3
30
2021-01-13 22:13:13

2
Seqs
sequences desc...
overall question...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40204123
2
75
2021-01-12 15:38:06

2
Seqs
sequences desc...
overall question...
40204123
3
30
2021-01-13 22:13:13

3
Prop Logic
logic desc    ...
overall quest...
40204123
1
80
2021-01-12 15:37:11

3
Prop Logic
logic desc    ...
overall quest...
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
1
80
2021-01-12 15:37:11

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
2
75
2021-01-12 15:38:06

4
Pred Logic
pred desc 1   ...
predicase quest...
40204123
3
30
2021-01-13 22:13:13


--
-- See the results of the query below for student_no = 40204123
--

1 Sets	The purpose of  Suppose we have	                40204123	1	80	2021-01-12 15:37:11
2 sequences	        sequences desc	overall  2	40204123	2	75	2021-01-12 15:38:06
3 Propositional Logic	logic desc	overall quest 	40204123	3	30	2021-01-13 22:13:13
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 Sets	The purpose of  Suppose we have	                40204123	1	80	2021-01-12 ' at line 5

--
-- "Forcing a result"  <<-- Meaningless! You have all the fields in the first table
--  with everything in the second as NULL! And, see result below! You have no
--  student_no to go with your NULL results from the student_points table.
--
--
SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, COALESCE(tab.s_points, 0), tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp WHERE sp.student_no = 40204123
) tab
ON l.id = tab.s_l_id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
COALESCE(tab.s_points, 0)
s_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
0
null


-- With 2 students! 
--
--
-- NOW, you've "lost" the NULLs for BOTH students who should have them - 1 NULL
-- for student no. = 40204123 for level_quiz.id = 3 and 1 NULL for student no. =
-- 40213894 for level_quiz.id = 4
--

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    s.student_no AS "s_no", s.level_id AS "s_l_id", 
    s.points AS "s_points", s.ts AS "s_ts"
  FROM student_points s WHERE s.student_no IN (40204123, 40213894) -- Where have the NULLs gone?
) tab
ON l.id = tab.s_l_id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_ts


1
Sets
The purpose of...
Suppose we have...
40213894
1
90
2021-01-14 21:52:00

1
Sets
The purpose of...
Suppose we have...
40204123
1
80
2021-01-12 15:37:11

2
Seqs
sequences desc...
overall question...
40213894
2
95
2021-01-17 22:42:50

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...
40213894
4
100
2021-01-17 22:42:50


--
--             ADD
--
--           TWO RECORDS TO
-- 
--     THE student_points TABLE!!!!!!!!!
--
INSERT INTO student_points VALUES
(40204123, 4, NULL, NULL),     -- <<<  NOW we INSERT these records! 
(40213894, 3, NULL, NULL);
--
--
--
--    Now, we obtain VALID results!!!!!
--
--
--
-- bb25 original SQL - 1 student - a 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 = 40204123 
ORDER BY student_points.student_no, level_quiz.id;


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
null
null


--
-- bb25 original SQL - 2 students - NULLs NOW in the result
--
EXPLAIN ANALYZE
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;


EXPLAIN


-> Sort: student_points.student_no, level_quiz.id  (actual time=0.099..0.100 rows=8 loops=1)
    -> Stream results  (cost=2.66 rows=2) (actual time=0.056..0.075 rows=8 loops=1)
        -> Inner hash join (level_quiz.id = student_points.level_id)  (cost=2.66 rows=2) (actual time=0.049..0.060 rows=8 loops=1)
            -> Table scan on level_quiz  (cost=0.15 rows=4) (actual time=0.005..0.011 rows=4 loops=1)
            -> Hash
                -> Filter: (student_points.student_no in (40204123,40213894))  (cost=1.45 rows=2) (actual time=0.020..0.028 rows=8 loops=1)
                    -> Table scan on student_points  (cost=1.45 rows=12) (actual time=0.013..0.025 rows=12 loops=1)



--
-- @nbk's answer for 1 record
-- 
-- This makes MUCH more sense! For 1 record.
--

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp 
  WHERE sp.student_no = 40204123
) tab
ON l.id = tab.s_l_id
ORDER BY tab.s_no, l.id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_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
null
null


--
-- @nbk's answer for 2 records
--
-- This also makes MUCH more sense! For 2 (or more) records.
--
-- NOW, searching for any/all students with *_NO_* points for a given level_quiz.id
-- works and we can make sense of the NULLs

SELECT 
  l.id AS "l_id", l.level_title AS "l_title", 
  l.quiz_desc AS "l_q_desc", l.overall_quest AS "l_o_quest",
  tab.s_no, tab.s_l_id, tab.s_points, tab.s_ts
FROM
  level_quiz l
LEFT JOIN
(
  SELECT 
    sp.student_no AS "s_no", sp.level_id AS "s_l_id", 
    sp.points AS "s_points", sp.ts AS "s_ts"
  FROM student_points sp 
  WHERE sp.student_no IN (40204123, 40213894)
) tab
ON l.id = tab.s_l_id
ORDER BY tab.s_no, l.id;


l_id
l_title
l_q_desc
l_o_quest
s_no
s_l_id
s_points
s_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
null
null

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
null
null

4
Pred Logic
pred desc 1   ...
predicase quest...
40213894
4
100
2021-01-17 22:42:50


--
--
--
--
-- DELETE the records added for Solution 1!
--
DELETE FROM student_points WHERE points IS NULL;
--
-- Now, we start afresh!
--
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;


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


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;


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
null

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
null

4
Pred L
pred desc 1   ...
predicase quest...
40213894
100
2021-01-17 22:42:50


EXPLAIN ANALYZE
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;


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)
id	level_title	quiz_desc	overall_quest
1	Sets	The purpose of...	Suppose we have...
2	Seqs	sequences desc...	overall question...
3	Prop Logic	logic desc ...	overall quest...
4	Pred Logic	pred desc 1 ...	predicase quest...
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