Introduction
Sometimes instead of a join you can deliberately use a scalar subquery to check that not more than one row was found. For example you might have this query to look up nationality for some person rows.
select p.name,
c.iso
from person p
join person_country_map pcm
on p.id = pcm.person
join country c
on pcm.country = c.id
where p.id in (1, 2, 3)
Now, suppose that the person_country_map is not a functional mapping. A given person may map to more than one country - so the join may find more than one row. Or indeed, a person might not be in the mapping table at all, at least as far as any database constraints are concerned.
But for this particular query I happen to know that the persons I am querying will have exactly one country. That is the assumption I am basing my code on. But I would like to check that assumption where possible - so that if something went wrong and I end up trying to do this query for a person with more than one country, or with no country mapping row, it will die.
Adding a safety check for at most one row
To check for more than one row, you can rewrite the join as a scalar subquery:
select p.name,
(
select c.iso
from person_country_map pcm
join country c
on pc.country = c.id
where pcm.person = p.id
) as iso
from person p
where p.id in (1, 2, 3)
Now the DBMS will give an error if a person queried maps to two or more countries. It won't return multiple entries for the same person, as the straightforward join would. So I can sleep a bit easier knowing that this error case is being checked for even before any rows are returned to the application. As a careful programmer I might check in the application as well, of course.
Is it possible to have a safety check for no row found?
But what about if there is no row in person_country_map for a person? The scalar subquery will return null in that case, making it roughly equivalent to a left join.
(For the sake of argument assume a foreign key from person_country_map.country to country.id and a unique index on country.id so that particular join will always succeed and find exactly one country row.)
My question
Is there some way I can express in SQL that I want one and exactly one result? A plain scalar subquery is 'zero or one'. I would like to be able to say
select 42, (select exactly one x from t where id = 55)
and have the query fail at runtime if the subquery wouldn't return a row. Of course, the above syntax is fictional and I am sure it wouldn't be that easy.
I am using MSSQL 2008 R2, and in fact this code is in a stored procedure, so I can use TSQL if necessary. (Obviously ordinary declarative SQL is preferable since that can be used in view definitions too.) Of course, I can do an exists check, or I can select a value into a TSQL variable and then explicitly check it for nullness, and so on. I could even select results into a temporary table and then build unique indexes on that table as a check. But is there no more readable and elegant way to mark my assumption that a subquery returns exactly one row, and have that assumption checked by the DBMS?
In MSSQL it appears that isnull only evaluates its second argument if the first is null. So in general you can say
select isnull(x, 0/0)
to give a query which returns x if non-null and dies if that would give null. Applying this to a scalar subquery,
select 42, isnull((select x from t where id = 55), 0/0)
will guarantee that exactly one row is found by the select x subquery. If more than one, the DBMS itself will produce an error; if no row, the division by zero is triggered.
Applying this to the original example leads to the code
select p.name,
-- Get the unique country code of this person.
-- Although the database constraints do not guarantee it in general,
-- for this particular query we expect exactly one row. Check that.
--
isnull((
select c.iso
from person_country_map pcm
join country c
on pc.country = c.id
where pcm.person = p.id
), 0/0) as iso
from person p
where p.id in (1, 2, 3)
For a better error message you can use a conversion failure instead of division by zero:
select 42, isnull((select x from t where id = 55), convert(int, 'No row found'))
although that will need further convert shenanigans if the value you are fetching from the subquery is not itself an int.