SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table USERS (
    id int not null primary key, 
    name varchar(50), 
    email varchar(50)
);

INSERT INTO USERS(id, name, email) VALUES
(1, 'john', 'aaa@gmail.com'),
(2, 'nick', 'bbb@gmail.com'),
(3, 'alex', 'ccc@gmail.com'),
(4, 'liam', 'ddd@gmail.com'),
(5, 'noah', 'eee@gmail.com'),
(6, 'oliver', 'fff@gmail.com'),
(7, 'james', 'ggg@gmail.com'),
(8, 'rob', 'hhh@gmail.com');

8 rows affected

select coalesce(max(id), (select coalesce(max(id), 0) + 1 from users))
from users
where email = 'asdf@gmail.com'


(No column name)


9


delete from users
8 rows affected

select coalesce(
    (select id from users where email = 'asdf@gmail.com'),
    (select max(id) + 1 from users),
    1
)


(No column name)


1