Message-ID: <3F4E95B9.30204@selectacast.net>
Date: Thu, 28 Aug 2003 19:52:25 -0400
From: Joseph Shraibman <jks@selectacast.net>
Organization: Xtenit, Inc. http://www.xtenit.com
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to join from two tables at once?
References: <20030825201102.T11277-100000@megazone.bigpanda.com>
In-Reply-To: <20030825201102.T11277-100000@megazone.bigpanda.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Stephan Szabo wrote:

> 
> Probably you want something like:
> SELECT u.uid, u.txt, p.val FROM
>  u INNER JOIN a ON (a.id=u.aid)
>  LEFT JOIN p ON (p.uid=u.uid AND p.pkey=a.pkey);

 From the docs:

  A CROSS JOIN or INNER JOIN is a simple Cartesian product, the same as you get from 
listing the two items at the top level of FROM. CROSS JOIN is equivalent to INNER JOIN ON 
(TRUE), that is, no rows are removed by qualification. These join types are just a 
notational convenience, since they do nothing you couldn't do with plain FROM and WHERE.

... so obviously there *is* something that INNER JOIN can do that regular ANDs can't.  But 
I'm still not clear why one works and the other doesn't.