LEFT OUTER JOIN on IDS 9.2
Kevin got different results from an Informix OUTER query and an ANSI LEFT OUTER JOIN on IDS 9.2 when filtering the outer table (c.customer_id = '123'). Dorn Bhechsonggram and Jonathan Leffler explained Informix and ISO outer joins differ. June Hunt quoted Art Kagel: with Informix syntax WHERE filters apply pre-join, while with ANSI syntax WHERE filters apply post-join.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I have two queries that I expect the same
results from. They are as follow:
--Query One
SELECT s.year_month
,s.period_status
,c.customer_id
FROM summary s,OUTER customer_activity c
WHERE s.year_month = c.year_month
AND s.year_month IN('200401','200402','200403')
AND c.customer_id = '123'
ORDER BY s.year_month DESC;
--Query Two
SELECT s.year_month
,s.period_status
,c.customer_id
FROM summary s
LEFT OUTER JOIN customer_activity c ON s.year_month = c.year_month
WHERE s.year_month IN ('200401','200402','200403')
AND c.customer_id = '123'
ORDER BY s.year_month DESC;
Results are:
--Query One Results
year_month| period_status| customer_id
200403 | N |
200402 | N | 123
200401 | R | 123
--Query Two Results
year_month| period_status| customer_id
200402 | N | 123
200401 | R | 123
Am I wrong in thinking that these queries should give me the same results?
What is the difference?
Thanks,
Kevin
Kevin,
The first query is the Informix - Extension Join; however, the second one is
the ANSI Join. They are different which depends on what you are looking
for.
Cheers,
Dorn.
----- Original Message -----
From: "KEVIN" <kslindholm@excite.com>
To: <ids@iiug.org>
Sent: Wednesday, February 11, 2004 11:48 AM
Subject: LEFT OUTER JOIN on IDS 9.2 [2530]
> I have two queries that I expect the same results from. They are as
follow:
> --Query One
> SELECT s.year_month
> ,s.period_status
> ,c.customer_id
> FROM summary s,OUTER customer_activity c
> WHERE s.year_month = c.year_month
> AND s.year_month IN('200401','200402','200403')
> AND c.customer_id = '123'
> ORDER BY s.year_month DESC;>
> --Query Two
> SELECT s.year_month
> ,s.period_status
> ,c.customer_id
> FROM summary s
> LEFT OUTER JOIN customer_activity c ON s.year_month = c.year_month
> WHERE s.year_month IN ('200401','200402','200403')
> AND c.customer_id = '123'
> ORDER BY s.year_month DESC;>
> Results are:
> --Query One Results
> year_month| period_status| customer_id
> 200403 | N |
> 200402 | N | 123
> 200401 | R | 123
>
> --Query Two Results
> year_month| period_status| customer_id
> 200402 | N | 123
> 200401 | R | 123
>
> Am I wrong in thinking that these queries should give me the same results?
What is the difference?
>
> Thanks,
> Kevin
>
>
--0__=08BBE4A4DFEE4EDB8f9e8a93df938690918c08BBE4A4DFEE4EDB
Content-type: multipart/alternative;
Boundary="1__=08BBE4A4DFEE4EDB8f9e8a93df938690918c08BBE4A4DFEE4EDB"
--1__=08BBE4A4DFEE4EDB8f9e8a93df938690918c08BBE4A4DFEE4EDB
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: quoted-printable
You said:
I have two queries that I expect the same results from.
I ask:
Why do you expect the same result? Informix's outer join is a totally
different critter from an ISO outer join. They are superficially simil=
ar,
and there are circumstances where they produce the same result, but the=
y
are very far from being identical. And, in particular, when you place =
a
search criterion on the outer table, Informix and ISO have different vi=
ews
on what the correct answer is. Your code includes the term c.customer=
_id
=3D '123' and c is an alias for the outer join table. Informix will pr=
eserve
all the rows in the inner table that meet the criteria regardless of
whether there are rows in the outer table that match - it sort of does =
the
outer join twice. ISO does the outer join once, and then filters the
result. Your results are consistent with that. The explanation of wha=
t
Informix does is incredibly difficult.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
|---------+---------------------------->
| | "KEVIN" |
| | <kslindholm@excit|
| | e.com> |
| | Sent by: |
| | forum.subscriber@|
| | iiug.org |
| | |
| | |
| | 02/11/2004 11:48 |
| | AM |
|---------+---------------------------->
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
--|
| =
=
|
| To: ids@iiug.org =
=
|
| cc: =
=
|
| Subject: LEFT OUTER JOIN on IDS 9.2 [2530] =
=
|
>--------------------------------------------------------------------=
-----------------------------------------------------------------------=
--|
I have two queries that I expect the same results from. They are as
follow:
--Query One
SELECT s.year_month
,s.period_status
,c.customer_id
FROM summary s,OUTER customer_activity c
WHERE s.year_month =3D c.year_month
AND s.year_month IN('200401','200402','200403')
AND c.customer_id =3D '123'
ORDER BY s.year_month DESC;
--Query Two
SELECT s.year_month
,s.period_status
,c.customer_id
FROM summary s
LEFT OUTER JOIN customer_activity c ON s.year_month =3D c.year_month
WHERE s.year_month IN ('200401','200402','200403')
AND c.customer_id =3D '123'
ORDER BY s.year_month DESC;
Results are:
--Query One Results
year_month| period_status| customer_id
200403 | N |
200402 | N | 123
200401 | R | 123
--Query Two Results
year_month| period_status| customer_id
200402 | N | 123
200401 | R | 123
Am I wrong in thinking that these queries should give me the same resul=
ts?
What is the difference?
Thanks,
Kevin
=
--1__=08BBE4A4DFEE4EDB8f9e8a93df938690918c08BBE4A4DFEE4EDB
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable
<html><body>
<p>You said:<br>
<tt>I have two queries that I expect the same results from. </tt>=
<br>
<br>
I ask:<br>
Why do you expect the same result? Informix's outer join is a totally =
different critter from an ISO outer join. They are superficially simil=
ar, and there are circumstances where they produce the same result, but=
they are very far from being identical. And, in particular, when you =
place a search criterion on the outer table, Informix and ISO have diff=
erent views on what the correct answer is. Your code includes the ter=
m c.customer_id =3D '123' and c is an alias for the outer join table. =
Informix will preserve all the rows in the inner table that meet the cr=
iteria regardless of whether there are rows in the outer table that mat=
ch - it sort of does the outer join twice. ISO does the outer join on=
ce, and then filters the result. Your results are consistent with that=
. The explanation of what Informix does is incredibly difficult.<br>
<br>
--<br>
Jonathan Leffler (jleffler@us.ibm.com)<br>
STSM, Informix Database Engineering, IBM Data Management<br>
4100 Bohannon Drive, Menlo Park, CA 94025<br>
Tel: +1 650-926-6921 Tie-Line: 630-6921<br>
"I don't suffer from insanity; I enjoy every minute of it!&q=
uot;<br>
<br>
<br>
<img src=3D"cid:10__=3D08BBE4A4DFEE4EDB8f9e8a93df938@us.ibm.com" width=3D=
"16" height=3D"16" alt=3D"Inactive hide details for "KEVIN" &=
lt;kslindholm@excite.com>">"KEVIN" <kslindholm@excite.c=
om><br>
<br>
<br>
<table V5DOTBL=3Dtrue width=3D"100%" border=3D"0" cellspacing=3D"0" cel=
lpadding=3D"0">
<tr valign=3D"top"><td width=3D"1%"><img src=3D"cid:20__=3D08BBE4A4DFEE=
4EDB8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"72" al=
t=3D""><br>
</td><td style=3D"background-image:url(cid:30__=3D08BBE4A4DFEE4EDB8f9e8=
a93df938@us.ibm.com); background-repeat: no-repeat; " width=3D"1%"><img=
src=3D"cid:20__=3D08BBE4A4DFEE4EDB8f9e8a93df938@us.ibm.com" border=3D"=
0" height=3D"1" width=3D"225" alt=3D""><br>
<ul>
<ul>
<ul>
<ul><b><font size=3D"2">"KEVIN" <kslindholm@excite.com>=
</font></b><br>
<font size=3D"2">Sent by: forum.subscriber@iiug.org</font>
<p><font size=3D"2">02/11/2004 11:48 AM</font></ul>
</ul>
</ul>
</ul>
</td><td width=3D"100%"><img src=3D"cid:20__=3D08BBE4A4DFEE4EDB8f9e8a93=
df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br>
<font size=3D"1" face=3D"Arial"> </font><br>
<font size=3D"2"> To: </font><font size=3D"2">ids@iiug.org</font><br>
<font size=3D"2"> cc: </font><br>
<font size=3D"2"> Subject: </font><font size=3D"2">LEFT OUTER JOIN on I=
DS 9.2 [2530]</font></td></tr>
</table>
<br>
<br>
<tt>I have two queries that I expect the same results from. They =
are as follow:<br>
--Query One<br>
SELECT s.year_month<br>
,s.period_status<br>
,c.customer_id<br>
FROM summary s,OUTER customer_activity c<br>
WHERE s.year_month =3D c.year_month<br>
AND s.year_month IN('200401','200402','200403')<br>
AND c.customer_id =3D '123'<br>
ORDER BY s.year_month DESC;<br>
<br>
--Query Two<br>
SELECT s.year_month<br>
,s.period_status<br>
,c.customer_id<br>
FROM summary s<br>
LEFT OUTER JOIN customer_activity c ON s.year_month =3D c.year_month<b=
r>
WHERE s.year_month IN ('200401','200402','200403')<br
KEVIN wrote:
>I have two queries that I expect the same results from. They are as
>follow:
>--Query One
>SELECT s.year_month
> ,s.period_status
> ,c.customer_id
>FROM summary s,OUTER customer_activity c
>WHERE s.year_month = c.year_month
>AND s.year_month IN('200401','200402','200403')
>AND c.customer_id = '123'
>ORDER BY s.year_month DESC;>
>--Query Two
>SELECT s.year_month
> ,s.period_status
> ,c.customer_id
>FROM summary s
> LEFT OUTER JOIN customer_activity c ON s.year_month = c.year_month
>WHERE s.year_month IN ('200401','200402','200403')
>AND c.customer_id = '123'
>ORDER BY s.year_month DESC;>
>Results are:
>--Query One Results
>year_month| period_status| customer_id
>200403 | N |
>200402 | N | 123
>200401 | R | 123
>
>--Query Two Results
>year_month| period_status| customer_id
>200402 | N | 123
>200401 | R | 123
>
>Am I wrong in thinking that these queries should give me the same results?
>What is the difference?
Taking from Art Kagel's 2003-09-25 post at comp.databases.informix on the
subject:
"Note that when using the Informix syntax for OUTER joins the WHERE clause
filters are ALL applied pre-join. Using ANSI syntax the ON clause filters
are
applied pre-join and the WHERE clause filters are applied post-join."
That accounts for the difference.
--
June Hunt
_________________________________________________________________
Let the advanced features & services of MSN Internet Software maximize your
online time. http://click.atdmt.com/AVE/go/onm00200363ave/direct/01/