A lot of times, I saw programmers use COUNT(*) and check if the result is greater than 0 to see if a particular record exits in a table. If your only purpose is to know if a record exists or not and you don’t really care about how many matched records are there, the better way to do it is using EXISTS() function. Here’s why.

Using COUNT(*), the database server needs to count all matched rows. This fact alone, implies that the database server needs to get all the matched records. Now compare that with EXITS() function. The database server knows that you only care if there exits at least 1 matched record. Therefore, as soon as it finds 1 record, it can return; thus saving everyone time and resources of finding additional matched records.

For example, suppose we’re in a library and I just want to know if Bob ever borrowed a book titled “The Cross & The Crescent” by Jerald F. Dirks. The book has ISBN-10 number 1590080025.

One way to find out if Bob ever borrowed the book is to run the query below using COUNT(*).

SELECT
	COUNT(*)
FROM
	checkout_log
WHERE
	first_name = 'bob'
	AND isbn = '1590080025'

A smarter programmer, instead, will use EXISTS() function like below.

SELECT EXISTS (
	SELECT
		isbn
	FROM
		checkout_log
	WHERE
		first_name = 'bob'
		AND isbn = '1590080025'
)

The speed difference may be significant if your library is very busy and checkout_log table is huge. The later query will stop the moment it finds one record while the first query will have to scan all the rows in the table to get the total count.

A minor thing I do for EXISTS() function sub query is to NOT do SELECT *, rather just select a primary key column or some other column. The idea is for EXISTS() function to return true, 1 column and 1 row is all that are needed to know if the record exist or not. Selecting only one column will result in shorter data being returned to EXISTS() and may lead to a better performance.

There you have it … I hope you enjoyed this article. Please leave comments / suggestions / questions if you have. I’m looking forward to improving my solution with your comments / suggestions / questions.