Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Using equals or IN

    Hi,
    Can anyway tell me is it better to do:

    SELECT Lastname, workdept
    FROM Employee
    WHERE workdept IN ('E11', 'E21')

    and

    SELECT Lastname, workdept
    FROM Employee
    WHERE workdept = 'E11' OR workdept = 'E12'

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Do an Explain and find out.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Marcus_A View Post
    Do an Explain and find out.
    and this will tell you whether there is a performance difference

    in addition to that consideration, there is the question of query clarity, comprehension, and maintainability -- for these, the IN syntax is ~much~ preferred
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Let's say that (in your example) "IN" will never be less performant than "OR", for any RDBMS and version.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Jan 2006
    Posts
    119
    Hi,
    Just ran two similar queries to above...

    1.
    SELECT EMPID, NAME, DEPT FROM EMPLOYEES WHERE EMPID=0;

    AND

    2.
    SELECT EMPID, NAME, DEPT FROM EMPLOYEES WHERE EMPID IN(0);

    This table is pretty simple has no indexes and very little data.
    Exact same explain / access plan for both queries. Even the number of timerons where the same.

    Then I change EMPID to be an index. Again exact same explain plan for both.

    I am just putting this information so anyone who is reading this thread gets the conclusion that different syntax does not mean different explain plan.

    Cheers.

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    in the first example you stated
    WHERE workdept IN ('E11', 'E21')
    next sample
    WHERE workdept = 'E11' OR workdept = 'E12'
    this will have a different access plan

    this is not the same as single values without OR
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by przytula_guy View Post
    in the first example you stated
    WHERE workdept IN ('E11', 'E21')
    next sample
    WHERE workdept = 'E11' OR workdept = 'E12'
    this will have a different access plan
    wow, if that's true, then DB2 is a totally weird database system

    all the other database systems i've worked with will use the exact same plan for them

    of course, because of the values, they will produce different results, but that's a different issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by przytula_guy View Post
    this will have a different access plan
    I'd say "it may have a different access plan, depending on many things".

Posting Permissions

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