Re: Outer Joins

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