Archives of the TeradataForum
Message Posted: Mon, 10 Feb 2004 @ 00:36:27 GMT
Interesting observation! I just checked your ideas on Oracle and
select count(*) from demo.customer where exists (select max(customer_id) from demo.customer where 1 < 0)
returns 33, which is indeed the full count, the same as in
select count(*) from demo.customer
I then modified the subquery (included the NVL function) to visualize what it returns.
select nvl(max(customer_id), -1) from demo.customer where 1 < 0
returns -1. Meaning that MAX returns NULL here. The fact that something is returned by the subquery makes Oracle EXISTS evaluate to TRUE. Everything is at least internally consistent.
The differences you described can be quite a gotcha with Oracle - TD migrations, so thanks for the warning. For what it's worth, I personally dislike EXISTS in all databases but sometimes it is unavoidable, so it's better to be prepared for those differences.
The query with UNION puzzles me the most, BTW.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|