Outer Joins

PostgreSQL 7.1 (which is in beta now) will have outer joins.

It will probably (and hopefully) be released in Jan./Feb., but the beta
seems quite stable to me.

Poul L. Christiansen

Michael Dunn wrote:

Show quoted text

I am relatively new to the list and somewhat new to PostgreSQL. I am
wondering if there has been any discussion or thought about including
outer joins in later releases of PostreSQL. It seems highly inefficient
simulating outer joins with subqueries and UNION ALL statements. Or,
perhaps PostgreSQL has another way prevent unjoined rows from being
supressed in the results. Your comments are welcome.

Thanks, Micael Dunn

Does postgres include this feature? Can someone point me toward the docs?

This is regarding the old clustering thread. If the on-line replication is
implemented, then it could be used for a form of clustering, because the
SELECTs could be distributed across multiple servers (one on each server).
Obviously, this would require the complete database to be on each server,
but until more advanced clustering methods are implemented, I don't see
another sensible way of doing clustering for situations where one server
just cannot feasibly cut it. Besides, insuring completeness of the returned
results when doing a multi-table join across multiple servers is a mind
numbingly hard problem...

I am also guessing that record locking would not work properly if multiple
computers were accessing the same physical database file that is mounted on
a remote file system. Is this the case? Not that I am even thinking about
trying to access data files via NFS or DFS... ;-)

Regards.

Gordan

What is the syntax for this? Is there an example I can see/run?

On Thursday 04 January 2001 04:30, Poul L. Christiansen wrote:

PostgreSQL 7.1 (which is in beta now) will have outer joins.

It will probably (and hopefully) be released in Jan./Feb., but the beta
seems quite stable to me.

Poul L. Christiansen

Michael Dunn wrote:

I am relatively new to the list and somewhat new to PostgreSQL. I am
wondering if there has been any discussion or thought about including
outer joins in later releases of PostreSQL. It seems highly inefficient
simulating outer joins with subqueries and UNION ALL statements. Or,
perhaps PostgreSQL has another way prevent unjoined rows from being
supressed in the results. Your comments are welcome.

Thanks, Micael Dunn

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

"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

On Sat, 6 Jan 2001, Robert B. Easter wrote:

What is the syntax for this? Is there an example I can see/run?

Should follow standard SQL92 syntax (which, BTW, Oralce doesn't):

SELECT * FROM table1 LEFT OUTER JOIN table2 ON (table1.field =
table2.field)

This will return all rows from table1 even if no corresponding row exists
in table2. A RIGHT OUTER JOIN would do the opposite.

-- Brett
http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
It is better to have loved a short man than never to have loved a tall.

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

On Sunday 07 January 2001 00:53, Robert B. Easter wrote:

Union join:
T1 UNION JOIN T2

is not implemented. Nice! :)

[snip]

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

UNION JOIN is deprecated so maybe it doesn't need to be implemented?

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

"Robert B. Easter" <reaster@comptechnews.com> writes:

Just for the heck of it, I tried to execute all this sql on 7.0.3 and got
this:
psql:join2.sql:23: pqReadData() -- backend closed the channel unexpectedly.
I knew it wouldn't run it, but didn't think it would crash.

7.0 had the beginnings of parser support for JOIN syntax, but there
were bugs ... I think the one that you hit had to do with nested JOIN
clauses, ie, a JOIN b JOIN c.

regards, tom lane

"Robert B. Easter" <reaster@comptechnews.com> writes:

UNION JOIN is deprecated

Oh? By whom?

The reason 7.1 doesn't have it is I didn't have time for it, not that
we don't plan to do it ever.

regards, tom lane

"Robert B. Easter" <reaster@comptechnews.com> writes:

UNION JOIN is deprecated

Oh? By whom?

I read it in the SQL spec. ANSI/ISO 9075-2 1999 (final).

It is intended that the following features will be removed at a
later date from a revised version of this part of ISO/IEC 9075:
1) The ability to specify UNION JOIN in a <joined table> has been
deprecated.

Interesting. Maybe we won't ever bother to do it, then.

regards, tom lane

On Sunday 07 January 2001 13:13, Tom Lane wrote:

"Robert B. Easter" <reaster@comptechnews.com> writes:

UNION JOIN is deprecated

Oh? By whom?

The reason 7.1 doesn't have it is I didn't have time for it, not that
we don't plan to do it ever.

I read it in the SQL spec. ANSI/ISO 9075-2 1999 (final).

Annex D

(informative)

Deprecated features

It is intended that the following features will be removed at a
later date from a revised version of this part of ISO/IEC 9075:

1) The ability to specify UNION JOIN in a <joined table> has been
deprecated.

It's the only one. I sent a patch to the patches list for JOIN
documentation. In it, the only thing I said about UNION JOIN is
"Deprecated." Let me know if I should change that. I thought about omitting
it entirely.

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

I am not sure if this is a bug, an oversight or something else entirely,
but it would appear that if there are two tables, Table1 and Table2, which
are joined using INNER JOIN, specifying WHERE = one of the join fields
doesn't automatically get equalised to the other field.

For example:

SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue';

takes a very long time (several minutes), and explain says that sequential
scans are used on both tables.

However, changing the above to:

SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue';

yields the correct answer in a fraction of a second. Explain says that
indices are being used. However, here's a REALLY strange thing. If I do:

SET ENABLE_SEQSCAN = OFF;

and run the first query, explain says that indices are used, but it STILL
takes forever. The first, slow query executes a merge join, while the
second only executes two index scans in a nested loop.

Why? This seems like a fairly basic thing, but it seems to break something
in the way the query is executed...

Regards.

Gordan

"Gordan Bobic" <gordan@freeuk.com> writes:

SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue';
[ is slow, but this is fast: ]
SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1)
WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue';

You're correct, we do not deduce the latter from the former, and so the
first example does not realize that it could use a constrained indexscan
over Table2.

Maybe we should try to do this, but I suspect we'd waste more cycles
trying than we'd make back on the queries where it helps.

regards, tom lane

SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 =
Table2.Field1)
WHERE Table1.Field1 = 'SomeValue';
[ is slow, but this is fast: ]
SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 =
Table2.Field1)
WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue';

You're correct, we do not deduce the latter from the former, and so the
first example does not realize that it could use a constrained indexscan
over Table2.

Maybe we should try to do this, but I suspect we'd waste more cycles
trying than we'd make back on the queries where it helps.

Hmm... As far as I can tell, it would help in any query joining several
tables on the same field in one table. I don't know how this compares to an
"average" case, but it is certainly common in the databases that I'm
working on... It would be nice if there was at least an option to enable
this sort of behaviour.

At the moment, the 6 table joins I am doing are so painfully slow, no
matter how I optimize the queries/views that I am going to forget about the
whole idea of views and set up some code that will do the separate table
subqueries manually. Even with _ALL_ fields that joins are performed on are
indexed in hash or btree, it still takes forever. Seen as I have to do this
often, unless I can find a way to speed up my queries by an order of
magnitude (or several in some cases), I don't see any other way around
this.

If I do a view that produces the data I want through joins, it takes hours,
even with all fields indexed, and after VACUUM ANALYZE. Doing SET ENABLE
SEQ_SCAN = OFF doesn't seem to make any difference. The query plan changes,
but select times are still roughly the same... Doing the separate
subqueries on each table and joining data manualy in the application code
takes literaly seconds. I am sure that cannot be right and I must be doing
something wrong, so if anyone has a good idea of how to solve this type of
problem, I'm not sure I have a lot of options left...

Regards.

Gordan

"Gordan Bobic" <gordan@freeuk.com> writes:

If I do a view that produces the data I want through joins, it takes hours,
even with all fields indexed, and after VACUUM ANALYZE. Doing SET ENABLE
SEQ_SCAN = OFF doesn't seem to make any difference. The query plan changes,
but select times are still roughly the same... Doing the separate
subqueries on each table and joining data manualy in the application code
takes literaly seconds.

Um, have you updated since I changed the subselect-restriction
processing two days ago?

regards, tom lane

If I do a view that produces the data I want through joins, it takes

hours,

even with all fields indexed, and after VACUUM ANALYZE. Doing SET

ENABLE

SEQ_SCAN = OFF doesn't seem to make any difference. The query plan

changes,

but select times are still roughly the same... Doing the separate
subqueries on each table and joining data manualy in the application

code

takes literaly seconds.

Um, have you updated since I changed the subselect-restriction
processing two days ago?

Not yet. I've done it in appliction code for now. I'll try again in
straight SQL when the next beta or release are available.

Thanks.

Gordan

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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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

Say what?

test=# CREATE TABLE a (id INTEGER, name TEXT, aname TEXT);
CREATE
test=# CREATE TABLE b (id INTEGER, name TEXT, bname TEXT);
CREATE
test=# CREATE TABLE c (id INTEGER, name TEXT, cname TEXT, date DATE DEFAULT
test(# CURRENT_DATE);
CREATE
test=# SELECT * FROM a FULL OUTER JOIN b USING (id);
id | name | aname | name | bname
----+------+-------+------+-------
(0 rows)

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

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

Say what?

test=# CREATE TABLE a (id INTEGER, name TEXT, aname TEXT);
CREATE
test=# CREATE TABLE b (id INTEGER, name TEXT, bname TEXT);
CREATE
test=# CREATE TABLE c (id INTEGER, name TEXT, cname TEXT, date DATE DEFAULT
test(# CURRENT_DATE);
CREATE
test=# SELECT * FROM a FULL OUTER JOIN b USING (id);
id | name | aname | name | bname
----+------+-------+------+-------
(0 rows)

The text of the email showed this query returning columns from table c.
Seems it was just a mistake in the email.

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