user3023588 user3023588 - 7 months ago 11
SQL Question

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?

Answer

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.