How do i check if something exist without using count(*) ... limit 1

Question

My code is SELECT COUNT(*) FROM name_list WHERE [name]='a' LIMIT 1

It appears there is no limit clause in SQL Server. So how do i say tell me if 'a' exist in name_list.name?

Answer 1

No nono that is wrong.

First there is top, so you have to say something like:

select top 1 1 from name_list where [name]='a'

You'll get a row with only a unnamed field with 1 out of the query if there is data, and no rows at all if there is no data.

Answer 2

The TOP clause is the closest equivalent to LIMIT. The following will return all of the fields in the first row whose name field equals 'a' (altough if more than one row matches, the row that ets returned will be undefined unless you also provide an ORDER BY clause).

SELECT TOP 1 * FROM name_list WHERE [name]='a'

But there's no need to use it if you're doing a COUNT(*). The following will return a single row with a single field that is number of rows whose name field eqals 'a' in the whole table.

SELECT COUNT(*) FROM name_list WHERE [name]='a'

Answer 3

IF (EXISTS(SELECT [name] FROM name_list where [name] = 'a'))
begin
   //do other work if exists
end

You can also do the opposite:

IF (NOT EXISTS(SELECT [name] FROM name_list where [name] = 'a'))
begin
   //do other work if not exists
end

Answer 4

COUNT(*) returns a single row anyway, no need to limit.
The ANSI equivalent for LIMIT is TOP: SELECT TOP(1) ... FROM ... WHERE...
And finally, there is EXISTS: IF EXISTS (SELECT * FROM ... WHERE ...).

Answer 5

IF EXISTS(SELECT * FROM name_list WHERE name = 'a')
BEGIN
    -- such a record exists
END
ELSE
BEGIN
    -- such a record does not exist
END

Points to note:

• don't worry about the SELECT * - the database engine knows what you are asking
• the IF is just for illustration - the EXISTS(SELECT ...) expression is what answers your question
• the BEGIN and END are strictly speaking unnecessary if there is only one statement in the block

Answer 6

This query returns exactly what you intended:

SELECT TOP 1 CASE WHEN EXISTS(SELECT * WHERE [name] = 'a') THEN 1 ELSE 0 END FROM name_list