Re: brain cramp or bug: inner join returns rows w/null

"Jon Armstrong" <noaddress_at_noaddress.org> wrote in message news:<40b27e1c$1_4_at_corp.newsgroups.com>...
> Robert,
>
> Brain cramp.
>
> An inner join is essentially a cartesian product of the 2 tables with
> filtering based on your "where" clause.
>
> The number of rows returned could have been as much as 20,000,000.

a failure of old style syntax. the "logic" of the inner join is to return a result set with, at maximum, the number of rows of the larger table. this is why it's "inner". if executed as a nested-loop, it will: read each order_line row, find its order, print the result table row. 20,000 rows. since there is a condition, why does oracle not execute as if a nested-loop??

(still doesn't explain why value could ever be NULL; i've restricted to matching on order_number in both tables. all order_lines have a value )

but, i suspect, because we have to refer to order in the from phrase, we get the product.

if i had 9i around, i could check it. using sql-92 syntax on DB2/UDB, i don't remember running into this.

thanks,
robert

>
> The nulls that you are refering to are fine, since that's not one of the
> columns with which you joined the tables.
>
> Regards... Jon
>
> Jon Armstrong
>
>
> "robert" <gnuoytr_at_rcn.com> wrote in message
> news:da3c2186.0405241314.336dec01_at_posting.google.com...
> > running 8.1.7 server, 8.1.6 client.
> >
> > i *thought* inner join should not return nulls, but not only that,
> > but i get way more rows than i'm expecting.
> >
> > assume:
> >
> > order table:
> > order_number
> >
> > order_line table:
> > order_number
> > order_line_number
> > value
> >
> > assume 1,000 order rows, 20,000 order_line rows all have an
> > order_number and value.
> >
> > the following:
> >
> > select value from order_line, order
> > where order_line.order_number = order.order_number
> >
> > returns rows with value = null
> >
> > ??
> >
> > not only that, but the number of returned rows is app 65,000.
> >
> > brain cramp or bug???
> >
> > thanks,
> > robert
>
>
>
>
> -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Received on Tue May 25 2004 - 14:59:17 CEST