Re: SQL JOINs vs WHERE statements

Replies are listed 'Best First'.
Re: Re: SQL JOINs vs WHERE statements by bean (Monk) on Aug 18, 2003 at 19:02 UTC
There is nothing silly or unusual about the left outer join in Oracle 8. Left outer join: `table1.column1 (+) = table2.column2` [download] Right outer join: `table1.column1 = table2.column2 (+)` [download] Full outer join: `table1.column1 (+) = table2.column2 (+)` [download] Inner join: `table1.column1 = table2.column2` [download] It isn't the *= syntax, but it is consistent and logical.	[reply] [d/l] [select]
Re: Re: Re: SQL JOINs vs WHERE statements by glwtta (Hermit) on Aug 18, 2003 at 20:48 UTC
Yeah, from what I remember I didn't know about the (+) syntax at the time, and what someone at work showed me was a UNION of an inner join with the values of a "null join" which would give you the rows that didn't join with the second table. Still weird, but not Oracle's fault :) In any case, I haven't done much with Oracle 8 and still know little about it.	[reply]
Re: Re: SQL JOINs vs WHERE statements by mpeppler (Vicar) on Aug 18, 2003 at 18:38 UTC
The resulting data set should be the same in both cases, but you are correct that the SQL optimizer might decide to choose different paths to resolve the query. FYI - in Sybase multi-table queries are optimized 4 tables at a time (by default - this is configurable for recent versions of ASE (12.0 and later, IIRC)). It should be noted that query optimization (and understanding why the server decides to use a certain query plan) is a bit of a black art, especially when working on really large data sets. Michael	[reply]