Hello.
While reading the official documentation I came across a query which I
couldn't understand in full. It seems that queries I wrote before were
too simple to help me understand the new one =) That's why I'm here.
Suppose we have two tables fdt and t2 both filled with the data as follows:
CREATE TABLE fdt (c1 int, name text);
CREATE TABLE t2 (c1 int, c2 int);
INSERT INTO fdt VALUES
(1, 'hello'),
(2, 'world'),
(3, 'linux');
INSERT INTO t2 VALUES
(1, 11),
(3, 11);
The query I have a problem with is:
SELECT * FROM fdt WHERE c1 IN (SELECT c1 FROM t2 WHERE c2 = fdt.c1 + 10);
I've never seen column names being used inside subquries, so let me
explain how I supposed it to work. It should help you to see where I'm
making a mistake.
After PG gets a row from fdt table (SELECT * FROM fdt), it then checks
if column c1 of the row is in some list of values (WHERE c1 IN). If
the list contains the value c1 column of the current row has, the
whole row will be included in the resulting table. The "list" is the
result of the subquery enclosed in parentheses. So if fdt.c1 = 1 we
will have:
-- Here fdt.c1 in subquery has been substituted by '1'
SELECT * FROM fdt WHERE c1 IN (SELECT c1 FROM t2 WHERE c2 = 1 + 10);
c1 | name
----+-------
1 | hello
3 | linux
That is what I supposed the result to be. But when I executed original
query, I saw different table:
SELECT * FROM fdt WHERE c1 IN (SELECT c1 FROM t2 WHERE c2 = fdt.c1 + 10);
c1 | name
----+-------
1 | hello
What happened here? Why hasn't PG included "3 | linux" row in the result?
Could you explain this behaviour in more detail?
P.S. The original version of the query can be found here:
http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html#QUERIES-WHERE
I just added sample table definitions to make testing possible.
P.P.S If the above behaviour is best explained by some article in the
official documentation, please point out it. I haven't found the
explanation yet.
Thanks for the reading.
--
Sergey Samokhin