Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PUTTING A FILTER IN AN OUTER JOIN QUERY

6195Jan 2 2008 — edited Jan 3 2008

HI,
I have query , which has an outer join . and the outer join table has a filter condition. if i remove the filter , then the query returns a different result set, if i put the filter condition then it does different , though the filter value may not exist in the table . To clarify my point here is a sample table with result set and the queries.

SELECT x.y, x.b FROM x
records from table X

 Y         B                       
 --------  ----------------------- 
 1         2007-05-04 11:49:45.86  
 11111     2007-05-04 11:49:45.86  
 2         2007-05-24 08:31:47.96  
 3         2007-05-24 08:31:55.54  
 4         2007-05-24 08:32:03.67  
 5         2007-05-24 09:02:04.13  
 6         2007-05-24 09:02:08.85  
 6         2007-07-27 15:42:17.603 
 6         2007-07-28 15:42:45.366 

SELECT z.y, z.b FROM z
records from table Z
 Y     B    
 ----  ---- 

 0 record(s) selected

query with the outer join
SELECT X.y, x.b , Z.y, z.b FROM X, Z WHERE X.Y = Z.Y (+) 

 Y         B                        Y       B      
 --------  -----------------------  ------  ------ 
 5         2007-05-24 09:02:04.13   (null)  (null) 
 11111     2007-05-04 11:49:45.86   (null)  (null) 
 1         2007-05-04 11:49:45.86   (null)  (null) 
 6         2007-07-28 15:42:45.366  (null)  (null) 
 6         2007-07-27 15:42:17.603  (null)  (null) 
 6         2007-05-24 09:02:08.85   (null)  (null) 
 4         2007-05-24 08:32:03.67   (null)  (null) 
 2         2007-05-24 08:31:47.96   (null)  (null) 
 3         2007-05-24 08:31:55.54   (null)  (null) 

query with outer join with filter
SELECT X.y, x.b , Z.y, z.b FROM X, Z WHERE X.Y = Z.Y (+) AND Z.CX = 4

 Y     B     Y     B    
 ----  ----  ----  ---- 

 0 record(s) selected

Although I was expecting the last query to return all the records from table X. Am I doing something wrong ? I need to have that filter criteria on table z, but i want the query to return the result set as well as in the third query. All helps appreciated.
thanks

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 31 2008
Added on Jan 2 2008
7 comments
1,269 views