Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the most efficient way to check if a record exists in Oracle?

Tags:

sql

oracle

A)

select decode(count(*), 0, 'N', 'Y') rec_exists from (select 'X'       from dual       where exists (select 'X'                     from sales                     where sales_type = 'Accessories')); 

B)

select decode(count(*), 0, 'N', 'Y') rec_exists from (select 'X'       from sales       where sales_type = 'Accessories');  

C) Something else (specify)

EDIT: It was hard to pick the "correct" answer, as the best approach depends on what you want to do after checking if the value exists, as pointed out by APC. I ended up picking the answer by RedFilter, since I had originally envisioned this check as a function by itself.

like image 507
Zesty Avatar asked Aug 08 '10 13:08

Zesty


People also ask

How do you check if record already exists in Oracle?

Type a short Oracle program, using the following code as a guide: DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM your_table WHERE search_field = 'search value' AND ROWNUM = 1; IF record_exists = 1 THEN DBMS_OUTPUT. put_line('Record Exists') ELSE DBMS_OUTPUT.

Which is faster in or exists in Oracle?

Answers. Exist is more faster than IN because IN doesn't use indexes at the time of fetching but Exist uses Index at the time of fetching.

How do I check if a record exists in SQL?

The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.


1 Answers

select case              when exists (select 1                           from sales                           where sales_type = 'Accessories')              then 'Y'              else 'N'          end as rec_exists from dual; 
like image 110
D'Arcy Rittich Avatar answered Oct 09 '22 05:10

D'Arcy Rittich