RE: join if there, blank if not - Mailing list pgsql-sql
From | Henry Lafleur |
---|---|
Subject | RE: join if there, blank if not |
Date | |
Msg-id | E332B20358CDD1118D7A00A0C995F75A91442E@XSERVER Whole thread Raw |
In response to | join if there, blank if not (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
List | pgsql-sql |
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.jacquot@anpe.fr] Sent: Thursday, July 13, 2000 5:59 AM To: pgsql-sql@postgresql.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@jacqro.COM] > Sent: Wednesday, July 12, 2000 9:41 AM > To: Gary Stainburn > Cc: pgsql-sql@postgresql.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.stainburn@ringways.co.uk > > REVCOM: http://www.revcom.org.uk mailto:gary.stainburn@revcom.org.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)