SQL return tuples where another column is not null-CodePudding

Let’s say I have a table

Name age
A    Null
B    Null
B    7
C    9
C    8

How can I write a sql query to return

Name 
C

Meaning that only names where there is no null value in age are returned? Specifically using Postgres

Thoughts so far:

I think doing select name from table where age is not null, returns B and C because B has one age that isn’t null. So then, I thought about grouping by name but aggregation seems to remove bulls. Any help appreciated!

CodePudding user response：

Depending on what dbms you are using, you can use ISNULL:

SELECT name FROM table
GROUP BY name
HAVING SUM(ISNULL(age)) = 0

CodePudding user response：

Do a GROUP BY. COUNT(age) counts non-null values. COUNT(*) counts all rows.

SELECT name
FROM table
GROUP BY name
HAVING COUNT(age) = COUNT(*)

Or do an EXCEPT query:

SELECT name FROM table
EXCEPT
SELECT name FROM table WHERE age IS NULL