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

[robert]

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

[Jon Armstrong]

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.

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" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

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! =-----

[robert]

"Jon Armstrong" <no*******@noaddress.org> wrote in message news:<40**********@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" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

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! =-----

[Laconic2]

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

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

I didn't catch this point the first time around. Did you say this earlier?

Test your data to make sure this is true. Maybe there's something about the
data that's different than what you expect.

Also, take a look at Oracle's execution plan to make sure that it's not
doing an outer join, for some reason.

[robert]

"Laconic2" <la******@comcast.net> wrote in message news:<UJ********************@comcast.com>...

"robert" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

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

I didn't catch this point the first time around. Did you say this earlier?

Test your data to make sure this is true. Maybe there's something about the
data that's different than what you expect.

Also, take a look at Oracle's execution plan to make sure that it's not
doing an outer join, for some reason.

if i add a group by i get the "correct" number of rows. ah. this
forces a cursor, which resolves at the row level, etc.

the mystery continues.

robert

[Jon Armstrong]

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" <gn*****@rcn.com> wrote in message
news:da**************************@posting.google.c om...

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! =-----

[Laconic2]

"robert" <gn*****@rcn.comwrote in message
news:da**************************@posting.google.c om...

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

I didn't catch this point the first time around. Did you say this earlier?

Test your data to make sure this is true. Maybe there's something about the
data that's different than what you expect.

Also, take a look at Oracle's execution plan to make sure that it's not
doing an outer join, for some reason.

[robert]

"Laconic2" <la******@comcast.netwrote in message news:<UJ********************@comcast.com>...

"robert" <gn*****@rcn.comwrote in message
news:da**************************@posting.google.c om...

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

I didn't catch this point the first time around. Did you say this earlier?

Test your data to make sure this is true. Maybe there's something about the
data that's different than what you expect.

Also, take a look at Oracle's execution plan to make sure that it's not
doing an outer join, for some reason.

if i add a group by i get the "correct" number of rows. ah. this
forces a cursor, which resolves at the row level, etc.

the mystery continues.

robert