CODEWITHSUNDEEP
sqloracle-databasesubquery
espresso_coffee
espresso_coffee

Reputation: 6110

SQL single-row subquery returns more than one row?

I'm trying to get ID and USER name from one query but at the same time I'm looking in my WHERE clause if ID exist in other table. I got error: 

ORA-01427: single-row subquery returns more than one row

Here is how my query look:

SELECT s.ID, s.LASTFIRST
From USERS s
Left Outer Join CALENDAR c
On s.ID = c.USERID
Where c.SUPERVISOR = '103'
And TO_CHAR(c.DATEENROLLED,'fmmm/fmdd/yyyy') >= '4/22/2016'
And TO_CHAR(c.DATELEFT,'fmmm/fmdd/yyyy') <= '4/22/2016'
And s.ID != (SELECT USER_ID
             From RESERVATIONS
             Where EVENT_ID = '56')

My query inside of where clause returns two ID's: 158 and 159 so these two should not be returned in my query where I'm looking for s.ID and s.LASTFIRST. What could cause this error?

Upvotes: 1

Views: 1345

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Use not in instead of !=

!= or = are for single IDs and values, not in and in are for multiple

And s.ID not in (SELECT USER_ID
                 From RESERVATIONS
                 Where EVENT_ID = '56')

Edit: not in vs not exists

Not exists is a perfectly viable option as well. In fact, it is better to not exists than not in if there are the possibility of null values in the subquery result set - In Oracle, the existence of a null will cause not in to return no results. As a general rule, I use not in for ID, not null columns, and not exists for everything else. It may be better practice to always use not exists... personal preference I suppose.

Not exists would be written like so:

SELECT s.ID, s.LASTFIRST
From USERS s
Left Outer Join CALENDAR c
On s.ID = c.USERID
Where c.SUPERVISOR = '103'
And TO_CHAR(c.DATEENROLLED,'fmmm/fmdd/yyyy') >= '4/22/2016'
And TO_CHAR(c.DATELEFT,'fmmm/fmdd/yyyy') <= '4/22/2016'
And not exists (SELECT USER_ID
               From RESERVATIONS r
               Where r.USER_ID = S.ID
               And EVENT_ID = '56')

Performance

In Oracle there is no performance difference between using not in, not exists or a left join.

Source : https://explainextended.com/2009/09/17/not-in-vs-not-exists-vs-left-join-is-null-oracle/

Oracle's optimizer is able to see that NOT EXISTS, NOT IN and LEFT JOIN / IS NULL are semantically equivalent as long as the list values are declared as NOT NULL.

It uses same execution plan for all three methods, and they yield same results in same time.

Upvotes: 4

Dan Bracuk
Dan Bracuk

Reputation: 20794

This is a formatted comment that is not related to your question.

This is slow:

And TO_CHAR(c.DATEENROLLED,'fmmm/fmdd/yyyy') >= '4/22/2016'

because you are filtering on a function result.

This is logically equivalent and much faster:

And c.DATEENROLLED >= to_date('4/22/2016','fmmm/fmdd/yyyy')

Edit starts here

Aaron D's answer says to use not in. Here are two faster ways to do the same thing:

left join reservations r on s.id = user_id
and r.event_id = '56'
etc
where r.user_id is null

or 

where s.id in 
(
select user_id 
from reservations
minus
select user_id 
from reservations
where event_id = 56
)

Upvotes: 2

Related Questions