Printable View
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.
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!
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.