pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
outer join syntax
4+ messages / 4 participants
[nested] [flat]

* outer join syntax
@ 2000-03-31 10:56 Sarfaraz Patel <s.patel@ctc.ucl.ac.uk>
  2000-03-31 13:55 ` Re: outer join syntax Peter Eisentraut <e99re41@DoCS.UU.SE>
  0 siblings, 1 reply; 4+ messages in thread

From: Sarfaraz Patel @ 2000-03-31 10:56 UTC (permalink / raw)
  To: pgsql-sql

HI, 
Regarding the syntax for outer join.....i try using this for my query...
select  ran, "pr", "followup"
from view_pr, view_followup
where ran.trialno *=* view_pr.trialno and
ran.trialno *=* view_followup.trialno;

I hope its right..but gives the error missing expression pointing at the *=*
sign...
help would be appreciated
thanx 
saf



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: outer join syntax
  2000-03-31 10:56 outer join syntax Sarfaraz Patel <s.patel@ctc.ucl.ac.uk>
@ 2000-03-31 13:55 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
  0 siblings, 0 replies; 4+ messages in thread

From: Peter Eisentraut @ 2000-03-31 13:55 UTC (permalink / raw)
  To: Sarfaraz Patel <s.patel@ctc.ucl.ac.uk>; +Cc: pgsql-sql

On Fri, 31 Mar 2000, Sarfaraz Patel wrote:

> Regarding the syntax for outer join.....i try using this for my query...
> select  ran, "pr", "followup"
> from view_pr, view_followup
> where ran.trialno *=* view_pr.trialno and
> ran.trialno *=* view_followup.trialno;

Since when is that the outer join syntax? PostgreSQL 7.0 will have support
for SQL outer join syntax but the actual joining isn't implemented yet.

-- 
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Outer Join Syntax
@ 2001-08-01 17:28 ` Joe Conway <joseph.conway@home.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Joe Conway @ 2001-08-01 17:28 UTC (permalink / raw)
  To: Richard Rowell <richard@bowmansystems.com>; pgsql-sql

Subject: [SQL] Outer Join Syntax


> I'm doing a feasability study on porting our flagship product to Postgres
> (from MS_SQL).  I have run across a few snags, the largest of which is the
> outer join syntax.  MS has some nice syntactical sugar with the *=/=*
> operators that Postgres dosen't seem to support.  I am confused on how
> to replicate the behavior however. We often link together many tables via

See http://www.postgresql.org/idocs/index.php?queries.html

You also might want to take a look at
http://www.postgresql.org/idocs/index.php?explicit-joins.html

HTH,

--Joe




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Outer Join Syntax
@ 2001-08-01 18:06 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-08-01 18:06 UTC (permalink / raw)
  To: Richard Rowell <richard@bowmansystems.com>; +Cc: pgsql-sql

"Richard Rowell" <richard@bowmansystems.com> writes:
> outer join syntax.  MS has some nice syntactical sugar with the *=/=*
> operators that Postgres dosen't seem to support.

Some of us view it as "nonstandard and broken", not as "nice syntactical
sugar" ;-).

> I'm just not grasping how one would accomplish the same using the SQL-92
> syntax.

SELECT ...
FROM ASSESSMENT_MEDICAL a
	LEFT JOIN AGENCIES ag ON a.Agency_creating = ag.Agency_id
	LEFT JOIN YESNO_TYPES02 y1 ON a.Health_prob = y1.Yesno_code
	LEFT JOIN ...
WHERE a.inactive != 'Y' AND a.Client_id = $Edit_Client_id;

While this is more typing, it's clear which conditions determine
joinability and which are filters on the overall result, which is a
critical semantic issue that the Oracle/MS syntax fails miserably on.
For example, suppose I want to add a constraint like y1.col3 = 'foo'.
Now, if there are no rows meeting that constraint for a given value of
a.Health_prob = y1.Yesno_code, does that mean I want to have
ASSESSMENT_MEDICAL rows with that Health_prob show up with nulls
substituted for the y1 fields?  Or does it mean that I don't want to see
those rows at all?  AFAICS there's no way to make that distinction with
the Oracle/MS approach.  With the standard syntax, you put the
additional constraint in ON in one case, and in WHERE in the other case.

The standard syntax also allows you to control the join order by
parenthesization, which is not so important for your star-query
example, but is critical if you want to join two outer-join results
together --- otherwise, you can't control which combinations result
in partially-NULL rows out, and which result in no rows out.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-08-01 18:06 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-03-31 10:56 outer join syntax Sarfaraz Patel <s.patel@ctc.ucl.ac.uk>
2000-03-31 13:55 ` Peter Eisentraut <e99re41@DoCS.UU.SE>
2001-08-01 17:28 ` Re: Outer Join Syntax Joe Conway <joseph.conway@home.com>
2001-08-01 18:06 ` Re: Outer Join Syntax Tom Lane <tgl@sss.pgh.pa.us>

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