CODEWITHSUNDEEP
sqloracle-databasesubquery
Bumper
Bumper

Reputation: 387

SQL return exactly one row or null in a select sub-query

In Oracle, is it possible to have a sub-query within a select statement that returns a column if exactly one row is returned by the sub-query and null if none or more than one row is returned by the sub-query?

Example:

SELECT X,
  Y,
  Z,
  (SELECT W FROM TABLE2 WHERE X = TABLE1.X) /* but return null if 0 or more than 1 rows is returned */
FROM TABLE1;

Thanks!

Upvotes: 2

Views: 8575

Answers (4)

PlantTheIdea
PlantTheIdea

Reputation: 16369

How about going about it in a different way? A simple LEFT OUTER JOIN with a subquery should do what you want:

SELECT T1.X
 ,T1.Y
 ,T1.Z
 ,T2.W
FROM TABLE1 AS T1
LEFT OUTER JOIN (
    SELECT X
      ,W
    FROM TABLE2
    GROUP BY X,W
    HAVING COUNT(X) = 1
) AS T2 ON T2.X = T1.X;

This will only return items that have exactly 1 instance of X, and LEFT OUTER JOIN it back to the table when appropriate (leaving the non-matches NULL).

This is also ANSI-compliant, so it is quite performant.

Upvotes: 6

halfbit
halfbit

Reputation: 3939

my answer is: dont use subselects (unless you are sure ...)

no need and not a good idea to use a subselect here as PlantTheIdea mentioned because of two things

explaination:

subselect means:

one select for each row of the primary select result set. i.e. if you get 1000 rows, you also get 1000 (small) select statemts in your db-system (ignoring optimizer here)

and(!)

with a subselect you have a good chance to hide (or override) a heavy database or select problem. that means: you are only expecting none (NULL) or one (exactly) row (both easily resolvable with a [left outer] join). if there are more than one in your subselect there is something wrong, the SQL Error points that out

the "HAVING COUNT(X) = 1" of course correct, has the small (or not small) problem, thats: "why is there a count of more than one row?"

I spent hours of lifetime finding a workarround like this, just ending up in "dont do it if you are realy sure ..."

I see that in opposite to a "having" like this 

 ...
 HAVING date=max(date) -- depends on sql dialect

or 

where date = select max(date) from same_table

and with my last example i again want to point out: if you get here more than one row (both from today ;.) you have a DB problem - you chould use a timestamp instead for example

Upvotes: 0

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

SELECT
    X, Y, Z, (SELECT W FROM TABLE2 WHERE X = TABLE1.X HAVING COUNT(*) = 1) 
FROM 
    TABLE1;

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

Besides a CASE solution or rewriting the inline subquery as an outer join, this will work, if you can apply an aggregate function (MIN or MAX) on the W column:

SELECT X,
  Y,
  Z,
  (SELECT MIN(W) FROM TABLE2 WHERE X = TABLE1.X HAVING COUNT(*) = 1) AS W
FROM TABLE1;

Upvotes: 1

Related Questions