Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    2

    Question Unanswered: the more performatic sql

    The two sql:

    Number 1:
    SELECT * FROM conteudo_imagem WHERE idConteudo
    IN (SELECT idConteudo FROM conteudo WHERE idGrupo = 9) ORDER BY idImagem


    Number 2:
    select conteudo_imagem.*
    from conteudo_imagem ci
    inner join conteudo c
    on ci.idconteudo = c.idconteudo
    where
    c.idgrupo = 9
    order by ci.idimagem


    The questions:

    The two sql resulting at the same result?
    What of them is more performatic?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i believe you will find that number 2 will give an error

    instead of select conteudo_imagem.* you need to say select ci.*

    otherwise, yes, both will give the same result

    which of them performs better? that depends on your database management system

    this is the ANSI SQL forum, which is more concerned with correctness of the SQL than performance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    2
    Quote Originally Posted by r937
    i believe you will find that number 2 will give an error
    instead of select conteudo_imagem.* you need to say select ci.*otherwise, yes, both will give the same result
    which of them performs better? that depends on your database management system this is the ANSI SQL forum, which is more concerned with correctness of the SQL than performance
    Thanks !!!

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by dreampeppers99
    Number 1:
    SELECT * FROM conteudo_imagem WHERE idConteudo
    IN (SELECT idConteudo FROM conteudo WHERE idGrupo = 9) ORDER BY idImagem


    Number 2:
    select conteudo_imagem.*
    from conteudo_imagem ci
    inner join conteudo c
    on ci.idconteudo = c.idconteudo
    where
    c.idgrupo = 9
    order by ci.idimagem
    There is actually a third equivalent formulation, sometimes more performant than the two others:
    Code:
    SELECT *
    FROM conteudo_imagem ci
    WHERE EXISTS
     (SELECT 1 FROM conteudo
      WHERE idGrupo = 9
      AND  ci.idConteudo = idConteudo)
    ORDER BY idImagem
    --_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
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    which of them performs better? that depends on your database management system
    Not only on the RDBMS, but also on the current status of the two tables (i.e., number of rows, width of the rows) and surroundings (presence of indexes, physical ordering of the data, ...)
    Even with the same tables on the same RDBMS, but at different time instances, a different one from the three formulations will be the most performant one.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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