Solved

Boolean alternative to SELECT * FROM sometable WHERE condition

Posted on 2004-08-11
9
1,074 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 11

Accepted Solution

by:
Sven earned 500 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Upcoming Webinar: Percona XtraDB Cluster 6/21 10am

Join Percona’s MySQL Practice Manager Kenny Gryp and QA Engineer, Ramesh Sivaraman as they present Percona XtraDB Cluster, Galera Cluster, MySQL Group Replication on Wednesday, June 21, 2017 at 10:00 am PDT / 1:00 pm EDT (UTC-7).

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

734 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