Re: Outer Joins

Can someone explain why cname and date from table c gets printed in this
query?

Thanks.

> SELECT * FROM a FULL OUTER JOIN b USING (id)
>
> id | name | aname | name | bname | name | cname | date
> ----+------+--------+------+--------+------+---------+------------
> 1 | Bob | aname1 | Bob | bname1 | Bob | cname1 | 2001-01-07
> 2 | Jim | aname2 | Tom | bname2 | Jim | cname2 | 2001-01-07
> 9 | | | | | Tom | cname9 | 2001-01-07
> 10 | | | | | | cname10 | 2001-01-07
> (4 rows)
>

---------------------------------------------------------------------------

[ Charset ISO-8859-1 unsupported, converting... ]
> On Saturday 06 January 2001 20:21, Tom Lane wrote:
> > "Robert B. Easter" <reaster(at)comptechnews(dot)com> writes:
> > > What is the syntax for this? Is there an example I can see/run?
> >
> > SQL92 standard.
> >
> > See
> > http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm
> > for documentation (such as it is). There are some examples in the
> > join regression test, too.
> >
> > regards, tom lane
>
> Thanks. I've tested out the cvs version and see that these joins appear to
> work:
>
> Qualified join:
> T1 INNER|[LEFT|RIGHT|FULL OUTER] JOIN T2 ON|USING() ...
>
> Natural join:
> T1 NATURAL INNER|[LEFT|RIGHT|FULL OUTER] JOIN T2
>
> Cross join:
> T1 CROSS JOIN T2
>
> But,
>
> Union join:
> T1 UNION JOIN T2
>
> is not implemented. Nice! :)
>
>
> Here is a sample running of all this on cvs pgsql:
>
> CREATE TABLE a (id INTEGER, name TEXT, aname TEXT);
> CREATE TABLE b (id INTEGER, name TEXT, bname TEXT);
> CREATE TABLE c (id INTEGER, name TEXT, cname TEXT, date DATE DEFAULT
> CURRENT_DATE);
>
> INSERT INTO a VALUES (1, 'Bob', 'aname1');
> INSERT INTO a VALUES (2, 'Jim', 'aname2');
> INSERT INTO a VALUES (3, 'Tom', 'aname3');
> INSERT INTO a VALUES (7, 'Joe', 'aname7');
> INSERT INTO a VALUES (8, null, 'aname8');
>
> INSERT INTO b VALUES (1, 'Bob', 'bname1');
> INSERT INTO b VALUES (2, 'Tom', 'bname2');
> INSERT INTO b VALUES (3, 'Joe', 'bname3');
> INSERT INTO b VALUES (5, 'Jim', 'bname5');
> INSERT INTO b VALUES (6, null, 'bname6');
>
> INSERT INTO c VALUES (1, 'Bob', 'cname1');
> INSERT INTO c VALUES (2, 'Jim', 'cname2');
> INSERT INTO c VALUES (9, 'Tom', 'cname9');
> INSERT INTO c VALUES (10, null, 'cname10');
>
> -- Qualified Joins
> SELECT * FROM a JOIN b USING (id) JOIN c USING (id);
> SELECT * FROM a INNER JOIN b ON (a.id = b.id);
> SELECT * FROM a LEFT OUTER JOIN b USING(id, name);
> SELECT * FROM a RIGHT OUTER JOIN b USING (id);
> SELECT * FROM a FULL OUTER JOIN b USING (id)
> RIGHT OUTER JOIN c USING(id);
> -- Natural Joins
> SELECT * FROM a NATURAL INNER JOIN b;
> SELECT * FROM a NATURAL LEFT OUTER JOIN b;
> SELECT * FROM a NATURAL RIGHT OUTER JOIN b;
> SELECT * FROM a NATURAL FULL OUTER JOIN b;
> -- Cross Join
> SELECT * FROM a CROSS JOIN b;
> -- Union Join (not implemented, yet)
> SELECT * FROM a UNION JOIN b;
>
>
> The output is like this with cvs version:
> --------------------------------------------------------
>
> CREATE TABLE a (id INTEGER, name TEXT, aname TEXT);
> CREATE
> CREATE TABLE b (id INTEGER, name TEXT, bname TEXT);
> CREATE
> CREATE TABLE c (id INTEGER, name TEXT, cname TEXT, date DATE DEFAULT
> CURRENT_DATE);
> CREATE
> INSERT INTO a VALUES (1, 'Bob', 'aname1');
> INSERT 21117 1
> INSERT INTO a VALUES (2, 'Jim', 'aname2');
> INSERT 21118 1
> INSERT INTO a VALUES (3, 'Tom', 'aname3');
> INSERT 21118 1
> INSERT INTO a VALUES (7, 'Joe', 'aname7');
> INSERT 21119 1
> INSERT INTO a VALUES (8, null, 'aname8');
> INSERT 21120 1
>
> INSERT INTO b VALUES (1, 'Bob', 'bname1');
> INSERT 21121 1
> INSERT INTO b VALUES (2, 'Tom', 'bname2');
> INSERT 21122 1
> INSERT INTO b VALUES (3, 'Joe', 'bname3');
> INSERT 21122 1
> INSERT INTO b VALUES (5, 'Jim', 'bname5');
> INSERT 21122 1
> INSERT INTO b VALUES (6, null, 'bname6');
> INSERT 21123 1
>
> INSERT INTO c VALUES (1, 'Bob', 'cname1');
> INSERT 21124 1
> INSERT INTO c VALUES (2, 'Jim', 'cname2');
> INSERT 21125 1
> INSERT INTO c VALUES (9, 'Tom', 'cname9');
> INSERT 21126 1
> INSERT INTO c VALUES (10, null, 'cname10');
> INSERT 21127 1
>
> SELECT * FROM a JOIN b USING (id) JOIN c USING (id);
>
> id | name | aname | name | bname | name | cname | date
> ----+------+--------+------+--------+------+--------+------------
> 1 | Bob | aname1 | Bob | bname1 | Bob | cname1 | 2001-01-07
> 2 | Jim | aname2 | Tom | bname2 | Jim | cname2 | 2001-01-07
> (2 rows)
>
> SELECT * FROM a INNER JOIN b ON (a.id = b.id);
>
> id | name | aname | id | name | bname
> ----+------+--------+----+------+--------
> 1 | Bob | aname1 | 1 | Bob | bname1
> 2 | Jim | aname2 | 2 | Tom | bname2
> 3 | Tom | aname3 | 3 | Joe | bname3
> (3 rows)
>
> SELECT * FROM a LEFT OUTER JOIN b USING(id, name);
>
> id | name | aname | bname
> ----+------+--------+--------
> 1 | Bob | aname1 | bname1
> 2 | Jim | aname2 |
> 3 | Tom | aname3 |
> 7 | Joe | aname7 |
> 8 | | aname8 |
> (5 rows)
>
> SELECT * FROM a RIGHT OUTER JOIN b USING (id);
>
> id | name | aname | name | bname
> ----+------+--------+------+--------
> 1 | Bob | aname1 | Bob | bname1
> 2 | Jim | aname2 | Tom | bname2
> 3 | Tom | aname3 | Joe | bname3
> 5 | | | Jim | bname5
> 6 | | | | bname6
> (5 rows)
>
> SELECT * FROM a FULL OUTER JOIN b USING (id)
>
> id | name | aname | name | bname | name | cname | date
> ----+------+--------+------+--------+------+---------+------------
> 1 | Bob | aname1 | Bob | bname1 | Bob | cname1 | 2001-01-07
> 2 | Jim | aname2 | Tom | bname2 | Jim | cname2 | 2001-01-07
> 9 | | | | | Tom | cname9 | 2001-01-07
> 10 | | | | | | cname10 | 2001-01-07
> (4 rows)
>
> SELECT * FROM a NATURAL INNER JOIN b;
>
> id | name | aname | bname
> ----+------+--------+--------
> 1 | Bob | aname1 | bname1
> (1 row)
>
> SELECT * FROM a NATURAL LEFT OUTER JOIN b;
>
> id | name | aname | bname
> ----+------+--------+--------
> 1 | Bob | aname1 | bname1
> 2 | Jim | aname2 |
> 3 | Tom | aname3 |
> 7 | Joe | aname7 |
> 8 | | aname8 |
> (5 rows)
>
> SELECT * FROM a NATURAL RIGHT OUTER JOIN b;
>
> id | name | aname | bname
> ----+------+--------+--------
> 1 | Bob | aname1 | bname1
> 2 | Tom | | bname2
> 3 | Joe | | bname3
> 5 | Jim | | bname5
> 6 | | | bname6
> (5 rows)
>
> SELECT * FROM a NATURAL FULL OUTER JOIN b;
>
> id | name | aname | bname
> ----+------+--------+--------
> 1 | Bob | aname1 | bname1
> 2 | Jim | aname2 |
> 2 | Tom | | bname2
> 3 | Joe | | bname3
> 3 | Tom | aname3 |
> 5 | Jim | | bname5
> 6 | | | bname6
> 7 | Joe | aname7 |
> 8 | | aname8 |
> (9 rows)
>
> SELECT * FROM a CROSS JOIN b;
>
> id | name | aname | id | name | bname
> ----+------+--------+----+------+--------
> 1 | Bob | aname1 | 1 | Bob | bname1
> 1 | Bob | aname1 | 2 | Tom | bname2
> 1 | Bob | aname1 | 3 | Joe | bname3
> 1 | Bob | aname1 | 5 | Jim | bname5
> 1 | Bob | aname1 | 6 | | bname6
> 2 | Jim | aname2 | 1 | Bob | bname1
> 2 | Jim | aname2 | 2 | Tom | bname2
> 2 | Jim | aname2 | 3 | Joe | bname3
> 2 | Jim | aname2 | 5 | Jim | bname5
> 2 | Jim | aname2 | 6 | | bname6
> 3 | Tom | aname3 | 1 | Bob | bname1
> 3 | Tom | aname3 | 2 | Tom | bname2
> 3 | Tom | aname3 | 3 | Joe | bname3
> 3 | Tom | aname3 | 5 | Jim | bname5
> 3 | Tom | aname3 | 6 | | bname6
> 7 | Joe | aname7 | 1 | Bob | bname1
> 7 | Joe | aname7 | 2 | Tom | bname2
> 7 | Joe | aname7 | 3 | Joe | bname3
> 7 | Joe | aname7 | 5 | Jim | bname5
> 7 | Joe | aname7 | 6 | | bname6
> 8 | | aname8 | 1 | Bob | bname1
> 8 | | aname8 | 2 | Tom | bname2
> 8 | | aname8 | 3 | Joe | bname3
> 8 | | aname8 | 5 | Jim | bname5
> 8 | | aname8 | 6 | | bname6
> (25 rows)
>
> SELECT * FROM a UNION JOIN b;
>
> ERROR: UNION JOIN is not implemented yet
> psql:/home/reaster/sql/join/join.sql:37: ERROR: UNION JOIN is not
> implemented yet
> --------------------------------------------------------
>
>
> Just for the heck of it, I tried to execute all this sql on 7.0.3 and got
> this:
>
> jointest=# \i join2.sql
> CREATE
> CREATE
> CREATE
> INSERT 2836025 1
> INSERT 2836026 1
> INSERT 2836027 1
> INSERT 2836028 1
> INSERT 2836029 1
> INSERT 2836030 1
> INSERT 2836031 1
> INSERT 2836032 1
> INSERT 2836033 1
> INSERT 2836034 1
> INSERT 2836035 1
> INSERT 2836036 1
> INSERT 2836037 1
> INSERT 2836038 1
> psql:join2.sql:23: pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> psql:join2.sql:23: connection to server was lost
>
>
> I knew it wouldn't run it, but didn't think it would crash.
>
> --
> -------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
> -- CompTechNews Message Board http://www.comptechnews.com/ --
> -- CompTechServ Tech Services http://www.comptechserv.com/ --
> ---------- http://www.comptechnews.com/~reaster/ ------------
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026