| Stephan Szabo 2005-08-28, 11:23 am |
| On Sun, 28 Aug 2005, Tang Tim Hei wrote:
>
> Let me explain my point in more detail below:
>
> The following commands are little different from the previous one.
> (1) select A.* from test.currency A, test.price_list B where
> A.curr_cd=B.curr_cd and A.curr_cd='USD'
> (2) select A.* from test.currency A, test.price_list B, test.country C
> where A.curr_cd=B.curr_cd and A.curr_cd='USD'
>
> For command (1), it is ok. The result is what I expect.
> However, for command (2), it has problem. I added the "test.country C"
> to it, here I actually just write a table name to it and no more other
> purpose. However, the result maybe totally different. If the table
> "country" is not empty, the result is just the same as in command (1)
> but if "country" is empty, there are no result row.
Theoretically, it's not the same if country has multiple rows given the
query above. The second query is simply incorrect if you want one row for
each currency that has a price list and is USD.
> The point is that: even though I add a constraint to a command, if an
> additional empty table is mentioned in the command, the result may be
> different.
Right, the result is different because it's a query with a different
semantic meaning in SQL.
The first query means something like:
Return currency data for any currency with curr_cd='USD' which has a
price list.
The second query means something like:
Return a copy of the currency data for any currency with curr_cd='USD'
which has a price list once for each row in country (which implicitly
means no times if there are no rows in country).
If you write the second when you are trying to get the first meaning,
that's an error in whatever is generating the query.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
|