Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: brain cramp or bug: inner join returns rows w/null

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

From: Jon Armstrong <noaddress_at_noaddress.org>
Date: Tue, 25 May 2004 23:10:06 -0400
Message-ID: <40b40aec$1_3@corp.newsgroups.com>


Robert,

Try the three queries shown below.

  1. select count(1) from order_line group by order_number having count(1)>1
  2. select count(1) from order group by order_number having count(1)>1

If you find (and you will) any non-zero results above, that's the reason you are seeing a number of rows in your result set greater than the largest table. There's no mystery here. You have duplicates, unless your description is incorrect.

3) select count(1) from order_line where value is null;

This will show null values if your description matches your actual data.

Regards... Jon

> > 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".

Not exactly correct. This is data and criteria dependent. If the criteria provides no actual restriction based on the given data, the result set will contain N1 x N2 records, where N1 and N2 are the total number of rows in each table.

Look for duplicates in your data. (See above)

With proper data and sql criteria, you would see the behavior you were looking for. But that doesn't appear to be the case here.

> 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 )

Re-check your data. According to your description, you do have null values. (See above)

> 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 - 22:10:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US