RE: join if there, blank if not

Patrick, Thanks. For some reason, I was typing EXIST and it wasn't
working--it's EXISTS! (OOPS--old habits)

I was talking to Jacques about this. After comparing performance using NOT
EXISTS, NOT IN, and even UNION vs. LEFT OUTER JOIN on an MS SQL 7 Server,
they all run at about the same speed.

(Interestingly, the MS SQL server did a similar query in 30 seconds to what
took 90 seconds on the psql server. The MS SQL server is a 500MHz dual PIII
with 1 GB of RAM and a RAID-5 with 4 9GB SCSI drives. The PSQL server is a
Linux box busy doing many things with a 166 MHz Pentium MMX, 96 MB of RAM,
and a slow IDE HD.)

Henry

-----Original Message-----
From: Patrick Jacquot [mailto:patrick(dot)jacquot(at)anpe(dot)fr]
Sent: Thursday, July 13, 2000 5:59 AM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] join if there, blank if not

Henry Lafleur wrote:

> Jacques,
>
> The problem with using the union in this way is that you get NULLs for a
> number weather or not it has an associated record in calls.
>
> To do a pure outer join, it would be something like this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, nnumber, ndesc
> from numbers
> WHERE nnumber NOT IN (SELECT nnumber FROM calls);
>
> ---
>
> What I have always had trouble with, though, is if you have multiple
fields
> for a primary key. For example, if a customer master table also had
ship-to
> locations as the key and you wanted to get all customers and any orders
for
> that customer, in rough ANSI SQL it would be:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND
> c.ship_to = o.ship_to
>
> then, in the union, it is not clear how to do it:
>
> SELECT c.cust_number, c.ship_to, o.item
> FROM cust c, orders o
> WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to
> UNION
> SELECT cust_number, ship_to, NULL AS item
> FROM cust
> WHERE ???
>
> which I never know what to do at ??? -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders)
> is one choice, but this doesn't help if the ship to doesn't match. We can
> get wild and try -
> WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE
> ship_to = cust.ship_to)
> but if you go to two and three keys, what happens then? It seems like it
> should work if we continue. But how efficiently does this work?
>
> Has anyone examined this problem?
>
> Thanks,
>
> Henry
>
> -----Original Message-----
> From: Jacques Williams [mailto:jacques(at)jacqro(dot)COM]
> Sent: Wednesday, July 12, 2000 9:41 AM
> To: Gary Stainburn
> Cc: pgsql-sql(at)postgresql(dot)org
> Subject: Re: [SQL] join if there, blank if not
>
> Gary,
>
> What you want here is an outer join. The syntax would look something like
> this:
>
> select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc
> from calls c, numbers n
> where c.cnumber=n.nnumber
> union all
> select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc
> from numbers;
>
> (I haven't tried this, but it should work.) For more information on outer
> joins, see Bruce Momjian's book at
> http://www.postgresql.org/docs/aw_pgsql_book/ .
>
> Jacques Williams
>
>
> On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote:
> > Hi all,
> >
> > I just can't get my head round this one so I hope one of you can.
> >
> > I've got two tables, one holding phone calls, and another holding phone
> numbers.
> >
> > I want to do a select where if the number in the calls table exists
> > in the numbers table, the description is included otherwise the
> > description field is blank.
> >
> > Unfortunately, using the select I've tried, if the number is not on
> > the one of the tables, it's ignored.
> >
> > Calls table
> > cdate date
> > ctime time
> > cextn char(3)
> > cnumber x(12)
> >
> > Numbers table
> >
> > nnumber x(12)
> > ndesc x(30)
> >
> > Select I tried.
> >
> > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc
> > from calls c, numbers n where c.cnumber = n.nnumber;
> >
> > -----------------------------------------
> > Gary Stainburn.
> > Work: http://www.ringways.co.uk mailto:gary(dot)stainburn(at)ringways(dot)co(dot)uk
> > REVCOM: http://www.revcom.org.uk mailto:gary(dot)stainburn(at)revcom(dot)org(dot)uk
> > -----------------------------------------
> > Murphy's Laws: (327) The minute before the engineer arrives, the printer
> starts working.
> > -----------------------------------------
> >

hi
for multiple fields in the join condition, i always succeeded wit a
WHERE NOT EXISTS SELECT ...
subselect in the second part of the UNION.
Hoing that may help (although perhaps suboptimal)