[SQL] Re: [INTERFACES] outer join in PostgreSql

[Patrick Welche]

On Mon, Jan 15, 2001 at 06:16:00PM -0400, Mauricio Hipp Werner wrote:
> I need help, which is the symbol used in postgreSql to carry out the outer
> join.
> 
>  in oracle the is used (+)
> in sybase the is used * and
> in postgreSql?

I don't really understand your question, but from src/test/regress/sql/join.sql
some examples of use are:

--
-- Outer joins
-- Note that OUTER is a noise word
--

SELECT '' AS "xxx", *
  FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL LEFT JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL RIGHT JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL FULL JOIN J2_TBL USING (i);

SELECT '' AS "xxx", *
  FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1);

SELECT '' AS "xxx", *
  FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1);


Cheers,

Patrick

[SQL] Re: [GENERAL] Help with query. (*)

[Patrick Welche]

On Tue, Jan 16, 2001 at 01:42:45PM -0700, Diehl, Jeffrey wrote:
> I'm having difficulty writing a query which I really can't live without... 
> 
> I need to get a list of records from table A for which there are
> corresponding records in table B.  I've tried to use the intersect clause,
> but it doesn't seem to work, or it runs far too long.  For example:
> 
> select * from A
>   where 1=1
>   intersect select * from A where
>   B.x=A.x
>   and A.y=B.y
>   and A.z=B.z
>   limit 100
> 
> I need the most efficient method possible; my A tables have upward of 5
> Million records.  The B table, btw, only has about 100 records.

Guessing - my system isn't in a state to test just at the minute - is it

select A.* from A,B
 where A.x=B.x
   and A.y=B.y
   and A.z=B.z
 limit 100

that you want?

Re: [SQL] Re: [GENERAL] MySQLs Describe emulator!

[Patrick Welche]

On Tue, Mar 06, 2001 at 10:38:43AM -0500, Michael Fork wrote:
> try starting psql with the -E option -- this displays all queries used
> internally to the screen, i.e.:

Sorry, hadn't read this one before posting... Thanks to the "moderating" it'll
all be out of synch anyway but..

Patrick

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html