SQL: Return "true" if list of records exists?

Here's how I usually do it:

Just replace your query with this statement SELECT * FROM table WHERE 1

SELECT
    CASE WHEN EXISTS 
    (
        SELECT * FROM table WHERE 1
    )
    THEN 'TRUE'
    ELSE 'FALSE'
END

Given your updated question, these are the simplest forms:

If ProductID is unique you want

SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100)

and then check that result against 3, the number of products you're querying (this last part can be done in SQL, but it may be easier to do it in C# unless you're doing even more in SQL).

If ProductID is not unique it is

SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100)

When the question was thought to require returning rows when all ProductIds are present and none otherwise:

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(*) FROM Products WHERE ProductID IN (1, 10, 100))=3)

or

SELECT ProductId FROM Products WHERE ProductID IN (1, 10, 100) AND ((SELECT COUNT(DISTINCT ProductID) FROM Products WHERE ProductID IN (1, 10, 100))=3)

if you actually intend to do something with the results. Otherwise the simple SELECT 1 WHERE (SELECT ...)=3 will do as other answers have stated or implied.


@Mark Hurd, thanks for pointing out the error.

this will work (if you are using Postgresql, Sql Server 2008):

create table products
(
product_id int not null
);



insert into products values(1),(2),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM (values(1),(10),(100)) as x(id) 
             WHERE x.id NOT IN (select product_id from products))
        THEN 0 --'NOT ALL'

        ELSE 1 -- 'ALL'
    END

If you are using MySQL, make a temporary memory table(then populate 1,10,100 there):

create table product_memory(product_id int) engine=MEMORY;

insert into product_memory values(1),(10),(100);

SELECT 
    CASE 
        WHEN EXISTS(
             SELECT 1 
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products))
        THEN 0 -- 'NOT ALL'

        ELSE 1 -- 'ALL'
    END

On your C# code:

bool isAllExist = (int)(new SqlCommand(queryHere).ExecuteScalar()) == 1;

[EDIT]

How can I write a query that will return a row if all the product id's exist and no row if one or more product id's does not exist?

Regarding, returning a row(singular) if all rows exists, and no row to be returned if one or more product id does not exists:

MySql:

SELECT 1
WHERE 
    NOT EXISTS(
        SELECT 1
             FROM product_memory
             WHERE product_memory.id NOT IN (select product_id from products) )

Posgresql, Sql Server 2008:

SELECT 1
WHERE 
    NOT EXISTS(            
        SELECT 1 
        FROM (values(1),(10),(100)) as x(id) 
        WHERE x.id NOT IN (select product_id from products) )

Then on your C# code:

var da = new SqlDataAdapter(queryhere, connectionhere);
var dt = new DataTable();
da.Fill(dt);

if (dt.Rows.Count > 0) 
    return true; 
else 
    return false;

Or just make the condition shorter:

return dt.Rows.Count > 0;