Scalar Query in SQL

Scalar Query is a query in SQL which returns exactly one column from a single row as a result in SQL.
Now, why do we need it? 

• We usually need it to retrieve a scalar value from result set.
• We can also use it in WHERE clause while checking certain conditions (true or false). This is termed as Scalar Subquery. 

Let's see few examples of Scalar Query and how well we can utilize it.

I have used SQL data set from w3schools.com for explanations.

Simple Scalar Queries :

SELECT CustomerID FROM Customers

WHERE Country='Germany'

LIMIT 1 OFFSET 3;

Output: 25

SELECT TOP 1 CustomerID FROM Customers

WHERE Country='Germany';

Output: 1

The way I used this query is as follows:

SELECT TOP 1 0 from Customers WHERE Country = 'London';

Output : 0 records.

Here, As there are no records with country = 'London', it won't have any result set.

SELECT TOP 1 0 from Customers WHERE Country = 'Germany';

Output: 0

SELECT TOP 1 1 from Customers WHERE Country = 'Germany';

Output: 1

SELECT TOP 1 or 0 will help to get a boolean value from query result.

Scalar Subquery example:
SELECT ct.CountryName,
              (SELECT COUNT(*) FROM Customers c where c.CountryID = ct.ID ) count
FROM Country ct


This helped me while writing lengthy queries with multiple joins and conditions. I hope you find it useful.

Thanks to you my team lead Hemant Ahire at Yardi for introducing me to this query.