Oracle sql return true if exists question

There is no Boolean type in Oracle SQL. You will need to return a 1 or 0, or some such and act accordingly:

SELECT CASE WHEN MAX(user_id) IS NULL THEN 'NO' ELSE 'YES' END User_exists  FROM user_id_table WHERE user_id = 'some_user';

In PL/SQL you can do this:

function user_exists (p_user_id users.user_id%type) return booleanis  l_count integer;begin  select count(*)  into   l_count  from   users  where  user_id = p_user_id;  return (l_count > 0);end;

This would then be used in calling PL/SQL like this:

if user_exists('john') then  dbms_output.put_Line('John exists');end if;

NOTE: I used count(*) in the query in the knowledge that this will only return 1 or 0 in the case of a primary key search. If there could be more than one row then I would add "and rownum = 1" to the query to prevent unnecessarily counting many records just to find out if any exists:

function user_has_messages (p_user_id users.user_id%type) return booleanis  l_count integer;begin  select count(*)  into   l_count  from   messages  where  user_id = p_user_id  AND ROWNUM = 1;  return (l_count > 0);end;

Oracle RDBMS does not have boolean data type, you can only use boolean variables in PL/SQL.

If you simply want to return strings 'TRUE' and 'FALSE'you can do this..

SELECT 'TRUE'  FROM DUAL WHERE EXISTS (SELECT 'x' FROM  table WHERE user_id = 'id')UNIONSELECT 'FALSE' FROM DUAL WHERE NOT EXISTS (SELECT 'x' FROM  table WHERE user_id = 'id')

I like @DCookie's query though.