pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Outer join in postgresql
13+ messages / 13 participants
[nested] [flat]
* Outer join in postgresql
@ 2000-06-12 21:01 Patrick Kay <patk@auctionsolutions.com>
2000-06-12 18:21 ` Re: Outer join in postgresql Peter Vazsonyi <neko@kredit.sth.szif.hu>
2000-06-12 19:23 ` Re: Outer join in postgresql Ed Loehr <eloehr@austin.rr.com>
2000-06-12 21:53 ` Re: Outer join in postgresql Robert B. Easter <reaster@comptechnews.com>
2000-06-13 01:01 ` Re: Outer join in postgresql Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
0 siblings, 4 replies; 13+ messages in thread
From: Patrick Kay @ 2000-06-12 21:01 UTC (permalink / raw)
To: pgsql-sql
I am looking for a way run an outer join in psql. Can anyone help?
Informix has an "OUTER" keyword. I don't see anything like this in the docs
for psql.
Thanks much.
-Pat Kay
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Outer join in postgresql
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
@ 2000-06-12 18:21 ` Peter Vazsonyi <neko@kredit.sth.szif.hu>
3 siblings, 0 replies; 13+ messages in thread
From: Peter Vazsonyi @ 2000-06-12 18:21 UTC (permalink / raw)
To: Patrick Kay <patk@auctionsolutions.com>; +Cc: pgsql-sql
On Mon, 12 Jun 2000, Patrick Kay wrote:
> I am looking for a way run an outer join in psql. Can anyone help?
>
> Informix has an "OUTER" keyword. I don't see anything like this in the docs
> for psql.
>
> Thanks much.
> -Pat Kay
>
Hmmm... I don't now the exact definition of outer join.
I feel it may somethink like this:
SELECT * FROM a WHERE NOT EXISTS
(SELECT id FROM b WHERE b.id=a.id);
This works in postgresql, but doesn't use indices ;(
I mean this runs an sequencial scan on a, and many index scan on b.
The result may be done with a special hash-join, but not in current
versions.
I have a strange sens, in 7.1 it will be...
regards
--
nek;(
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Outer join in postgresql
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
@ 2000-06-12 19:23 ` Ed Loehr <eloehr@austin.rr.com>
2000-06-12 19:49 ` Re: Outer join in postgresql Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 1 reply; 13+ messages in thread
From: Ed Loehr @ 2000-06-12 19:23 UTC (permalink / raw)
To: Patrick Kay <patk@auctionsolutions.com>; +Cc: pgsql-sql
Patrick Kay wrote:
>
> I am looking for a way run an outer join in psql. Can anyone help?
>
> Informix has an "OUTER" keyword. I don't see anything like this in the docs
> for psql.
There are many examples on how to do this in the archives or via
deja.com's power search.
Regards,
Ed Loehr
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Outer join in postgresql
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
2000-06-12 19:23 ` Re: Outer join in postgresql Ed Loehr <eloehr@austin.rr.com>
@ 2000-06-12 19:49 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2000-06-12 19:49 UTC (permalink / raw)
To: Ed Loehr <eloehr@austin.rr.com>; +Cc: Patrick Kay <patk@auctionsolutions.com>; pgsql-sql
See the FAQ. It involves UNION.
> Patrick Kay wrote:
> >
> > I am looking for a way run an outer join in psql. Can anyone help?
> >
> > Informix has an "OUTER" keyword. I don't see anything like this in the docs
> > for psql.
>
> There are many examples on how to do this in the archives or via
> deja.com's power search.
>
> Regards,
> Ed Loehr
>
--
Bruce Momjian | http://www.op.net/~candle
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Outer join in postgresql
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
@ 2000-06-12 21:53 ` Robert B. Easter <reaster@comptechnews.com>
3 siblings, 0 replies; 13+ messages in thread
From: Robert B. Easter @ 2000-06-12 21:53 UTC (permalink / raw)
To: Patrick Kay <patk@auctionsolutions.com>; +Cc: pgsql-sql
On Mon, 12 Jun 2000, Patrick Kay wrote:
> I am looking for a way run an outer join in psql. Can anyone help?
>
> Informix has an "OUTER" keyword. I don't see anything like this in the docs
> for psql.
>
> Thanks much.
> -Pat Kay
Here is an example I have about simulating an outer join in PostgreSQL:
(hope this is correct, I'm still a bit of a novice but getting better)
CREATE TABLE master (m_id INTEGER, m_name TEXT);
CREATE TABLE slave (m_id INTEGER, s_name TEXT);
INSERT INTO master (m_id, m_name) VALUES (1, 'Satan');
INSERT INTO master (m_id, m_name) VALUES (2, 'God');
INSERT INTO slave (m_id, s_name) VALUES (1, 'Joe');
INSERT INTO slave (m_id, s_name) VALUES (1, 'Tom');
SELECT * FROM master m, slave s
WHERE m.m_id = s.m_id
UNION
SELECT *, NULL, 'no slaves'
FROM master m
WHERE m.m_id NOT IN ( SELECT m_id FROM slave );
m_id | m_name | m_id | s_name
------+--------+------+-----------
1 | Satan | 1 | Joe
1 | Satan | 1 | Tom
2 | God | | no slaves
(3 rows)
-- Oracle outer-join
SELECT * FROM master m, slave s
WHERE m.m_id = s.m_id (+);
m_id | m_name | m_id | s_name
------+--------+------+-----------
1 | Satan | 1 | Joe
1 | Satan | 1 | Tom
2 | God | |
(3 rows)
I keep a file at http://comptechnews.com/~reaster/dbdesign.html that has
some info that might be useful to some people.
--
Robert B. Easter
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Outer join in postgresql
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
@ 2000-06-13 01:01 ` Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
3 siblings, 0 replies; 13+ messages in thread
From: Jesus Aneiros @ 2000-06-13 01:01 UTC (permalink / raw)
To: Patrick Kay <patk@auctionsolutions.com>; +Cc: pgsql-sql
There is no OUTER JOIN in postgres. You could use SELECT and UNION ALL.
Jesus.
On Mon, 12 Jun 2000, Patrick Kay wrote:
> I am looking for a way run an outer join in psql. Can anyone help?
>
> Informix has an "OUTER" keyword. I don't see anything like this in the docs
> for psql.
>
> Thanks much.
> -Pat Kay
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* outer join in PostgreSql
@ 2001-01-15 22:16 Mauricio Hipp Werner <mhipp@mail.pjud.cl>
2001-01-15 21:36 ` Re: outer join in PostgreSql Patrick Welche <prlw1@newn.cam.ac.uk>
2001-01-15 22:21 ` Re: [INTERFACES] outer join in PostgreSql Peter Eisentraut <peter_e@gmx.net>
2001-01-16 05:12 ` Re: outer join in PostgreSql Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-01-16 15:17 ` Re: outer join in PostgreSql Borek Lupoměský <borekl@volny.cz>
0 siblings, 4 replies; 13+ messages in thread
From: Mauricio Hipp Werner @ 2001-01-15 22:16 UTC (permalink / raw)
To: Alexaki Sofia <alexaki@ics.forth.gr>; pgsql-sql; Markus Wagner <wagner@imsd.uni-mainz.de>; +Cc: pgsql-interfaces@postgresql.org
I need help, which is the symbol used in postgreSql to carry out the outer
join.
in oracle the is used (+)
in sybase the is used * and
in postgreSql?
thank you
Hipp Mauricio
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
@ 2001-01-15 21:36 ` Patrick Welche <prlw1@newn.cam.ac.uk>
3 siblings, 0 replies; 13+ messages in thread
From: Patrick Welche @ 2001-01-15 21:36 UTC (permalink / raw)
To: Mauricio Hipp Werner <mhipp@mail.pjud.cl>; +Cc: Alexaki Sofia <alexaki@ics.forth.gr>; pgsql-sql@postgresql.org, Markus Wagner <wagner@imsd.uni-mainz.de>; pgsql-interfaces@postgresql.org
On Mon, Jan 15, 2001 at 06:16:00PM -0400, Mauricio Hipp Werner wrote:
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?
I don't really understand your question, but from src/test/regress/sql/join.sql
some examples of use are:
--
-- Outer joins
-- Note that OUTER is a noise word
--
SELECT '' AS "xxx", *
FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL RIGHT JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL FULL JOIN J2_TBL USING (i);
SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);
SELECT '' AS "xxx", *
FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);
Cheers,
Patrick
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [INTERFACES] outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
@ 2001-01-15 22:21 ` Peter Eisentraut <peter_e@gmx.net>
3 siblings, 0 replies; 13+ messages in thread
From: Peter Eisentraut @ 2001-01-15 22:21 UTC (permalink / raw)
To: Mauricio Hipp Werner <mhipp@mail.pjud.cl>; +Cc: pgsql-sql
Mauricio Hipp Werner writes:
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?
No symbol, just words.
http://www.postgresql.org/devel-corner/docs/postgres/sql-select.htm
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
@ 2001-01-16 05:12 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-01-16 16:44 ` Re: outer join in PostgreSql Hannu Krosing <hannu@tm.ee>
3 siblings, 1 reply; 13+ messages in thread
From: Thomas Lockhart @ 2001-01-16 05:12 UTC (permalink / raw)
To: Mauricio Hipp Werner <mhipp@mail.pjud.cl>; +Cc: Alexaki Sofia <alexaki@ics.forth.gr>; pgsql-sql@postgresql.org, Markus Wagner <wagner@imsd.uni-mainz.de>; pgsql-interfaces@postgresql.org
Mauricio Hipp Werner wrote:
>
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
>
> in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?
The PostgreSQL outer join is accomplished using SQL92 syntax. You will
not find real outer joins *except* in the current beta release, and
beware that there may be some tweaks to the grammar to help with
conformance to the standard.
In any case, check the standard or try something like "select * from t1
left outer join t2 on (i)".
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
2001-01-16 05:12 ` Re: outer join in PostgreSql Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 2001-01-16 16:44 ` Hannu Krosing <hannu@tm.ee>
2001-01-17 17:14 ` Re: outer join in PostgreSql Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 13+ messages in thread
From: Hannu Krosing @ 2001-01-16 16:44 UTC (permalink / raw)
To: Mauricio Hipp Werner <mhipp@mail.pjud.cl>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Alexaki Sofia <alexaki@ics.forth.gr>; pgsql-sql@postgresql.org, Markus Wagner <wagner@imsd.uni-mainz.de>; pgsql-interfaces@postgresql.org
Thomas Lockhart wrote:
>
> Mauricio Hipp Werner wrote:
> >
> > I need help, which is the symbol used in postgreSql to carry out the outer
> > join.
> >
> > in oracle the is used (+)
> > in sybase the is used * and
> > in postgreSql?
>
> The PostgreSQL outer join is accomplished using SQL92 syntax. You will
> not find real outer joins *except* in the current beta release, and
> beware that there may be some tweaks to the grammar to help with
> conformance to the standard.
>
> In any case, check the standard or try something like "select * from t1
> left outer join t2 on (i)".
To get a feel you could use MS Access visual query builder and then view
the source.
I have not checked it lately, but it very likely produces SQL92
compliant outer joins.
-----------
Hannu
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
2001-01-16 05:12 ` Re: outer join in PostgreSql Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-01-16 16:44 ` Re: outer join in PostgreSql Hannu Krosing <hannu@tm.ee>
@ 2001-01-17 17:14 ` Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 0 replies; 13+ messages in thread
From: Ross J. Reedstrom @ 2001-01-17 17:14 UTC (permalink / raw)
To: pgsql-sql; pgsql-interfaces@postgresql.org
On Tue, Jan 16, 2001 at 06:44:18PM +0200, Hannu Krosing wrote:
>
> To get a feel you could use MS Access visual query builder and then view
> the source.
> I have not checked it lately, but it very likely produces SQL92
> compliant outer joins.
>
I fired up MS-Access 97SR1, just to see, and here's one result:
SELECT Institution.InstitutionName, InstituteAssignment.PersonID FROM
Institution LEFT JOIN InstituteAssignment ON Institution.InstID =
InstituteAssignment.InstID;
I'm surprised: looks pretty standard, to me.
Ross
--
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers
and users independent of economic motivations. Jim Flynn, Sunnyvale, Calif.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: outer join in PostgreSql
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
@ 2001-01-16 15:17 ` Borek Lupoměský <borekl@volny.cz>
3 siblings, 0 replies; 13+ messages in thread
From: Borek Lupoměský @ 2001-01-16 15:17 UTC (permalink / raw)
To: pgsql-sql
On Mon, 15 Jan 2001, Mauricio Hipp Werner wrote:
MHW> in oracle the is used (+)
MHW> in sybase the is used * and
MHW> in postgreSql?
In PostgreSQL we have no outer joins at all. But it is promised they
are going to arrive soon, perhaps in 7.1?
Bye Borek
--
=====================================================================
BOREK LUPOMESKY Usti nad Labem, Czech Republic, Europe
WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB
==========[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===========
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2001-01-17 17:14 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-12 21:01 Outer join in postgresql Patrick Kay <patk@auctionsolutions.com>
2000-06-12 18:21 ` Peter Vazsonyi <neko@kredit.sth.szif.hu>
2000-06-12 19:23 ` Ed Loehr <eloehr@austin.rr.com>
2000-06-12 19:49 ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-12 21:53 ` Robert B. Easter <reaster@comptechnews.com>
2000-06-13 01:01 ` Jesus Aneiros <aneiros@jagua.cfg.sld.cu>
2001-01-15 22:16 outer join in PostgreSql Mauricio Hipp Werner <mhipp@mail.pjud.cl>
2001-01-15 21:36 ` Re: outer join in PostgreSql Patrick Welche <prlw1@newn.cam.ac.uk>
2001-01-15 22:21 ` Re: [INTERFACES] outer join in PostgreSql Peter Eisentraut <peter_e@gmx.net>
2001-01-16 05:12 ` Re: outer join in PostgreSql Thomas Lockhart <lockhart@alumni.caltech.edu>
2001-01-16 16:44 ` Re: outer join in PostgreSql Hannu Krosing <hannu@tm.ee>
2001-01-17 17:14 ` Re: outer join in PostgreSql Ross J. Reedstrom <reedstrm@rice.edu>
2001-01-16 15:17 ` Re: outer join in PostgreSql Borek Lupoměský <borekl@volny.cz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox