Outer join returns incorrect results when using sub-selects

Chris Greening

I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);

insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);

I want to be able to run a query that generates the resultset:

dim1, sum(stat1), sum(stat2)
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL

I was planning to use the following sql:

select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;

When I run this. Instead of the 4 rows I was expecting I get back:

dim1,stat1,stat2
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL

The values for stat2 seem to be getting lost when there is no value for stat1.

Anyone have any ideas as to what is going on? Am I doing something stupid?

Jul 19 '05 #1

Subscribe Post Reply

6386

Ed prochak

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);

insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);

I want to be able to run a query that generates the resultset:

dim1, sum(stat1), sum(stat2)
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL

I was planning to use the following sql:

select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;

When I run this. Instead of the 4 rows I was expecting I get back:

dim1,stat1,stat2
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL

The values for stat2 seem to be getting lost when there is no value for stat1.

Anyone have any ideas as to what is going on? Am I doing something stupid?

I don't see the problem, but I did notice the nvl() function. A NULL
shouldn't occur in the join column (at least not with the data you
presented) so you should just list the key:

select s1.dim1, stat1, stat2 from

I doubt that would fix the other columns, but this is going to be one
of those "intuitive obvious" solutions, i.e., once you know the
answer, you realize it was "intuitive obvious" 8^)

Jul 19 '05 #2

Alex Filonov

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);

insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);

I want to be able to run a query that generates the resultset:

dim1, sum(stat1), sum(stat2)
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL

I was planning to use the following sql:
select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;

When I run this. Instead of the 4 rows I was expecting I get back:

dim1,stat1,stat2
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL

The values for stat2 seem to be getting lost when there is no value for stat1.

Anyone have any ideas as to what is going on? Am I doing something stupid?

What's RDBMS version? I had similar problem with 8.1.6, which disappeared
after upgrade to 8.1.7. There was a workaround too, forcing hash outer
join fixed the query.

Jul 19 '05 #3

Database Guy

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);

insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);

I want to be able to run a query that generates the resultset:

dim1, sum(stat1), sum(stat2)
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL

I was planning to use the following sql:

select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;

When I run this. Instead of the 4 rows I was expecting I get back:

dim1,stat1,stat2
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL

The values for stat2 seem to be getting lost when there is no value for stat1.

Anyone have any ideas as to what is going on? Am I doing something stupid?

No, if you are getting that result with that SQL then you are correct
and Oracle has a serious bug. Which version?
DG

Jul 19 '05 #4

Lori

select dim1,sum(stat1) stat1,sum(stat2) stat2
from (select distinct dim1, stat1, stat2 from
data1 full outer join data2 using (dim1)
order by dim1)
group by dim1;

This way will work.

Lori

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);
insert into data1 values (3,3);
insert into data1 values (6,3);
insert into data1 values (7,3);

insert into data2 values (2,1);
insert into data2 values (2,2);
insert into data2 values (3,2);
insert into data2 values (4,3);
insert into data2 values (5,3);

I want to be able to run a query that generates the resultset:

dim1, sum(stat1), sum(stat2)
1,3,NULL
2,4,3
3,3,2
4,NULL,3
5,NULL,3
6,3,NULL
7,3,NULL

I was planning to use the following sql:

select nvl(s1.dim1, s2.dim1) dim1, stat1, stat2 from
(
select dim1, sum(stat1) stat1 from data1 group by dim1
) s1
full outer join
(
select dim1, sum(stat2) stat2 from data2 group by dim1
) s2 on s1.dim1=s2.dim1
order by dim1;

When I run this. Instead of the 4 rows I was expecting I get back:

dim1,stat1,stat2
1,3,NULL
2,4,3
3,3,2
4,NULL,NULL
5,NULL,NULL
6,3,NULL
7,3,NULL

The values for stat2 seem to be getting lost when there is no value for stat1.

Anyone have any ideas as to what is going on? Am I doing something stupid?

Jul 19 '05 #5

Chris Greening

Hi Ed/Alex, thanks for replying

Ed, here's a simpler example that gives the same problem:

create table test1 (dim1 integer, stat1 float);
create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
(select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;

DIM1 SUM(STAT1) DIM1 SUM(STAT2)
---------- ---------- ---------- ----------
1 1
2

As you can see the SUM(STAT2) column is always null.

If I remove the group by (and the sum) from the subselect I get the correct results:

select * from
(select dim1, stat1 from test1) s1 full outer join
(select dim1, stat2 from test2) s2 on s1.dim1=s2.dim1;

DIM1 STAT1 DIM1 STAT2
---------- ---------- ---------- ----------
1 1
2 1

Unfortunately we need the aggregation in our subselects.

Alex, we are using:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Thanks
Chris.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...
I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);

....

Jul 19 '05 #6

Lori

Try this :

select dim1,sum(stat1) stat1,sum(stat2) stat2
from (select distinct dim1, stat1, stat2 from
data1 full outer join data2 using (dim1)
order by dim1)
group by dim1;
Seems to give the proper output with no nvl surprises.

Lori

Jul 19 '05 #7

Alex Filonov

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

Hi Ed/Alex, thanks for replying

Ed, here's a simpler example that gives the same problem:

create table test1 (dim1 integer, stat1 float);
create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
(select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;

DIM1 SUM(STAT1) DIM1 SUM(STAT2)
---------- ---------- ---------- ----------
1 1
2

As you can see the SUM(STAT2) column is always null.

If I remove the group by (and the sum) from the subselect I get the correct results:

Looks like problem was fixed in 9.2.0.3:

SQL> create table test1 (dim1 integer, stat1 float);
create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
(select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;
Table created.

SQL>
Table created.

SQL> SQL>
1 row created.

SQL>
1 row created.

SQL>
Commit complete.

SQL> SQL> 2 3
DIM1 SUM(STAT1) DIM1 SUM(STAT2)
---------- ---------- ---------- ----------
1 1
2 1

You need to upgrade your server.
select * from
(select dim1, stat1 from test1) s1 full outer join
(select dim1, stat2 from test2) s2 on s1.dim1=s2.dim1;

DIM1 STAT1 DIM1 STAT2
---------- ---------- ---------- ----------
1 1
2 1

Unfortunately we need the aggregation in our subselects.

Alex, we are using:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Thanks
Chris.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...
I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);

...

Jul 19 '05 #8

Chris Greening

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...
I'm seeing a very strange problem with outer joins.

The example below replicates the problem:

create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);

insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);

....

Jun 27 '08 #9

Alex Filonov

ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...

Hi Ed/Alex, thanks for replying

Ed, here's a simpler example that gives the same problem:

create table test1 (dim1 integer, stat1 float);
create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
(select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;

DIM1 SUM(STAT1) DIM1 SUM(STAT2)
---------- ---------- ---------- ----------
1 1
2

As you can see the SUM(STAT2) column is always null.

If I remove the group by (and the sum) from the subselect I get the correct results:

Looks like problem was fixed in 9.2.0.3:

SQLcreate table test1 (dim1 integer, stat1 float);
create table test2 (dim1 integer, stat2 float);

insert into test1 values (1, 1);
insert into test2 values (2, 1);
commit;

select * from
(select dim1, sum(stat1) from test1 group by dim1) s1 full outer join
(select dim1, sum(stat2) from test2 group by dim1) s2 on s1.dim1=s2.dim1;
Table created.

SQL>
Table created.

SQLSQL>
1 row created.

SQL>
1 row created.

SQL>
Commit complete.

SQLSQL 2 3
DIM1 SUM(STAT1) DIM1 SUM(STAT2)
---------- ---------- ---------- ----------
1 1
2 1

You need to upgrade your server.

select * from
(select dim1, stat1 from test1) s1 full outer join
(select dim1, stat2 from test2) s2 on s1.dim1=s2.dim1;

DIM1 STAT1 DIM1 STAT2
---------- ---------- ---------- ----------
1 1
2 1

Unfortunately we need the aggregation in our subselects.

Alex, we are using:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Thanks
Chris.

af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. com>...
ch************@actix.com (Chris Greening) wrote in message news:<57**************************@posting.google. com>...
I'm seeing a very strange problem with outer joins.
>
The example below replicates the problem:
>
create table data1 (dim1 integer, stat1 float);
create table data2 (dim1 integer, stat2 float);
>
insert into data1 values (1,1);
insert into data1 values (1,2);
insert into data1 values (2,2);
insert into data1 values (2,2);

...

Jun 27 '08 #10

Similar topics

full outer join question

by: Dave | last post by:

Hi I have the following 4 tables and I need to do a fully outerjoin on them. create table A (a number, b number, c char(10), primary key (a,b)) create table B (a number, b number, c ...

Oracle Database

help with data transformations with outer join?

by: Preston Landers | last post by:

Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having...

Microsoft SQL Server

OUTER JOIN problem

by: Matt | last post by:

Hello I have to tables ar and arb, ar holds articles and a swedish description, arb holds descriptions in other languages. I want to retreive all articles that match a criteria from ar and...

Microsoft SQL Server

Outer Join not working when using SQL-92

by: thilbert | last post by:

All, I have a perplexing problem that I hope someone can help me with. I have the following table struct: Permission ----------------- PermissionId Permission

Microsoft SQL Server

Diff between "LEFT OUTER JOIN" and " *= " when condition

by: Dam | last post by:

Using SqlServer : Query 1 : SELECT def.lID as IdDefinition, TDC_AUneValeur.VALEURDERETOUR as ValeurDeRetour FROM serveur.Data_tblDEFINITIONTABLEDECODES def,...

Microsoft SQL Server

Can someone help me with multiple "Left Outer Joins"?

by: Steve | last post by:

I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...

Microsoft SQL Server

Outer Join help please

by: Rory Campbell-Lange | last post by:

I'm having troube doing a left outer self join on a table. The sent column shows the number of items sent to each recipient from each source. The received column (generated by the outer join) is...

PostgreSQL Database

Alternatives for "OUTER JOIN"

by: Martin | last post by:

Hello everybody, I have the following question. As a join clause on Oracle we use " table1.field1 = table2.field1 (+) " On SQL Server we use " table1.field1 *= table2.field1 " Does DB2...

DB2 Database

Outer Join possible in Access 2002?

by: deko | last post by:

From what I understand, an Inner Join narrow the rows selected to the table with the least results... and an Outer Join does the opposite... SELECT qryTxToQ1.Q1Total, qryTxToQ2.Q2Total,...

Microsoft Access / VBA

Incorrect results using FULL OUTER JOIN in DB2 v8

by: brunodamato | last post by:

In the example that follows, I am receiving an incorrect result set in the View. I am looking to get the FULL result set from View_LY and Table_TY. Instead, this View is returning the Common (Equi)...

DB2 Database

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA