Re: Outer Joins - Mailing list pgsql-general

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@comptechnews.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@comptechnews.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@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026