Re: [GENERAL] Simulating an outer join
От | Bruce Bantos |
---|---|
Тема | Re: [GENERAL] Simulating an outer join |
Дата | |
Msg-id | 042e01bf5d3d$5bf256a0$0200a8c0@rsdevelop обсуждение исходный текст |
Ответ на | RE: [GENERAL] Simulating an outer join ("Culberson, Philip" <philip.culberson@dat.com>) |
Список | pgsql-general |
I was originally trying to avoid this, but I think you make a good point. The default value is probably best for this case. Thanks for the solid argument. ----- Original Message ----- From: "Culberson, Philip" <philip.culberson@dat.com> > It seems to me that in this case Bruce would be better off to use a default > value and NOT "simulate" an outer join. > > I suggest the following: > > Instead of using a character abbreviation for the relation, use a number. > Since the list of categories is most likely going to remain small, you can > use an int2. This has two advantages. > > 1) It is then truly divorced from the text description. If you ever change > "Small Business" to "Not Really Big Business", the abbreviation "SB" loses > it's meaning. > > 2) Less storage. Per the user documentation, an int2 takes 2 bytes of > storage. Both char[n] and varchar[n] take 4+n bytes of storage, so even if > com_cat_abbr is NULL, you still burn at least 4 bytes! > > Default the value of com_cat_abbr to 0 and make an appropriate entry in the > company_category table (say, with a com_cat_long value of "Undefined"). > > Since you are already using the lookup table to populate pulldowns, > enforcing that the user makes a choice in your client app should not be a > problem. > > Now you can just do a straight join and not incur the cost of doing a union > or sub-selects, etc. > > Hope this helps. > > Phil Culberson > > -----Original Message----- > From: Mike Mascari [mailto:mascarm@mascari.com] > Sent: Wednesday, January 12, 2000 9:47 AM > To: Bruce Momjian > Cc: PostgreSQL-general > Subject: Re: [GENERAL] Simulating an outer join > > > Bruce Momjian wrote: > > > > I have been thinking about how to simulate an outer join. It seems the > > best way is to do: > > > > SELECT tab1.col1, tab2.col3 > > FROM tab1, tab2 > > WHERE tab1.col1 = tab2.col2 > > UNION ALL > > SELECT tab1.col1, NULL > > FROM tab1 > > WHERE tab1.col1 NOT IN (SELECT tab2.col2 FROM tab2) > > > > Comments? I know someone was asking about this recently. > > > > I wouldn't use IN ;-) > > SELECT table1.key, table2.value > FROM table1, table2 > WHERE table1.key = table2.key > UNION ALL > SELECT table1.key, NULL > FROM table1 WHERE NOT EXISTS > (SELECT table2.key FROM table2 WHERE table1.key = table2.key); > > Mike Mascari > > ************
В списке pgsql-general по дате отправления: