What's the Most Efficient Way to Check If a Record Exists in Oracle

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

select case 
            when exists (select 1 
                         from sales 
                         where sales_type = 'Accessories') 
            then 'Y' 
            else 'N' 
        end as rec_exists
from dual;

The fastest way to check if some records in a database table?

An EXISTS query is the one to go for if you're not interested in the number of records:

select 'Y' from dual where exists (select 1 from mytable where parent_id = :id)

This will return 'Y' if a record exists and nothing otherwise.

[In terms of your question on Hibernate's "uniqueResult" - all this does is return a single object when there is only one object to return - instead of a set containing 1 object. If multiple results are returned the method throws an exception.]

Proper way of checking if row exists in table in PL/SQL block

I wouldn't push regular code into an exception block. Just check whether any rows exist that meet your condition, and proceed from there:

declare
  any_rows_found number;
begin
  select count(*)
  into   any_rows_found
  from   my_table
  where  rownum = 1 and
         ... other conditions ...

  if any_rows_found = 1 then
    ...
  else
    ...
  end if;

Fastest way to determine if record exists

SELECT TOP 1 products.id FROM products WHERE products.id = ?; will outperform all of your suggestions as it will terminate execution after it finds the first record.

SQL: How to properly check if a record exists

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

Oracle PL/SQL: Efficient way to check if records are present in a view?

DECLARE
    ln_exists NUMBER;
BEGIN
      SELECT COUNT(*)
      INTO ln_exists
      FROM v_view_with_huge_data --Data is also coming from DBLink tables
 /**/ WHERE rownum <= 1 /**/

      IF ln_exists > 0
      THEN
         p_log_message('data found');
         --further processing
         ...
         ...
      ELSE
         p_log_message('no data found');
      END IF;
END

How can I check if a record exist in Oracle database with Java?

String checkSql = "select count(*) from HIGHSCORES where name = '"+name+"'";

Statement st = con.createStatement();
ResultSet result = st.executeQuery(checkSql);
result.next();
if ( result.getInt(1) == 0) {
  System.out.println("doesn't exist");
} else {
  System.out.println("exists");
}

To make this better, you'd switch to a PreparedStatement which prevents SQL injection

---

Related Topics