Mysql – SELECT LEFT JOIN but only the record that every matched value is null

join;MySQLselect

Suppose that I have two tables name and location

name    |   location_id
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL
'B'     |   2
'C'     |   3
'C'     |   4

location_id |   location_name
1   |   Australia
2   |   Belgium
3   |   Croatia
4   |   Chile
5   |   Denmark
CREATE TABLE location (
    location_id INT PRIMARY KEY,
    location_name VARCHAR(64)
);

INSERT INTO location VALUES
    (1, 'Australia'),
    (2, 'Belgium'),
    (3, 'Croatia'),
    (4, 'Chile'),
    (5, 'Denmark');

CREATE TABLE name (
    id INT PRIMARY KEY,
    fname VARCHAR(64),
    location_id INT,
    FOREIGN KEY (location_id) REFERENCES Location(location_id)
);

INSERT INTO name VALUES
    (1, 'A', NULL),
    (2, 'A', NULL),
    (3 ,'B', NULL),
    (4 ,'B', 2),
    (5 ,'C', 3),
    (6 ,'C', 4);

I want to select only the record that every matched value is null. That is only A will appear because it does not match any location_id but B should not appear because it does match with 2 or Belgium.

SELECT fname, location_name
FROM name n LEFT JOIN location l
USING (location_id)
WHERE location_name IS NULL

Query result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL
'B'     |   NULL

The desired result:

name    |   location_name
'A'     |   NULL
'A'     |   NULL

Can someone guide me how do I write the WHERE statement that will give me the desired result? Thanks!

Best Answer

If you want just rows which has NULL values, you can use this;

SELECT fname,location_id
FROM name WHERE fname NOT IN 
(SELECT fname FROM name WHERE location_id IS NOT NULL)

The last row in code eliminate rows which have a value different than NULL