Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Boolean alternative to SELECT * FROM sometable WHERE condition

Posted on 2004-08-11
9
Medium Priority
?
1,088 Views
Last Modified: 2006-11-17
is there a Boolean alternative to SELECT * FROM sometable WHERE condition ? I only need to know if a row or several exists with that certain condition or not, no need to fetch the data. I want to do this to optimize my mysql queries. Is there such a way ?
0
Comment
Question by:iconpacific
9 Comments
 
LVL 11

Accepted Solution

by:
Sven earned 1500 total points
ID: 11770925
SELECT RowCount = COUNT(*) FROM sometable WHERE condition

When RowCount > 0 then you have data inside!
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11771716
select CASE COUNT(*) when 0 then "False" else "True" end from TableName WHERE ColumnName=Condition

OR if you prefer 1,0 then

select CASE COUNT(*) when 0 then 0 else 1 end from TableName WHERE ColumnName=Condition
0
 
LVL 11

Expert Comment

by:Sven
ID: 11771742
@hkamal: Would be no difference to mine! For example in VB:

If rs("rowcount") = 0 Then
Else
End If

Or yours:

If rs(0) = 0 Then
Else
End If

You see? No difference at all!
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 14

Expert Comment

by:psadac
ID: 11773765
to DarthSonic :
do you really use mysql ? yes there is a big difference between your query and hkamal one : yours does not work.

RowCount = COUNT(*)     is incorrect

COUNT(*) AS RowCount   will work

0
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 11773829
You can also use SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS(). If you put a LIMIT on the first query, you don't have to return the entire result set to the client.  I think the COUNT(*) option is probably easier in this case, though.

http://dev.mysql.com/doc/mysql/en/Information_functions.html
0
 
LVL 26

Expert Comment

by:Umesh
ID: 11798881
try this..

SELECT ifnull( count( * ) , NULL ) AS Total
FROM tableName
WHERE columnName = 'columnCondition'
0
 
LVL 11

Expert Comment

by:Sven
ID: 11807290
@ushastry
isn't it:

SELECT isnull( count( * ) , 0) AS Total
FROM tableName
WHERE columnName = 'columnCondition'

First: isNull instead of ifNull (in MS SQL it is)
Second set to zero when NULL, otherwise it would be the same as before calling the function: null ;)

At all you do not need isNull, cause COUNT(*) will NEVER return NULL!
0
 
LVL 26

Expert Comment

by:Umesh
ID: 11807328
Hi DarthSonic,

Im not that much familiar with MS SQL.. but in mysql I think it is IFNULL.

0
 
LVL 11

Expert Comment

by:Sven
ID: 11807348
never the less it is unnecessary to call this function as COUNT(*) will ever return zero if nothing is found ;)
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question