Home » SQL & PL/SQL » SQL & PL/SQL » The 2 queries that are supposed to return the same values, return different values:
The 2 queries that are supposed to return the same values, return different values: [message #206851] Fri, 01 December 2006 14:37 Go to next message
fabiozea
Messages: 4
Registered: December 2006
Location: Montreal
Junior Member
are this 2 queries supposed to return the same?
SETUP
--------
SQL> create table a (uno number(8), dos varchar2(10));
Table created.
SQL> create table b (tres number(8), cuatro varchar2(8), cinco number);
Table created.
SQL> create table c (siete number(8), cinco varchar2(2), cuatro
varchar2(2),ocho number);
SQL> insert into b values (1234,null,null);

1 row created.

SQL> commit;

Commit complete.
-------------
Query #1
SQL> select distinct 'x' from a, b, c
2 where a.uno=1234
3 or b.tres=1234
4 or c.siete=1234;

no rows selected
------------
Query #2
SQL> select 'x'
2 from dual
3 where exists (select 'x'
4 from a where uno=1234
5 union all
6 select 'x'
7 from b where tres=1234
8 union all
9 select 'x'
10 from c where siete=1234
11 );

'
-
x

[Updated on: Fri, 01 December 2006 14:38]

Report message to a moderator

Re: The 2 queries that are supposed to return the same values, return different values: [message #206861 is a reply to message #206851] Fri, 01 December 2006 15:34 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As far as I can tell, no - they aren't supposed to return the same result.

Second query's UNION makes sure that this subquery returns something (i.e. is not empty record set); thus, main query will surely select 'x' from dual. If none of WHERE clauses were true, this query would also return nothing.

If you included outer join in the first query, the result would be different. For example:
SELECT 'x'
  FROM A, B, C
 WHERE (A.uno = 1234 OR B.tres = 1234 OR C.siete = 1234)
   AND A.uno(+) = B.tres
   AND C.siete(+) = A.uno
Re: The 2 queries that are supposed to return the same values, return different values: [message #206862 is a reply to message #206851] Fri, 01 December 2006 15:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You join a, b and c in the first query. This doesn't yield a record, because a and c do not contain any records, so the carthesian product of a, b and c doesn't either.
You could even remove the where-clause and still don't get a record back.
Re: The 2 queries that are supposed to return the same values, return different values: [message #206863 is a reply to message #206862] Fri, 01 December 2006 15:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
must... learn... to type faster....
Re: The 2 queries that are supposed to return the same values, return different values: [message #207229 is a reply to message #206851] Mon, 04 December 2006 15:05 Go to previous messageGo to next message
fabiozea
Messages: 4
Registered: December 2006
Location: Montreal
Junior Member
Thank you all for your replies.
Please correct me if I'm wront, but shouldn't the cartesian product be the set of all possible combinations? in this case table B is the only one that has a value, so shouldn't I get one row with nulls except for the column that has a value in B?
So if I do "select * from a,b,c"
shouldn't I get something like:
UNO DOS TRES CUATRO CINCO SIETE CINCO CUATRO OCHO
--- --- ---- ------ ----- ----- ----- ------ ----
1234
thanks
Re: The 2 queries that are supposed to return the same values, return different values: [message #207235 is a reply to message #206851] Mon, 04 December 2006 15:29 Go to previous message
fabiozea
Messages: 4
Registered: December 2006
Location: Montreal
Junior Member
hello, I'm now correcting myself,
"For any set A, the Cartesian product of A and the empty set is empty:
∀A: A ∅ = ∅ "
that's why,
thanks again
Previous Topic: Bulk collect
Next Topic: Datatype for Null
Goto Forum:
  


Current Time: Sat Dec 03 20:37:08 CST 2016

Total time taken to generate the page: 0.07717 seconds