simple but surprising SQL example

Hi! I posted the following article to comp.databases.sybase a few days ago. One follow-up article says that INGRES doesn't have the problem I described. Another follow-up suspects that Sybase optimized table `reg' out in Query 2 because no column of `reg' table was SELECT'ed.

I wonder how Oracle does. Could experts here kindly give me a hint?

• original article in comp.databases.sybase -----------------

In the following isql session, I got some surprising results.

1. Query1 returns exactly what I expected, i.e., three rows. The two "john" in the returned rows are for the two different students both named "john" in `student' table.
2. Why does Query2 return two rows? I thought Query2 conceptually applies the WHERE clause on the following Cartesian product of tables `student' and `reg':

	1> select * from student, reg
	2> go
	 student_id  student_name student_id  course     
	 ----------- ------------ ----------- ---------- 
	     1111111 dave             1111111 intro      
	     2222222 john             1111111 intro      
	     9999999 john             1111111 intro      
	     1111111 dave             2222222 advanced   
	     2222222 john             2222222 advanced   
	     9999999 john             2222222 advanced   
	
	(6 rows affected)

   Since there are 4 rows in the above Cartesian product that satisfy the    WHERE clause, shouldn't Query2 have returned 4 rows like this:

	 student_name 
 	 ------------ 
	 dave
	 john
	 dave
	 john

   instead of 2 rows ????????  

3) Query3 is Query2 plus one more column (i.e., `course') in the

   SELECT clause. The result looks good. But what's so magic between    Query2 and Query3?

Could anyone give an explanation?

• isql session ------------------------------

2> create table student 
3> (	student_id	int,
4> 	student_name	char(10)
5> )

2> create table reg 
3> (	student_id	int,
4> 	course		char(10)

2> insert student values(1111111, "dave")
3> insert student values(2222222, "john")
4> insert student values(9999999, "john")
5> 
6> insert reg values(1111111, "intro")
7> insert reg values(2222222, "advanced")

10> SELECT student_name		/*.... Query 1 ....*/
11> FROM
12> 	student

 student_name

 dave         
 john         
 john         

(3 rows affected)

1> 
2> SELECT student_name 		/*.... Query 2 ....*/
3> FROM
4> 	student, reg
5> WHERE
6> 	student.student_id in (SELECT student_id FROM reg)

 student_name

 dave         
 john         

(2 rows affected)

1> 
2> SELECT student_name, course 	/*.... Query 3 ....*/
3> FROM
4> 	student, reg
5> WHERE
6> 	student.student_id in (SELECT student_id FROM reg)
 student_name course     
 ------------ ---------- 
 dave         intro      
 dave         advanced   
 john         intro      
 john         advanced   

(4 rows affected)

-- 

Vincent Q. Yin
umyin_at_ccu.umanitoba.ca