Sql: How to Properly Check If a Record Exists

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.

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.

Check if record exists then insert new row in database table?

I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...

if record does not exist in table1 but
record does exit in contact then
insert a row into inter_work_tbl

The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):

insert into inter_work_tbl

      (user_id
      ,first_name
      ,last_name
      ,password)

select '#session.user_id#',
       c.fname,
       c.lname,
       '#password#'

from   contact c

where  c.userid = #session.user_id#
and    not exists(select  1
                  from    table1 t
                  where   t.user_id = c.userid)

SQL Server : check if row exists in group

You are not getting the record for a specific week, your inner query is returning some random top 1 record having Price = 19.170000, you need to add the following condition in your case statement to get the data for a specific week.

t2.companyId = 18527 AND T1.YEAR([calendarDate])=T2.YEAR([calendarDate]) 
AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])

Your full query should look like the following.

SELECT (
        CASE 
            WHEN EXISTS (
                    SELECT 1
                    FROM table1 t2 WITH (NOLOCK)
                    WHERE Price = 19.170000 AND t2.companyId = 18527
                        AND T1.YEAR([calendarDate]) = T2.YEAR([calendarDate])
                        AND T1.datepart(week, [calendarDate]) = T2.datepart(week, [calendarDate])
                    GROUP BY companyId
                        ,YEAR([calendarDate])
                        ,datepart(week, [calendarDate])
                    )
                THEN 'Record Exists'
            ELSE 'Record doesn''t Exists'
            END
        ) AS [Transaction]
    ,YEAR([calendarDate]) AS year_
    ,datepart(week, [calendarDate]) AS week_
    ,calendarDate
    ,Price
FROM table1 t1
WHERE t1.companyId = 18527

What is the best way to check if a record exists in a SQL Server table using C#?

Exists is more efficient than Count, because count needs to scan all rows to match the criteria and include in the count, exist dont.

So exists with ExecuteScalar is better.

As more info backing this:

According to http://sqlblog.com/blogs/andrew_kelly/archive/2007/12/15/exists-vs-count-the-battle-never-ends.aspx

Both queries scanned the table but the EXISTS was able to at least do
a partial scan do to the fact it can stop after it finds the very
first matching row. Where as the COUNT() must read each and every row
in the entire table to determine if they match the criteria and how
many there are. That is the key folks. The ability to stop working
after the first row that meets the criteria of the WHERE clause is
what makes EXISTS so efficient. The optimizer knows of this behavior
and can factor that in as well. Now keep in mind that these tables are
relatively small compared to most databases in the real world. So the
figures of the COUNT() queries would be multiplied many times on
larger tables. You could easily get hundred's of thousands of reads or
more on tables with millions of rows but the EXISTS will still only
have just a few reads on any queries that can use an index to satisfy
the WHERE clause.

As a simple experiment using AdventureWorks with MSSQL 2012

set showplan_all on

-- TotalSubtreeCost: 0.06216168
select count(*) from sales.Customer

-- TotalSubtreeCost: 0.003288537
select 1 where exists (select * from sales.Customer)

Check if record exists before submitting new

I would have imagined there would be a just a few unique identifier's in worst case; the following should work.

Sub btnSubmit_Click()
    Dim strSQL as string

    strSQL = "INSERT INTO tblMaster (PeriodID, ClassID, ChildID, SubjectID, LevelID) VALUES (" & _
                cboPeriod & ", " & cboClass & ", " & cboName & ", " & tbSubject1 & ", " & cboLevel1 & ")"

    If DCount("*", "tblMaster", "PeriodID = " & cboPeriod & " AND " & _
                                "ClassID = " & cboClass & " AND " & _
                                "ChildID = " & cboName & " AND " & _
                                "SubjectID = " & tbSubject1 & " AND " & _
                                "LevelID = " & cboLevel1) = 0 Then
        CurrentDB.Execute strSQL
    Else
        MsgBox "A Record already exists for this information.", vbOKOnly, "No Records Added."
    End If
End Sub

Cleanest way to check if a record exists

Q: Should I have a LIMIT 1 at the end of my SQL statement? Does COUNT(1) already limit the amount of rows found by 1 or does the server keep searching for more records even after finding the first one?

Your SELECT COUNT() FROM query will return one row, if the execution is successful, because there is no GROUP BY clause. There's no need to add a LIMIT 1 clause, it wouldn't have any affect.

The database will search for all rows that satisfy the conditions in the WHERE clause. If the user_id column is UNIQUE, and there is an index with that as the leading column, or, if that column is the PRIMARY KEY of the table... then the search for all matching rows will be efficient, using the index. If there isn't an index, then MySQL will need to search all the rows in the table.

It's the index that buys you good performance. You could write the query differently, to get a usable result. But what you have is fine.

Q: Is this the cleanest...

  if ($stmt->fetch()[0]>0)

My personal preference would be to avoid that construct, and break that up into two or more statements. The normal pattern...separate statement to fetch the row, and then do a test.

Personally, I would tend to avoid the COUNT() and just get a row, and test whether there was a row to fetch...

  $sql = "SELECT 1 AS `row_exists` FROM myTable WHERE user_id = :id_var";
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':id_var', $id_var);
  $stmt->execute();
  if($stmt->fetch())  {
      // row found
  } else {
      // row not found 
  }
  $stmt->closeCursor();

How do you check if a record exists in SQL server 2012 when NULL values are possible?

Edit: Recent activity prompted me to reread my accepted answer and I have no idea what I was thinking when I wrote it. Sure, it will work if you're careful but it's not a good solution. Instead, the intersect and except features should be used because they are designed to gracefully handle null comparisons:

declare @a int = null;
declare @b int = null;

select Data.*
from (values
    (1, 1)
  , (1, null)
  , (null, 1)
  , (null, null)
) as Data (a, b)
where exists (
          select @a, @b
          intersect
          select Data.a, Data.b 
      );

I also noticed that @hvd actually shared this solution in the comments so I hope you followed his advice and not mine!

Original (terrible) Answer: I think this does what you're asking for with the caveat that you must be careful about which value you choose as your fallback. The choice of zero here is poor because 0 is potentially a valid value in the table.

declare @i1 int = null;
declare @i2 int = null;

select Data.*
from (values
    (1, 1)
  , (1, null)
  , (null, 1)
  , (null, null)
) as Data (a, b)
where Coalesce(Data.a, 0) = Coalesce(@i1, 0)
  and Coalesce(Data.b, 0) = Coalesce(@i2, 0);

Check if specific record exists in one to many tables

You want to know if a meeting with the meeting type and the persons in question already exists. So join and count:

select count(*)
from
(
  select m.id
  from meeting m
  join meeting_persion mp on mp.meeting_id = m.id
  where m.meeting_type = 1 
    and (mp.person_type, mp.person_id) in ((1,100),(1,101),(2,102))
  group by m.id
  having count(*) = 3
);

This query results in the number of matching meetings (0 or more).

If, however, you are only interested in meetings with exactly those persons, i.e. no additional persons, then you must move the criteria on persons from WHERE to HAVING:

select count(*)
from
(
  select m.id
  from meeting m
  join meeting_persion mp on mp.meeting_id = m.id
  where m.meeting_type = 1 
  group by m.id
  having count(case when (mp.person_type, mp.person_id) in ((1,100),(1,101),(2,102))
                    then 1 end) = 3
     and count(*) = 3
);

This query results in the number of matching meetings (0 or 1).

Sql: How to Properly Check If a Record Exists