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

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.

CodePudding user response：

Or use UNION:

(select id 
 from foo
 where name='user1234'
 union 
 select null) 
order by id desc 
limit 1;

DBFIDDLE

CodePudding user response：

You could left outer join with your own derived table, such as:

select t.id
from (select null x)x left join t on t.name = 'aaa';

Demo fiddle

CodePudding user response：

Another options is using COALESCE, but this answer is optimal if the id is auto_increment and from name varchar(128) unique not null I expect the id to be unique ,too.

select COALESCE(MAX(id), NULL) AS id
from foo
where name='test4';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=16feefafd7eeccd9b48cd45b7b7a4d18

CodePudding user response：

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.