How to compare multiple column with same values (Oracle SQL)

Question

I need to compare multiple values of a table with same set of values in single shot without using multiple AND condition in WHERE clause. Please let me know is any methods to do it.

code:

select count(1)
  from test
 where pre_sys_id1 in (select sys_id from test where auto_id = 10)
   and pre_sys_id2 in (select sys_id from test where auto_id = 10)
   and pre_sys_id3 in (select sys_id from test where auto_id = 10)
   and pre_sys_id4 in (select sys_id from test where auto_id = 10)
;

How your output should look?

massko
– massko

2016-11-23 07:35:57 +00:00
Commented Nov 23, 2016 at 7:35 — massko
– massko, Commented Nov 23, 2016 at 7:35

Arkadiusz Łukasiewicz · Accepted Answer · 2016-11-23 07:42:45Z

1

Select count(1)
from test
where  (select sys_id from test where auto_id=10) = all (pre_sys_id1,pre_sys_id2,pre_sys_id3,pre_sys_id4)

Or join

Select count(1)
from test t1
join test t2 on( sys_id= pre_sys_id1 and sys_id= pre_sys_id2 and sys_id= pre_sys_id3  and sys_id= pre_sys_id4) 
where t2.auto_id = 10 ;

answered Nov 23, 2016 at 7:42

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Serg Over a year ago

What if (select sys_id from test where auto_id=10) returns multiple rows?

Arkadiusz Łukasiewicz Over a year ago

Exception will be raised ( single-row subquery returns more than one row). Construction with all/some works only for scalar values. one_value =,<,> ALL/SOME (set of values)

guthy · Accepted Answer · 2016-11-23 09:37:25Z

1

You can try:

select count(1)
  from test
 where (pre_sys_id1, pre_sys_id2, pre_sys_id3, pre_sys_id4)
   in (select sys_id, sys_id, sys_id, sys_id from test where auto_id = 10)

answered Nov 23, 2016 at 9:37

guthy

3261 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to compare multiple column with same values (Oracle SQL)

code:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

code:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related