pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOUTER JOINS in PostgreSQL
6+ messages / 5 participants
[nested] [flat]
* OUTER JOINS in PostgreSQL
@ 1999-06-02 18:30 ant9000@c0a111.science.unitn.it
0 siblings, 0 replies; 6+ messages in thread
From: ant9000@c0a111.science.unitn.it @ 1999-06-02 18:30 UTC (permalink / raw)
To: pgsql-sql
Hi, I was trying to do an apparently simple task: I have two tables, say
master
id | name
---------
1 | Alpha
2 | Beta
3 | Gamma
detail
-------
id | lastvisit | info
---------------------
1 | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Wed Jun 02 19:45:08 1999 CEST | some more blah's
Now, I'd like to list all of the fields of table 'master', together with the
fields lastvisit and info from 'detail' if they have a corresponding value:
ie, I'd like some SQL that gives me
id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
2 | Beta | |
3 | Gamma | |
The best I was able to obtain is this:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id
UNION
SELECT *,NULL AS lastvisit,NULL AS info FROM master
WHERE id NOT IN (SELECT id FROM detail);
which is (at best) unelegant; in MS Access you could do something like
SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
ON master.id=detail.id;
With Oracle, there's an even shorter solution:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id(+);
Is anything like that available with PostgreSQL?
Thanks in advance,
Ant9000
^ permalink raw reply [nested|flat] 6+ messages in thread
* OUTER JOINs in PostgreSQL
@ 1999-06-02 20:47 Ant9000 <ant9000@seldati.it>
1999-06-02 21:08 ` Re: [SQL] OUTER JOINs in PostgreSQL Jason Earl <jearl@box100.com>
1999-06-06 12:06 ` Re: [SQL] OUTER JOINs in PostgreSQL Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 6+ messages in thread
From: Ant9000 @ 1999-06-02 20:47 UTC (permalink / raw)
To: pgsql-sql
Hi, I was trying to do an apparently simple task: I have two tables, say
master
id | name
---------
1 | Alpha
2 | Beta
3 | Gamma
detail
-------
id | lastvisit | info
---------------------
1 | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Wed Jun 02 19:45:08 1999 CEST | some more blah's
Now, I'd like to list all of the fields of table 'master', together with the
fields lastvisit and info from 'detail' if they have a corresponding value:
ie, I'd like some SQL that gives me
id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
2 | Beta | |
3 | Gamma | |
The best I was able to obtain is this:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id
UNION
SELECT *,NULL AS lastvisit,NULL AS info FROM master
WHERE id NOT IN (SELECT id FROM detail);
which is (at best) unelegant; in MS Access you could do something like
SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
ON master.id=detail.id;
With Oracle, there's an even shorter solution:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id(+);
Is anything like that available with PostgreSQL?
Thanks in advance,
Ant9000
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] OUTER JOINs in PostgreSQL
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
@ 1999-06-02 21:08 ` Jason Earl <jearl@box100.com>
1999-06-05 13:56 ` Re: [SQL] OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1 sibling, 1 reply; 6+ messages in thread
From: Jason Earl @ 1999-06-02 21:08 UTC (permalink / raw)
To: ant9000@seldati.it; +Cc: pgsql-sql
I think that you are looking for something like:
SELECT m.id, m.name, d.lastvisit, d.info
FROM master m, detail d
WHERE m.id = d.id;
Jason
Hi, I was trying to do an apparently simple task: I have two tables, say
master
id | name
---------
1 | Alpha
2 | Beta
3 | Gamma
detail
-------
id | lastvisit | info
---------------------
1 | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Wed Jun 02 19:45:08 1999 CEST | some more blah's
Now, I'd like to list all of the fields of table 'master', together with the
fields lastvisit and info from 'detail' if they have a corresponding value:
ie, I'd like some SQL that gives me
id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
2 | Beta | |
3 | Gamma | |
The best I was able to obtain is this:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id
UNION
SELECT *,NULL AS lastvisit,NULL AS info FROM master
WHERE id NOT IN (SELECT id FROM detail);
which is (at best) unelegant; in MS Access you could do something like
SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
ON master.id=detail.id;
With Oracle, there's an even shorter solution:
SELECT master.*,detail.lastvisit,detail.info FROM master,detail
WHERE master.id=detail.id(+);
Is anything like that available with PostgreSQL?
Thanks in advance,
Ant9000
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] OUTER JOINs in PostgreSQL
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-02 21:08 ` Re: [SQL] OUTER JOINs in PostgreSQL Jason Earl <jearl@box100.com>
@ 1999-06-05 13:56 ` Ant9000 <ant9000@seldati.it>
1999-06-14 20:46 ` Re: [SQL] OUTER JOINs in PostgreSQL Fomichev Michael <fomichev@null.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Ant9000 @ 1999-06-05 13:56 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jason Earl <jearl@box100.com>
On Wed, 02 Jun 1999, you wrote:
>I think that you are looking for something like:
>
>SELECT m.id, m.name, d.lastvisit, d.info
> FROM master m, detail d
> WHERE m.id = d.id;
>
>Jason
>
No, that way I get an inner join: in my example the result would be
id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
and I'm instead looking for this output:
id | name | lastvisit | info
-----------------------------
1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
2 | Beta | |
3 | Gamma | |
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] OUTER JOINs in PostgreSQL
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-02 21:08 ` Re: [SQL] OUTER JOINs in PostgreSQL Jason Earl <jearl@box100.com>
1999-06-05 13:56 ` Re: [SQL] OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
@ 1999-06-14 20:46 ` Fomichev Michael <fomichev@null.ru>
0 siblings, 0 replies; 6+ messages in thread
From: Fomichev Michael @ 1999-06-14 20:46 UTC (permalink / raw)
To: Ant9000 <ant9000@seldati.it>; +Cc: pgsql-sql
On Sat, 5 Jun 1999, Ant9000 wrote:
> On Wed, 02 Jun 1999, you wrote:
> >I think that you are looking for something like:
> >
> >SELECT m.id, m.name, d.lastvisit, d.info
> > FROM master m, detail d
> > WHERE m.id = d.id;
> >
> >Jason
> >
>
> No, that way I get an inner join: in my example the result would be
>
> id | name | lastvisit | info
> -----------------------------
> 1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
>
> and I'm instead looking for this output:
>
> id | name | lastvisit | info
> -----------------------------
> 1 | Alpha | Wed Jun 02 19:43:08 1999 CEST | blah blah
> 1 | Alpha | Wed Jun 02 19:45:08 1999 CEST | some more blah's
> 2 | Beta | |
> 3 | Gamma | |
>
Try this:
SELECT m.id, m.name, d.lastvisit, d.info
FROM master m, detail d
WHERE m.id = d.id
UNION ALL
SELECT m.id, m.name, 0, 0
FROM master m
WHERE not m.id=ANY(select id from detail);
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] OUTER JOINs in PostgreSQL
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
@ 1999-06-06 12:06 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 6+ messages in thread
From: Herouth Maoz @ 1999-06-06 12:06 UTC (permalink / raw)
To: Ant9000 <ant9000@seldati.it>; pgsql-sql
At 23:47 +0300 on 02/06/1999, Ant9000 wrote:
> The best I was able to obtain is this:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id
> UNION
> SELECT *,NULL AS lastvisit,NULL AS info FROM master
> WHERE id NOT IN (SELECT id FROM detail);
>
> which is (at best) unelegant; in MS Access you could do something like
>
> SELECT master.*,detail.lastvisit,detail.info FROM master LEFT JOIN detail
> ON master.id=detail.id;
>
> With Oracle, there's an even shorter solution:
>
> SELECT master.*,detail.lastvisit,detail.info FROM master,detail
> WHERE master.id=detail.id(+);
>
>
> Is anything like that available with PostgreSQL?
No, outer joins are not supported in PostgreSQL. Your solution is the
accepted workaround, although I tend to recommend WHERE NOT EXISTS ( select
* from detail WHERE detail.id = master.id )in the second clause - it is
more efficient.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-06-14 20:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-02 18:30 OUTER JOINS in PostgreSQL ant9000@c0a111.science.unitn.it
1999-06-02 20:47 OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-02 21:08 ` Re: [SQL] OUTER JOINs in PostgreSQL Jason Earl <jearl@box100.com>
1999-06-05 13:56 ` Re: [SQL] OUTER JOINs in PostgreSQL Ant9000 <ant9000@seldati.it>
1999-06-14 20:46 ` Re: [SQL] OUTER JOINs in PostgreSQL Fomichev Michael <fomichev@null.ru>
1999-06-06 12:06 ` Re: [SQL] OUTER JOINs in PostgreSQL Herouth Maoz <herouth@oumail.openu.ac.il>
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