Thread: Outer Joins
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
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
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
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/ ------------
"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
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
On Tuesday 23 January 2001 11:30, Bruce Momjian wrote: > Can someone explain why cname and date from table c gets printed in this > query? The query was supposed to be: SELECT * FROM a FULL OUTER JOIN b USING (id) ������� RIGHT OUTER JOIN c USING(id); I missed the end of it when I was writing the email. > > SELECT * FROM a FULL OUTER JOIN b USING (id) <-- no ';' > > 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) > > --------------------------------------------------------------------------- > -- -------- 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/ ------------
Has anybody used Postgres with JRelationalFramework (http://jrf.sourceforge.net/)? Any opinions on it? I have been looking it over, and it seems kind of interesting... If someone has already written a DatabasePolicy Interface for Postgres, could you post it? Thanks, Erik