SQL: Return true if list of records exists?
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.
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.
Check if row exists in db, and return boolean
Simple queries are better:
SELECT 1 FROM currency WHERE name=%s LIMIT 1
This will return a single row of 1 if it exists, an 0 rows if it doesn't exist.
Also use prepared statement to avoid SQL injection.
SQL - How to return Boolean value if values exists in table
I would recommend exists
with a table of values. This will prevent duplicates in the result set:
SELECT v.parcel,
(CASE WHEN EXISTS (SELECT 1
FROM Property p
WHERE p.taxyear = YEAR(GETDATE()) - 1 AND
p.parcel = v.parcel
)
THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END) AS ExistsStatus
FROM (VALUES ('1719309002000'),
('1024247013000'),
('1024247008000'),
('1024247001000')
) v(parcel)
Return Boolean Value on SQL Select Statement
What you have there will return no row at all if the user doesn't exist. Here's what you need:
SELECT CASE WHEN EXISTS (
SELECT *
FROM [User]
WHERE UserID = 20070022
)
THEN CAST(1 AS BIT)
ELSE CAST(0 AS BIT) END
SQL: return true/false if multiple values exists or not in a subquery
You can do it by grouping by userid and a condition in the HAVING clause:
select userid
from tablename
where orderid in (2,3,4,5)
group by userid
having count(distinct orderid) = 4
This will select all the userid
s for which there exist the orderid
s 2, 3, 4 and 5.
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.
Related Topics
How to Select All Values and Hide Null Values in SQL
How to Avoid "Table Mutating" Errors
Best Way to Reset an Oracle Sequence to the Next Value in an Existing Column
SQL Missing Right Parenthesis on Order by Statement
Rollback Event Triggers in Postgresql
SQL Update If Parameter Is Not Null or Empty
Select Top N Rows for Each Group
Generate Insert Script for Selected Records
How to Quote Values Using Group_Concat
Rows to Comma Separated Values Using Xml Tag
Save Output from SQL Function to CSV File (Copy) with Dynamic Filename
How to Find the Size of an Array in Postgresql
When Is a Good Situation to Use a Full Outer Join
Divide the Table Data Randomly Based on Percentages
Total Children Values Based on Parent
Selecting Distinct Combinations