Oracle sql return true if exists question

Question

How do I check if a particular element exists in a table - how can I return true or false?

I have a table that has

• user_id
• user_password
• user_secretQ

Verbally, I want to do this: If a particular user_id exists in the user_id column, then return true -- otherwise return false.

Garlicky

Desired output STRING "YES" or "NO" And user_id is numberic – Garlicky

If you want to return a string, then edit your question according to that. An string saying "YES" or "NO" is not the same as a returning true or false, which would be a boolean. – Condottiere

Answer 1

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';

Fundy

Is it throwing an error? Does your select statement work in plain old SQL*Plus? – Fundy

Or SELECT NVL2(MAX(user_ID),'YES','NO') ... – Edmanda

Answer 2

In PL/SQL you can do this:

function user_exists (p_user_id users.user_id%type) return boolean
is
  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 boolean
is
  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;

Conditioner

Answer 3

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')
UNION
SELECT 'FALSE' FROM DUAL WHERE NOT EXISTS (SELECT 'x' FROM  table WHERE user_id = 'id')

I like @DCookie's query though.

Wamsley

TIL boolean is not part of the DDL – Catalan

Answer 4

select count(*) from table where userid = :userid and rownum <= 1); -- If exists then 1 else 0

Tussis

Answer 5

Or you could do this:

select decode(max(USER_ID), null, 'FALSE', 'TRUE') BOOL_VAL
from USER_TABLE where USER_ID = [some USER_ID here]

Farland

A String with "FALSE" or "TRUE" as content is not a Boolean value. – Condottiere

Answer 6

I use something like this using Oracle:

SELECT CASE WHEN EXISTS (
    { MY SELECT QUERY HERE }
) THEN 1 ELSE 0 END AS result
FROM DUAL;

For example:

SELECT CASE WHEN EXISTS (
    SELECT 1
    FROM mytable t
    WHERE t.column1 = 1234 AND t.column2 = 4444
) THEN 1 ELSE 0 END AS result
FROM DUAL;

Matney