Error while trying to mimic intersection

Question

I am trying to mimic the query(for academical purpose)

(select course_id from section where semester = 'Spring' and year = 2010) 
intersect
(select course_id from section where semester = 'Fall' and year = 2009)

Successfully mimicked with

select t.course_id from section t, section s where s.course_id = t.course_id and
s.semester = 'Spring' and s.year = 2010 and t.semester = 'Fall' and t.year = 2009;

and when I tried these,

select t.course_id from section t, section s where s.course_id = t.course_id and
(s.semester, s.year, t.semester,t.year) in ('Spring',2010,'Fall',2009);

error at the parenthesis after in predicate(as per the row and column mentioned in error), error is ORA-00920: invalid relational operator 00920. 00000 - "invalid relational operator"

then I tried

select t.course_id from section t, section s where 
s.course_id = t.course_id and (s.semester,s.year) = ('Spring',2010) 
and (t.semester, t.year) in ('Fall',2009);


select t.course_id from section t, section s where 
s.course_id = t.course_id and ((s.semester,s.year) in ('Spring',2010)) 
and ((t.semester, t.year) = ('Fall',2009));

with different combination of in and = getting the same error at parenthesis after the first in or =

Is there a limit in mentioning attributes for (..) in/= (...) or using the same table causes this or some other reason?

using Oracle 12c.

Answer 1

First of all stop using "comma join syntax". It is obsolete and has great successor named JOIN. You can read more here: INNER JOIN ON vs WHERE clause.

Second you need to wrap your value with another pair of round brackets:

SELECT t.course_id 
FROM section t 
JOIN section s 
  ON s.course_id = t.course_id
WHERE (s.semester, s.year, t.semester,t.year) IN (('Spring',2010,'Fall',2009));

SqlFiddleDemo

You may ask "Why I need extra round brackets?", think about multiple values in IN clause:

WHERE (col1, col2, col3, col4) IN ((1,2,3,4), (5,6,7,8), (9,10,11,12))

Your confusion may be caused by single value like:

WHERE col IN (1,2,3);
<=>
WHERE (col) IN ((1), (2), (3));