CODEWITHSUNDEEP
sqlsql-serverperformanceselectcount
SnakeDoc
SnakeDoc

Reputation: 14351

Fastest way to determine if record exists

As the title suggests... I'm trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not.

Sample query:

SELECT COUNT(*) FROM products WHERE products.id = ?;

    vs

SELECT COUNT(products.id) FROM products WHERE products.id = ?;

    vs

SELECT products.id FROM products WHERE products.id = ?;

Say the ? is swapped with 'TB100'... both the first and second queries will return the exact same result (say... 1 for this conversation). The last query will return 'TB100' as expected, or nothing if the id is not present in the table.

The purpose is to figure out if the id is in the table or not. If not, the program will next insert the record, if it is, the program will skip it or perform an UPDATE query based on other program logic outside the scope of this question.

Which is faster and has less overhead? (This will be repeated tens of thousands of times per program run, and will be run many times a day).

(Running this query against M$ SQL Server from Java via the M$ provided JDBC driver)

Upvotes: 219

Views: 670898

Answers (17)

Wesley Cheek
Wesley Cheek

Reputation: 1696

Returning a simple True/False (1 | 0) in MySQL/MariaDB:

SELECT 
    EXISTS(
        SELECT TRUE FROM <table>
        WHERE <condition>
    )

Upvotes: 0

Metalogic
Metalogic

Reputation: 538

SELECT IIF(EXISTS(SELECT 1 FROM products WITH (NOLOCK) WHERE products.id = ?), 1, 0);

This is the fastest way because:

  • It uses EXISTS rather than COUNT or SELECT TOP 1. This is the database native way of determining whether a row exists.
  • Uses NOLOCK to avoid contention.

Additionally, this is more ergonomic than a CASE expression.

Upvotes: 0

iLearn
iLearn

Reputation: 1179

Select count(*) as RecordsExists from 
( SELECT TOP 1 * 
  FROM 
  Your-Table-Name 
  WHERE Column-Name-A='Y' or Column-Name-A ='N'
) 
as x

This Statement will get you the Records Count Based on Inner Statement whether the Records exists or not. It is divided into 2 parts

  1. The Inner Nested Statement will send you the Records whether it exists or not
  2. The Outer Statement will give you the count based on the Records provided by the inner Statement.
  • If the inner statement gives you "No Records" then the Outer Statement will give you "0"
  • If the inner statement gives you "1 Record" then the Outer Statement will give you "1"

Upvotes: 0

GinCanhViet
GinCanhViet

Reputation: 469

May you wanna try my way:

IF (SELECT TOP 1 1 FROM [TableName]) = 1
BEGIN
     --Do work
END

Upvotes: 0

Mohammad Dayyan
Mohammad Dayyan

Reputation: 22409

SQL SERVER 2012+

SELECT IIF((SELECT TOP 1 1 FROM dbo.[YourTable] WHERE [YourColumn] = [YourValue]) IS NULL, 0, 1)

Upvotes: 0

Muhammed Fasil
Muhammed Fasil

Reputation: 8566

For MySql you can use LIMIT like below (Example shows in PHP)

  $sql = "SELECT column_name FROM table_name WHERE column_name = 'your_value' LIMIT 1";
  $result = $conn->query($sql);
  if ($result -> num_rows > 0) {
      echo "Value exists" ;
  } else {
      echo "Value not found";
  }

Upvotes: 1

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

EXISTS (or NOT EXISTS) is specially designed for checking if something exists and therefore should be (and is) the best option. It will halt on the first row that matches so it does not require a TOP clause and it does not actually select any data so there is no overhead in size of columns. You can safely use SELECT * here - no different than SELECT 1, SELECT NULL or SELECT AnyColumn... (you can even use an invalid expression like SELECT 1/0 and it will not break). 

IF EXISTS (SELECT * FROM Products WHERE id = ?)
BEGIN
--do what you need if exists
END
ELSE
BEGIN
--do what needs to be done if not
END

Upvotes: 279

Werner
Werner

Reputation: 449

For those stumbling upon this from MySQL or Oracle background - MySQL supports the LIMIT clause to select a limited number of records, while Oracle uses ROWNUM.

Upvotes: 2

Stefan Zvonar
Stefan Zvonar

Reputation: 4309

Don't think anyone has mentioned it yet, but if you are sure the data won't change underneath you, you may want to also apply the NoLock hint to ensure it is not blocked when reading.

SELECT CASE WHEN EXISTS (SELECT 1 
                     FROM dbo.[YourTable] WITH (NOLOCK)
                     WHERE [YourColumn] = [YourValue]) 
        THEN CAST (1 AS BIT) 
        ELSE CAST (0 AS BIT) END

Upvotes: 13

manish Prasad
manish Prasad

Reputation: 676

Below is the simplest and fastest way to determine if a record exists in database or not Good thing is it works in all Relational DB's

SELECT distinct 1 products.id FROM products WHERE products.id = ?;

Upvotes: 6

Declan_K
Declan_K

Reputation: 6826

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.

Upvotes: 214

Mohammad Atiour Islam
Mohammad Atiour Islam

Reputation: 5708

You can also use

 If EXISTS (SELECT 1 FROM dbo.T1 WHERE T1.Name='Scot')
    BEGIN
         --<Do something>
    END 

ELSE    
     BEGIN
       --<Do something>
     END

Upvotes: 11

Kris Coleman
Kris Coleman

Reputation: 426

SELECT CASE WHEN EXISTS (SELECT TOP 1 *
                         FROM dbo.[YourTable] 
                         WHERE [YourColumn] = [YourValue]) 
            THEN CAST (1 AS BIT) 
            ELSE CAST (0 AS BIT) END

This approach returns a boolean for you.

Upvotes: 18

rogue lad
rogue lad

Reputation: 2452

SELECT COUNT(*) FROM products WHERE products.id = ?;

This is the cross relational database solution that works in all databases.

Upvotes: 2

Eric Parsons
Eric Parsons

Reputation: 1

I've used this in the past and it doesn't require a full table scan to see if something exists. It's super fast...

UPDATE TableName SET column=value WHERE column=value
IF @@ROWCOUNT=0
BEGIN
     --Do work
END

Upvotes: 0

kiran
kiran

Reputation: 1

create or replace procedure ex(j in number) as
i number;
begin
select id into i from student where id=j;
if i is not null then
dbms_output.put_line('exists');
end if;
exception
   when no_data_found then
        dbms_output.put_line(i||' does not exists');

end;

Upvotes: 0

AgentSQL
AgentSQL

Reputation: 2930

Nothing can beat - 

SELECT TOP 1 1 FROM products WHERE id = 'some value';

You don't need to count to know if there is a data in table. And don't use alias when not necessary.

Upvotes: 31

Related Questions