Re: Outer Joins - Mailing list pgsql-general

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/ ------------