SQL scalar subquery checking a row was found

Question

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?

Answer 1

You are making this harder than it needs to be

For sure you need a FK relationship on person.id to person_country_map.person

You either have unique constraint on person_country_map.person or you don't?
If you don't have a unique constraint then yes you can have multiple records for the same person_country_map.person.

If you want to know if you have any duplicate then

select pcm.person 
from person_country_map pcm
group by  pcm.person  
having count(*) > 1

If there is more than one then you just need to determine which one

select p.name,
       min(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)
group by p.name

Answer 2

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.