SQL query that returns the primary key if found, NULL otherwise

Given a table like

create table foo(id integer primary key, name varchar(128) unique not null);

I am looking for a query that searches for a given name, returns the id if found, and NULL otherwise. Sort of what left join can do, except here there is no left table.

Answer

Use aggregation with MAX() (or MIN()):

SELECT MAX(id) AS id
FROM foo
WHERE name = ?;

An aggregation query like this always returns exactly 1 row with 1 column and if no name satisfies the condition in the WHERE clause then it returns NULL.

Replace ? with the name that you want.

See the demo.