Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Unanswered: Any limits to using 'IN' with sub query?

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    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:

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •