How to check if the system user with the name "John" already exists in IRIS via SQL?

name($Username) or fullname?

some form of

select name,Fullname from security.Users

with the appropriate where clause would work, albeit it has to be run in %SYS.

Additionally, non SQL, there is a built in function for this of

if##class(Security.Users).Exists("John") {
    // user exists
}

which confirms if the IRIS user exists using the users username (here, assuming username of John). Documentation on the Security.Users docs page.