CODEWITHSUNDEEP
sql
Iron3eagle
Iron3eagle

Reputation: 1077

Combine two IN statements into a single clause?

WHERE
    E.CNO IN
    (
    SELECT
        CNO
    FROM
        COURSE
    WHERE
        LOWER(CNAME) LIKE LOWER('%WORLD%')
    AND
        LOWER(CNAME) LIKE LOWER('%HISTORY%')
    )
AND
    C.DNAME IN
    (
    SELECT
        DNAME
    FROM
        COURSE
    WHERE
        LOWER(CNAME) LIKE LOWER('%WORLD%')
    AND
        LOWER(CNAME) LIKE LOWER('%HISTORY%')
    );

This gives me the result I'm looking for, but I'm tad bit OCD and would love to be able to shorten this if possible. Bellow is what I'd like to do, but I'm uncertain as to was operators would work.

WHERE
    E.CNO, C.DNAME IN
    (
    SELECT
        CNO,
        DNAME
    FROM
        COURSE
    WHERE
        LOWER(CNAME) LIKE LOWER('%WORLD%')
    AND
        LOWER(CNAME) LIKE LOWER('%HISTORY%')
    );

Upvotes: 1

Views: 31

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Some databases support multiple columns in the in clause. However, you may find that it can be rewritten using a join or using exists. Here's an example with exists:

where exists (
    select 1
    from course 
    where e.cno = course.cno and 
          c.dname = course.dname and 
          lower(c.cname) like lower('%WORLD%') and
          lower(c.cname) like lower('%HISTORY%')
)

Or you could use a join:

join course on c.dname = course.dname and
           e.cno = course.cno and
           lower(c.cname) like lower('%WORLD%') and 
           lower(c.cname) like lower('%HISTORY%')

No need for a subquery there.

Upvotes: 2

Related Questions