sql null in sub_query

_sql_removing_nulls.md

Filter out the winners

Notes

• in this interview question the bad query fails beacasse of null values in the races table.
• adding a where not field is null exludes the nulls and allows the function to work correctly
• using a subquery within the where clause based on a forieng key is like an inner join.
• using a negation of the above produces a filter of non winner.
• cheking null is done using IS not =

SQL Fiddle

Query 1:

select * from races

Results:

| id |          event | winner_id |
|----|----------------|-----------|
|  1 | 100 meter dash |         2 |
|  2 | 500 meter dash |         3 |
|  3 |  cross-country |         2 |
|  4 |     triathalon |    (null) |

Query 2:

select * from runners

Results:

| id |        name |
|----|-------------|
|  1 |    John Doe |
|  2 |    Jane Doe |
|  3 | Alice Jones |
|  4 | Bobby Louis |
|  5 | Lisa Romero |

Query 3:

SELECT * 
  FROM runners 
 WHERE id NOT IN (SELECT winner_id 
                    FROM races
                 where not winner_id is null)

Results:

| id |        name |
|----|-------------|
|  1 |    John Doe |
|  4 | Bobby Louis |
|  5 | Lisa Romero |

bad query.sql

SELECT * 
  FROM runners 
 WHERE id NOT IN (SELECT winner_id 
                    FROM races
                 WHERE NOT winner_id IS null);

good query.sql

SELECT * 
  FROM runners 
 WHERE id NOT IN (SELECT winner_id 
                    FROM races);

setup.sql

CREATE TABLE runners
    (`id` int, `name` varchar(11));
    
INSERT INTO runners
    (`id`, `name`)
VALUES
    (1, 'John Doe'),
    (2, 'Jane Doe'),
    (3, 'Alice Jones'),
    (4, 'Bobby Louis'),
    (5, 'Lisa Romero');

CREATE TABLE races
    (`id` int, `event` varchar(14), `winner_id` varchar(4))
;
    
INSERT INTO races
    (`id`, `event`, `winner_id`)
VALUES
    (1, '100 meter dash', '2'),
    (2, '500 meter dash', '3'),
    (3, 'cross-country', '2'),
    (4, 'triathalon', NULL)
;