Any limits to using 'IN' with sub query?

davoutuk · #1 (**permalink**) 07-23-09, 14:46

I have a complex query where I will probably have to block out parts of the overall query into sub queries.

As I'm not that experienced with building large/complex queries, I'm wondering if its valid to use the 'IN' qualifier against a sub query that returns a large result set?

Code:

    SELECT expertName 
    FROM expert 
    WHERE expertid IN (SELECT expertid 
                               FROM xxxxx 
                               WHERE  xxxxxx)

Can the SQL engine handle an IN qualifier where a sub query returns 1,000+ results?

TIA,

.... davout

P.S. I'm wondering whether there should be a separate forum for generic SQL questions like mine?

Pat Phelan · #2 (**permalink**) 07-23-09, 14:56

There is such a forum, and I've moved the post there for you.

As long as you can stand the performance (which probably won't be long), syntactically this use of IN is correct and most if not all SQL engines implement it properly, albiet slowly.

What you really want in a case like this is EXISTS, something like:

Code:

SELECT expertName 
    FROM expert 
    WHERE EXISTS (SELECT expertid 
                               FROM xxxxx 
                               WHERE  xxxxxx
                                    AND subSelectColumn = expertid)

-PatP

n_i · #3 (**permalink**) 07-24-09, 13:12

Quote:

Originally Posted by davoutuk

P.S. I'm wondering whether there should be a separate forum for generic SQL questions like mine?

This is not a generic question, because such limitations are specific to each particular DBMS, which you chose not to disclose.

You could also consider using a join, which will eliminate the need for the IN condition.

sco08y · #4 (**permalink**) 07-24-09, 19:21

A query of the form:

Code:

SELECT TA.whatever 
FROM TA 
WHERE TA.x IN (SELECT TB.y 
                      FROM TB 
                      WHERE expr)

Is equivalent to:

Code:

SELECT TA.whatever
FROM TA INNER JOIN TB ON TA.x = TB.y
WHERE expr

An optimizer can rewrite it as such or simply use the same techniques as joins, in which case it may perform better than an EXISTS query. PostgreSQL, for example is like this:

Quote:

IN / NOT IN subqueries are now much more efficient

In previous releases, IN/NOT IN subqueries were joined to the upper query by sequentially scanning the subquery looking for a match. The 7.4 code uses the same sophisticated techniques used by ordinary joins and so is much faster. An IN will now usually be as fast as or faster than an equivalent EXISTS subquery; this reverses the conventional wisdom that applied to previous releases.

If your system supports the EXPLAIN plan, use that to determine what's going on.

Internet Services

Web Development

Digital Marketing

Consumer Tech