Below are some not so common concepts in SQL server.
- Having clause
When we are dealing with aggregate functions like Sum(), count() etc., we generally face difficulty in using them in a WHERE clause.
For example, let us say we have a Customers table which has three columns CustomerID, CustomerName and Product. Because a single customer can buy multiple products, we can insert multiple records for each product a customer buys.
Let’s say we want to find the number of customers who purchased at least five products.
The below query will throw an error:
SELECT Customername, count(CustomerName) FROM Customers where count(CustomerName)>=5
Error Message: An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
We correct this issue using the below query which uses the having clause:
SELECT Customername, count(CustomerName) FROM cUSTOMERs GROUP BY CustomerName having count(CustomerName)>=5
Note: GROUP BY clause is a must when using having clause.
- Exists condition
SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition)
The above query first checks if the select query inside the exists condition has data. If yes, it will return the values in the actual query.