In this blog post today we will see an interesting observation which is related to COUNT and FROM clause in a single query. During my recent Comprehensive Database Performance Health Check, this was brought to my attention by one of the attendees.
When you write code, it is important to review your code. Usually, SQL developers have short deadlines and start writing code with hurry. If there are syntax problems with the code, you will get to know them immediately after executing the code. But there are a few cases where you wanted to mean something but you would get a different result if you miss out the keyword FROM.
Let me show you a simple example
Create the following data set
CREATE TABLE #customers (cust_id INT, cust_name VARCHAR(200), contact_no VARCHAR(20))
INSERT INTO #customers SELECT 10001,'Allen','918762345' UNION ALL SELECT 10002,'Johnson','918923450' UNION ALL SELECT 10001,'Victor','912387659'
Suppose you want to find out the total rows from the table #customers. You write this following query
SELECT COUNT(*) FROM #customers
Which results to 3
Now see what happens when you forget to specify FROM clause
SELECT COUNT(*) #customers
What do you think the result is?
You may be expecting an error as FROM clause is missing. This is quite interesting that it does not throw any error but an unexpected result of 1. #customers become column alias and count(*) return 1. A simple aggregate of count(*) without counting from any table will result to 1.
Reference: Pinal Dave (https://blog.SQLAuthority.com)