Very Computer

by **Peter Ga** » Thu, 31 Jan 2002 03:37:14

Fellow Ingres Types,

No doubt most of you (who are beyond Ingres 6.4) have made use of the
new join syntax and specifically used LEFT or RIGHT Outer joins. I have
been explaining this syntax to some recent converts to the Ingres faith
and as usual when I got to FULL join I got stuck. I can explain what it
does and I can illustrate it with some contrived examples. What I cannot
do is show a genuine, real world example of a FULL JOIN.

That is the challenge. Can someone show a genuine use for the FULL JOIN.
I need table specs, sql and a description of what the query is supposed
to be showing. The main rule in this challenge is that you must really
need to run this query. It is not sufficient to simply code up a FULL
JOIN to show what it does.

Thanks in anticipation.

Peter Gale
Director
PJG Computer Services Ltd
+44 (0)1398 341491 (o)
+44 (0)7831 513181 (m)

by **Ronald Jening** » Thu, 31 Jan 2002 04:36:34

Hi,

I used a full outer join shortly for testing the differences between two
"should-be-identical" tables in both directions at once:

select *
from taba a full join tabb b
on a.col1 = b.col1
and a.col2 = b.col2
...
where a.col1 is null
or b.col1 is null

Is this of any use for you ?
(since the tables were small, performance was not an issue, so I ignored
this aspect).

Ronald

> Fellow Ingres Types,

> No doubt most of you (who are beyond Ingres 6.4) have made use of the
> new join syntax and specifically used LEFT or RIGHT Outer joins. I have
> been explaining this syntax to some recent converts to the Ingres faith
> and as usual when I got to FULL join I got stuck. I can explain what it
> does and I can illustrate it with some contrived examples. What I cannot
> do is show a genuine, real world example of a FULL JOIN.

> That is the challenge. Can someone show a genuine use for the FULL JOIN.
> I need table specs, sql and a description of what the query is supposed
> to be showing. The main rule in this challenge is that you must really
> need to run this query. It is not sufficient to simply code up a FULL
> JOIN to show what it does.

> Thanks in anticipation.

> Peter Gale
> Director
> PJG Computer Services Ltd
> +44 (0)1398 341491 (o)
> +44 (0)7831 513181 (m)

by **Chris Daw** » Thu, 04 Apr 2002 03:00:06

Ok, I am up for a challenge:

Bi-directional referential integrity check.

SELECT
IFNULL(test1.name,'Not in Test1'),
IFNULL(test2.name,'Not in Test2')
FROM test1 FULL JOIN test2 ON test1.name=test2.name
WHERE test1.tid IS NULL
OR test2.tid IS NULL;

Whether this is any faster than doing the check other ways would need
testing.

I personally HATE the syntax of the outer joins (the lack of examples
in the SQL manual does not help), but in my above code, for a row to
be returned it needs test1.tid IS NULL or test2.tid IS NULL. These
are both non-nullable columns, and so neither condition can ever be
satisfied, can it ?

Since the outer join is defining an intermediate results table, and it
is its values which are NULL, we could have something like:
SELECT
IFNULL(test_join.test1.name,'Not in Test1'),
IFNULL(test_join.test2.name,'Not in Test2')
FROM test_join = (test1 FULL JOIN test2 ON test1.name=test2.name)
WHERE test_join.test1.tid IS NULL
OR test_join.test2.tid IS NULL;
and then it is clear that we are testing values from the join and not
from the tables.
(I used TID in the IS NULL tests to make it very clear that this must
be from the join, as most of us remember that TIDs are always
non-nullable).

Chris Dawe

>Fellow Ingres Types,

>No doubt most of you (who are beyond Ingres 6.4) have made use of the
>new join syntax and specifically used LEFT or RIGHT Outer joins. I have
>been explaining this syntax to some recent converts to the Ingres faith
>and as usual when I got to FULL join I got stuck. I can explain what it
>does and I can illustrate it with some contrived examples. What I cannot
>do is show a genuine, real world example of a FULL JOIN.

>That is the challenge. Can someone show a genuine use for the FULL JOIN.
>I need table specs, sql and a description of what the query is supposed
>to be showing. The main rule in this challenge is that you must really
>need to run this query. It is not sufficient to simply code up a FULL
>JOIN to show what it does.

>Thanks in anticipation.

>Peter Gale
>Director
>PJG Computer Services Ltd
>+44 (0)1398 341491 (o)
>+44 (0)7831 513181 (m)

--
Chris Dawe
Ingres Consultant

by **Peter Ga** » Thu, 04 Apr 2002 17:47:36

In the outer join syntax the conditions in the where clause are applied
to the result of the outer join. Hence it is possible for any column on
the 'optional' side of the join to be NULL. In a full join that is both
sides. Your query would return any rows where the join failed either way
so it's syntactically correct and, if (Big If) you had two tables that
referenced each other on the same column, it would be a logical test of
all the failing references. However I refer you to my original post. I
am looking for a genuine example so if you can show me a case where two
tables reference each other on the same column then you have a genuine
example.

I must say I like the idea of assigning something akin to a correlation
name for each join to improve the readability of the query.

Peter Gale

PJG Computer Services Ltd

>-----Original Message-----

>Sent: 02 April 2002 19:00

>Subject: Re: The Outer Join Challenge

>Ok, I am up for a challenge:

>Bi-directional referential integrity check.

>SELECT
> IFNULL(test1.name,'Not in Test1'),
> IFNULL(test2.name,'Not in Test2')
>FROM test1 FULL JOIN test2 ON test1.name=test2.name
>WHERE test1.tid IS NULL
>OR test2.tid IS NULL;

>Whether this is any faster than doing the check other ways
>would need testing.

>I personally HATE the syntax of the outer joins (the lack of
>examples in the SQL manual does not help), but in my above
>code, for a row to be returned it needs test1.tid IS NULL or
>test2.tid IS NULL. These are both non-nullable columns, and
>so neither condition can ever be satisfied, can it ?

>Since the outer join is defining an intermediate results
>table, and it is its values which are NULL, we could have
>something like: SELECT
> IFNULL(test_join.test1.name,'Not in Test1'),
> IFNULL(test_join.test2.name,'Not in Test2')
>FROM test_join = (test1 FULL JOIN test2 ON test1.name=test2.name)
>WHERE test_join.test1.tid IS NULL
>OR test_join.test2.tid IS NULL;
>and then it is clear that we are testing values from the join
>and not from the tables. (I used TID in the IS NULL tests to
>make it very clear that this must be from the join, as most of
>us remember that TIDs are always non-nullable).

>Chris Dawe

>>Fellow Ingres Types,

>>No doubt most of you (who are beyond Ingres 6.4) have made use of the
>>new join syntax and specifically used LEFT or RIGHT Outer
>joins. I have
>>been explaining this syntax to some recent converts to the
>Ingres faith
>>and as usual when I got to FULL join I got stuck. I can
>explain what it
>>does and I can illustrate it with some contrived examples. What I
>>cannot do is show a genuine, real world example of a FULL JOIN.

>>That is the challenge. Can someone show a genuine use for the FULL
>>JOIN. I need table specs, sql and a description of what the query is
>>supposed to be showing. The main rule in this challenge is that you
>>must really need to run this query. It is not sufficient to
>simply code
>>up a FULL JOIN to show what it does.

>>Thanks in anticipation.

>>Peter Gale
>>Director
>>PJG Computer Services Ltd
>>+44 (0)1398 341491 (o)
>>+44 (0)7831 513181 (m)

>--
>Chris Dawe
>Ingres Consultant

by **Tim Ell** » Fri, 05 Apr 2002 18:47:27

> >>That is the challenge. Can someone show a genuine use for the FULL
> >>JOIN. I need table specs, sql and a description of what the query is
> >>supposed to be showing. The main rule in this challenge is that you
> >>must really need to run this query. It is not sufficient to
> simply code
> >>up a FULL JOIN to show what it does.

I had a quick look through some old SQL and found this, which is a
genuine example, although it certainly isn't the only way of getting
the result (I strongly suspect it was done this way as much to show
the use of the full join as anything else) - it is, however fairly
small and self contained...

Table OT (in reality this table was a session table built up from a
number of queries, but for simplicity I'll show it as a base table
here...)
Fields Employee_no, Year_Week, Day_no, OT_Code, OT_Time

This table contains the amount of Overtime booked by an employee on a
particular day, held as Year_week and a Day Number where Saturday is
Day 1.

Create two temporary tables to add up the total amount of Week day and
Week end overtime

declare global temporary table session.sum_wd as
select employee_no,year_week,sum(ot_time) as "OT_WDAY"
from ot
where day_no > 2
and ot_code = 'P'
group by employee_no,year_week
on commit preserve rows with norecovery,structure=btree,
key = (year_week,employee_no);

declare global temporary table session.sum_we as
select employee_no,year_week,sum(ot_time) as "OT_Wend"
from ot
where day_no < 2 and ot_code = 'P'
group by employee_no,year_week
on commit preserve rows with norecovery,structure=btree,
key = (year_week,employee_no);

Then "Full Join" them into a table which will show the total amount of
weekday and/or weekend Overtime for any employee who has worked
either...

declare global temporary table session.sums as
select a.employee_no,a.year_week,ifnull(ot_wday,0) as "OT_WDAY",
ifnull(ot_wend,0) as "OT_WEND",b.employee_no as "EMP_NO",b.year_week
as
"YW"
from session.sum_wd a full join session.sum_we b
on a.employee_no = b.employee_no and
a.year_week = b.year_week
on commit preserve rows with norecovery,structure=btree
,key=(year_week,employee_no);

Finally replace the "null" key values in this table from the "other"
set of values...

update session.sums
set year_week = yw, employee_no = emp_no
where year_week is null;

This table can now be used to drive reports / further queries

by **Mark Fren** » Tue, 20 Aug 2002 18:53:46

Peter,

I've got an example of a FULL OUTER JOIN:

There are two tables:
the first table containes the amount of apples ;) a person should have
the second table containes the amount of apples a person actually has
received

It's not a given that a person in the first table must exist in the second
table and
likewise, the other way round

So I need all persons from table one AND all persons from the second
table.
This is the statement I used:

( KPN means Royal Dutch Apple Inc. ;)

CREATE TABLE tmp_KPNmatch AS
SELECT ifnull(tk1.speler, tk2.speler) AS
speler,
ifnull(tk1.uit_te_geven, 0) AS
uit_te_geven,
ifnull(tk2.uitgegeven, 0) AS
uitgegeven,
(ifnull(tk1.uit_te_geven, 0) - ifnull(tk2.uitgegeven, 0)) AS
verschil
FROM tmp_KPN1 tk1
FULL OUTER
JOIN tmp_KPN2 tk2 on tk1.speler = tk2.speler
WITH location = (db01, db02, db03),
structure = BTREE,
key = ( speler );

This works like a charm and I used it (just now) real-life...

Dutch groeten,
Mark Frenay

> -----Oorspronkelijk bericht-----

> Tim Ellis
> Verzonden: donderdag 4 april 2002 11:47

> Onderwerp: Re: The Outer Join Challenge

> > >>That is the challenge. Can someone show a genuine use for the FULL
> > >>JOIN. I need table specs, sql and a description of what the query is
> > >>supposed to be showing. The main rule in this challenge is that you
> > >>must really need to run this query. It is not sufficient to
> > simply code
> > >>up a FULL JOIN to show what it does.

> I had a quick look through some old SQL and found this, which is a
> genuine example, although it certainly isn't the only way of getting
> the result (I strongly suspect it was done this way as much to show
> the use of the full join as anything else) - it is, however fairly
> small and self contained...

> Table OT (in reality this table was a session table built up from a
> number of queries, but for simplicity I'll show it as a base table
> here...)
> Fields Employee_no, Year_Week, Day_no, OT_Code, OT_Time

> This table contains the amount of Overtime booked by an employee on a
> particular day, held as Year_week and a Day Number where Saturday is
> Day 1.

> Create two temporary tables to add up the total amount of Week day and
> Week end overtime

> declare global temporary table session.sum_wd as
> select employee_no,year_week,sum(ot_time) as "OT_WDAY"
> from ot
> where day_no > 2
> and ot_code = 'P'
> group by employee_no,year_week
> on commit preserve rows with norecovery,structure=btree,
> key = (year_week,employee_no);

> declare global temporary table session.sum_we as
> select employee_no,year_week,sum(ot_time) as "OT_Wend"
> from ot
> where day_no < 2 and ot_code = 'P'
> group by employee_no,year_week
> on commit preserve rows with norecovery,structure=btree,
> key = (year_week,employee_no);

> Then "Full Join" them into a table which will show the total amount of
> weekday and/or weekend Overtime for any employee who has worked
> either...

> declare global temporary table session.sums as
> select a.employee_no,a.year_week,ifnull(ot_wday,0) as "OT_WDAY",
> ifnull(ot_wend,0) as "OT_WEND",b.employee_no as "EMP_NO",b.year_week
> as
> "YW"
> from session.sum_wd a full join session.sum_we b
> on a.employee_no = b.employee_no and
> a.year_week = b.year_week
> on commit preserve rows with norecovery,structure=btree
> ,key=(year_week,employee_no);

> Finally replace the "null" key values in this table from the "other"
> set of values...

> update session.sums
> set year_week = yw, employee_no = emp_no
> where year_week is null;

> This table can now be used to drive reports / further queries

by **Richard Beav** » Thu, 22 Aug 2002 14:49:23

Here's another entry in the Outer Join Reality Challenge:-

Our section pays a fuel subsidy to licensees known as BEUs. Each BEU
licensee has a unique licence_id.

We automatically send out a return form to our licensees for
completion at the end of each financial year. This is recorded in the
rfs_beu_return_xtract table. Other return forms are sent out manually
and are not recorded in the rfs_beu_return_xtract table.

However, all completed return forms are sent back here for processing
and are entered in the rfs_beu_return table. Thus we receive completed
returns that we have not recorded as sent.

To report who has received/returned a form, we need a FULL JOIN - all
those who have been sent an automatic return by us and all those who
have replied. These are intersecting sets but not complete or partial
sub-sets.

SELECT ifnull(a.return_id, 0) AS k_return_id,
ifnull(a.return_end_date, b.return_end_date) AS
c_return_end_date,
ifnull(a.fg_pp_return, b.fg_pp_return) AS c_fg_pp_return,
ifnull(b.date_exported, '') AS
c_date_exported,
ifnull(a.date_imported, '') AS c_date_imported
FROM (rfs_beu_return a FULL JOIN rfs_beu_return_xtract b
ON a.licence_id = b.licence_id
AND a.return_end_date = b.return_end_date)
ORDER BY k_return_id DESC;

Hope this helps
Richard

> Peter,

> I've got an example of a FULL OUTER JOIN:

> There are two tables:
> the first table containes the amount of apples ;) a person should have
> the second table containes the amount of apples a person actually has
> received

> It's not a given that a person in the first table must exist in the second
> table and
> likewise, the other way round

> So I need all persons from table one AND all persons from the second
> table.
> This is the statement I used:

> ( KPN means Royal Dutch Apple Inc. ;)

> CREATE TABLE tmp_KPNmatch AS
> SELECT ifnull(tk1.speler, tk2.speler) AS
> speler,
> ifnull(tk1.uit_te_geven, 0) AS
> uit_te_geven,
> ifnull(tk2.uitgegeven, 0) AS
> uitgegeven,
> (ifnull(tk1.uit_te_geven, 0) - ifnull(tk2.uitgegeven, 0)) AS
> verschil
> FROM tmp_KPN1 tk1
> FULL OUTER
> JOIN tmp_KPN2 tk2 on tk1.speler = tk2.speler
> WITH location = (db01, db02, db03),
> structure = BTREE,
> key = ( speler );

> This works like a charm and I used it (just now) real-life...

> Dutch groeten,
> Mark Frenay

> > -----Oorspronkelijk bericht-----

> > Tim Ellis
> > Verzonden: donderdag 4 april 2002 11:47

> > Onderwerp: Re: The Outer Join Challenge

> > > >>That is the challenge. Can someone show a genuine use for the FULL
> > > >>JOIN. I need table specs, sql and a description of what the query is
> > > >>supposed to be showing. The main rule in this challenge is that you
> > > >>must really need to run this query. It is not sufficient to
> simply code
> > > >>up a FULL JOIN to show what it does.

> > I had a quick look through some old SQL and found this, which is a
> > genuine example, although it certainly isn't the only way of getting
> > the result (I strongly suspect it was done this way as much to show
> > the use of the full join as anything else) - it is, however fairly
> > small and self contained...

> > Table OT (in reality this table was a session table built up from a
> > number of queries, but for simplicity I'll show it as a base table
> > here...)
> > Fields Employee_no, Year_Week, Day_no, OT_Code, OT_Time

> > This table contains the amount of Overtime booked by an employee on a
> > particular day, held as Year_week and a Day Number where Saturday is
> > Day 1.

> > Create two temporary tables to add up the total amount of Week day and
> > Week end overtime

> > declare global temporary table session.sum_wd as
> > select employee_no,year_week,sum(ot_time) as "OT_WDAY"
> > from ot
> > where day_no > 2
> > and ot_code = 'P'
> > group by employee_no,year_week
> > on commit preserve rows with norecovery,structure=btree,
> > key = (year_week,employee_no);

> > declare global temporary table session.sum_we as
> > select employee_no,year_week,sum(ot_time) as "OT_Wend"
> > from ot
> > where day_no < 2 and ot_code = 'P'
> > group by employee_no,year_week
> > on commit preserve rows with norecovery,structure=btree,
> > key = (year_week,employee_no);

> > Then "Full Join" them into a table which will show the total amount of
> > weekday and/or weekend Overtime for any employee who has worked
> > either...

> > declare global temporary table session.sums as
> > select a.employee_no,a.year_week,ifnull(ot_wday,0) as "OT_WDAY",
> > ifnull(ot_wend,0) as "OT_WEND",b.employee_no as "EMP_NO",b.year_week
> > as
> > "YW"
> > from session.sum_wd a full join session.sum_we b
> > on a.employee_no = b.employee_no and
> > a.year_week = b.year_week
> > on commit preserve rows with norecovery,structure=btree
> > ,key=(year_week,employee_no);

> > Finally replace the "null" key values in this table from the "other"
> > set of values...

> > update session.sums
> > set year_week = yw, employee_no = emp_no
> > where year_week is null;

> > This table can now be used to drive reports / further queries