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

Question

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!

Answer 1

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

Answer 2

I'm not sure exactly what you mean, but COUNT(col) does not count null, so:

SELECT fname
FROM name
GROUP BY fname
HAVING COUNT(location_id) = 0

If you do a self join with name, you will get the cardinality back.

SELECT a.fname, a.location_id
FROM name a
JOIN (
    SELECT fname
    FROM name
    GROUP BY fname
    HAVING COUNT(location_id) = 0
) as b
    USING (fname)

I really don't see the point joining with location, since it is guaranteed to fail, but:

SELECT a.fname, c.location_name
FROM name a
JOIN (
    SELECT fname
    FROM name
    GROUP BY fname
    HAVING COUNT(location_id) = 0
) as b
    USING (fname)
LEFT JOIN location c
    USING (location_id);