From: Bruno Wolff III <bruno@wolff.to>
To: Ludwig Lim <lud_nowhere_man@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Question on OUTER JOINS.
Date: Sat, 28 Jun 2003 06:22:41 -0500
Message-ID: <20030628112241.GA13700@wolff.to> (raw)
In-Reply-To: <20030628061618.19440.qmail@web80510.mail.yahoo.com>
References: <16131.1056780183@sss.pgh.pa.us>
<20030628061618.19440.qmail@web80510.mail.yahoo.com>
On Fri, Jun 27, 2003 at 23:16:18 -0700,
Ludwig Lim <lud_nowhere_man@yahoo.com> wrote:
>
> Is there way of rewritting :
> SELECT a.status,
> employee_id
> FROM permission a LEFT JOIN
> ( SELECT * FROM employee WHERE employee_id
> =5) as b ON (a.status = b.status)
> WHERE status='test'
>
> into a query that has no subselect in the FROM clause.
> I mean can the query above be rewritten into
> something like:
>
> SELECT a.status,
> b.employee_id
> FROM permission a LEFT JOIN employee b ON
> (a.status = b.status)
> WHERE a.status = 'test' and
> b.employee_id = 5;
The two queries don't produce the same results in general.
For example if there is one record in permission with a status of test and
one record in employee with an employee_id of 6 and a status of test,
then the first query will return one row with a status of test and an
employee_id of null and the second query will return no rows.
query
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, lud_nowhere_man@yahoo.com, tgl@sss.pgh.pa.us
Subject: Re: Question on OUTER JOINS.
In-Reply-To: <20030628112241.GA13700@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox