Results 1 to 3 of 3

Thread: Problems with IN, ANY, EXISTS and ALL

  1. #1
    Join Date
    Oct 2007
    Posts
    2

    Problems with IN, ANY, EXISTS and ALL

    Hello:

    I want to share with you all an investigations I made related to the SQL weakness and interesting characteristics of this language on database processing in the following site:

    www.dbtheoryinpractice.com

    All the examples in this work have been verified with the DB2's SQL language. The situation is practically the same and in some cases still worse in other DBMS SQL

    I hope it’s useful.

  2. #2
    Join Date
    Oct 2007
    Posts
    2

    Red face

    In place of
    SELECT A.ACC
    FROM DEPOSIT A
    WHERE A.AMOUNT > ALL (SELECT B.AMOUNT
    FROM DEPOSIT B, ACCOUNT C
    WHERE C.ACC = B.ACC
    AND C.BNK = ‘BC’)


    Why not use :
    SELECT A.ACC
    FROM DEPOSIT A
    WHERE A.AMOUNT > (SELECT MAX(B.AMOUNT)
    FROM DEPOSIT B, ACCOUNT C
    WHERE C.ACC = B.ACC
    AND C.BNK = ‘BC’)


    This is will give you the desired result without having to worry about nulls (you can use the COALESCE function to get rid of the nulls) but using ALL has always made life difficult and it doesn't make sense!

  3. #3
    Join Date
    Oct 2007
    Posts
    2
    Thank you very much for your suggestion.

    The point is that the condition ALL gives us TRUE when it has the empty set as its only element, this may be a problem that can lead us to misinterpret a query result. I said maybe because this behaviour is ok from the perspective of logic.

Posting Permissions

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