-1

I had a query that was a simple left join between two tables with IS NULL included in the where clause because I needed all of the rows of the left table shown, even if it gave null values in the right table.

This worked as I had it working within my php code and my site was displaying what it needed. I haven't looked at this in over a week and went back today to see that it is now all of a sudden not working even though I haven't touched it.

I have created a db fiddle here with my exact code and tables - https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=2effc82390641ce513806252700fd25c

I want to show - all rows in the left table (level_quiz) and all rows in the right table (student_points) where student_no = 40204123 OR there are NULL rows

Could anyone please have a look at this to see why it is not showing the extra row of the left table? (where there would be NULL values for the right table)

This would be much appreciated.

0

You have to select the student before joining.

also use aliases so you have less to type in

SELECT 
    *
FROM
    level_quiz
        LEFT JOIN
    (SELECT * FROM student_points WHERE student_no = 40204123) sp ON level_quiz.id = sp.level_id
id | level_title            | quiz_desc                                                                                                                                                                    | overall_quest                                                                                                                                               | student_no | level_id | points | timestamp          
-: | :--------------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :---------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------: | -------: | -----: | :------------------
 1 | Sets                   | The purpose of this challenge is to help you become completely familiar with how to write certain kinds of sets, and how to perform the standard operations on actual sets.  | Suppose we have three sets of numbers A, B and C, which are defined as follows: A = { 1, 10, 7, 3, 5, 2 };B = { 5, 8, 6, 7, 4 };C = { 7, 1, 8 } |   40204123 |        1 |     80 | 2021-01-12 15:37:11
 2 | sequences              | sequences desc                                                                                                                                                               | overall question 2                                                                                                                                          |   40204123 |        2 |     75 | 2021-01-12 15:38:06
 3 | Propositional Logic    | logic desc                                                                                                                                                                   | overall quest logic                                                                                                                                         |   40204123 |        3 |     30 | 2021-01-13 22:13:13
 4 | Predicate Logic - Sets | predicate desc 1                                                                                                                                                             | predicase quest 1                                                                                                                                           |       null |     null |   null | null               

db<>fiddle here

4
  • Thanks so much! Do you have any idea why my query would have been working originally if it isn't now? This is so strange because it was 100% working ! – bb25 Jan 23 at 0:11
  • no i was also astonished, that this will not work, also a test in mysql 8 shows the same result and used a right join. i still thinl this is a bug, as i wrote a bug report on mysql website – nbk Jan 23 at 0:17
  • And in January, 2038 your application using timestamp will quit functioning. Check timestamp date ranges in the reference manual, please. – Wilson Hauck Jan 30 at 21:35
  • it is ample time to make huge int and timestamp to hold bigger dates – nbk Jan 30 at 21:39
0

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!

5
  • Firstly thank you for your very detailed answer and time! However - your Solution 1 i find unnecessary because it is adding NULL's to the DB where they are not really required, and I'd have to add these for every student and every quiz at some stage in the code which seems a bit pointless :( Also I only ever need to check this for one student at a time so it isn't necessary that the query works for multiple students. – bb25 Jan 30 at 16:58
  • Also your solution 3 has confused me because that is my schema already.. I have already normalized my DB and I have a student table, a level_quiz table, and the associative table is student_points to allow for the many-to-many relationship between students and quizzes. I can't see the difference in your solution 3 schema and mine. I have already changed my query to SELECT * FROM level_quiz LEFT JOIN student_points ON level_quiz.id = student_points.level_id AND student_points.student_no = '40204123' and it now works, so I'm not sure why the cross join in your solution 3 is necessary? – bb25 Jan 30 at 17:02
  • note - I just didn't include my full schema in the question as the student table wasn't necessary to the question and I also have other tables in the whole DB – bb25 Jan 30 at 17:06
  • The 3rd solution uses a CROSS JOIN for all students against all quizzes - so a student who hasn't taken any quizes (i.e no entry in the student_points table) - will show up in the results - unless that doesn't interest you and you can exclude that possibility. If you only want 1 student at a time, then nbk's solution will work for you, but it's not generalisable. I find that it's better to have a flexible solution that can be adapted rather than having the SQL tailored to a particular situation. What you really require is an understanding of why your first query didn't work! – Vérace Jan 30 at 17:14
  • I would argue that your student table is necessary for a complete solution and provides a better answer in the long run, but if you're happy with the SQL and results from nbk's solution, then that's great for you! – Vérace Jan 30 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.