Menu
I just need to know if a query returns or not a record.
Of course I can do this:
SELECT COUNT(*) FROM tbl WHERE conds;
But this returns the exact number of rows (of course), and I don't need this overhead.
So I thought this query:
SELECT COUNT(*) FROM (SELECT id FROM tbl WHERE conds LIMIT 1) as t1
Limiting the internal query to 1.
Is this faster? Or considering I am doing a subquery it cancels the benefits of LIMIT 1?
Note: for everyone asking theirself, I can't apply LIMIT 1 to the first query because it doens't work
420
The SQL EXISTS Operator The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.
Description. The SQL EXISTS condition is used in combination with a subquery and is considered to be met, if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
You can use the FETCH FIRST 1 ROW ONLY clause in a SELECT statement to ensure that only one row is returned. This action prevents undefined and unpredictable data from being returned when you specify the INTO clause of the SELECT statement.
To test whether a row exists in a MySQL table or not, use exists condition. The exists condition can be used with subquery. It returns true when row exists in the table, otherwise false is returned. True is represented in the form of 1 and false is represented as 0.
The inner-select in the second query is redundant.
If you just want to check at-least of one row :-
SELECT 1 FROM tbl // return 1
WHERE conds // depends on your index and query
ORDER BY NULL // avoid file-sort
LIMIT 1; // minimum row
Why not just:
SELECT 1 FROM tbl WHERE conds LIMIT 1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
