Thread: 'natural join' and 'join ... using' giving different results
I am seeing different results when using 'natural join' as opposed to
'join ... using' on what I think the equivalent columns should be.
The 'join ... using' version of the query gives the expected answer.
I have tried this on 7.1.3 (built locally) and 7.2b4 with a patch
to how foreign keys are checked. The machines where both running
Redhat linux 6.1 with a 2.2.16 kernel.
Below is a sample psql session that I think illustrates the problem.
Script started on Wed Jan 9 11:17:06 2002
[bruno@wolff bruno]$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
area=> \d games
Table "games"
Attribute | Type | Modifier
-----------+--------------------------+------------------------
gameid | text | not null
title | text | not null
touched | timestamp with time zone | not null default 'now'
Indices: games_pkey,
title_idx
Constraints: (gameid ~ '^[A-Z0-9]+$'::text)
(title ~ '^[!-~]+( [!-~]+)*$'::text)
area=> \d crate
Table "crate"
Attribute | Type | Modifier
-----------+--------------------------+------------------------
areaid | text | not null
gameid | text | not null
rate | integer | not null default 5000
frq | integer | not null default 0
opp | integer | not null default 0
rmp | integer | not null default 0
trn | integer | not null default 0
touched | timestamp with time zone | not null default 'now'
Index: crate_pkey
Constraints: (rate >= 0)
(frq >= 0)
(opp >= 0)
(rmp >= 0)
(trn >= 0)
area=> select * from crate natural join games where areaid = '53217.01';
gameid | touched | areaid | rate | frq | opp | rmp | trn | title
--------+------------------------+----------+------+-----+-----+-----+-----+-----------
WRS | 2000-06-01 00:00:00-05 | 53217.01 | 5000 | 0 | 0 | 0 | 0 | Wrasslin'
(1 row)
area=> select * from crate join games using (gameid) where areaid = '53217.01';
gameid | areaid | rate | frq | opp | rmp | trn | touched | title | touched
--------+----------+------+-----+-----+-----+-----+------------------------+-------------------------+------------------------
GR | 53217.01 | 4969 | 2 | 49 | 0 | 2 | 1999-08-02 00:00:00-05 | Greed | 2001-11-17
00:00:00-06
GXY | 53217.01 | 4975 | 1 | 4 | 0 | 1 | 2001-01-13 00:00:00-06 | Galaxy | 2001-09-09
00:00:00-05
MRA | 53217.01 | 4966 | 1 | 3 | 0 | 1 | 1999-08-02 00:00:00-05 | Monsters Ravage America | 2000-06-01
00:00:00-05
RBN | 53217.01 | 5143 | 4 | 15 | 0 | 2 | 1993-08-02 00:00:00-05 | Rail Baron | 2001-08-10
00:00:00-05
SLS | 53217.01 | 4986 | 1 | 7 | 0 | 1 | 1999-08-02 00:00:00-05 | Slapshot | 2000-06-01
00:00:00-05
TTA | 53217.01 | 5103 | 1 | 4 | 0 | 1 | 1999-08-02 00:00:00-05 | Titan: The Arena | 2001-09-09
00:00:00-05
TTN | 53217.01 | 5554 | 28 | 38 | 0 | 11 | 2001-07-01 00:00:00-05 | Titan | 2001-09-21
00:00:00-05
VIP | 53217.01 | 5300 | 15 | 15 | 0 | 3 | 1999-03-07 00:00:00-06 | Victory In The Pacific | 2002-01-03
00:00:00-06
WRS | 53217.01 | 5000 | 0 | 0 | 0 | 0 | 2000-06-01 00:00:00-05 | Wrasslin' | 2000-06-01
00:00:00-05
(9 rows)
area=> \q
[bruno@wolff bruno]$ exit
exit
Script done on Wed Jan 9 11:18:48 2002
Natural join of those two tables will be on (gameid, touched)
not only (gameid). You should've noticed that the natural
join was only emitting one copy of the "touched" column ...
regards, tom lane
On Wed, 9 Jan 2002, Bruno Wolff III wrote: > I am seeing different results when using 'natural join' as opposed to > 'join ... using' on what I think the equivalent columns should be. > The 'join ... using' version of the query gives the expected answer. > I have tried this on 7.1.3 (built locally) and 7.2b4 with a patch > to how foreign keys are checked. The machines where both running > Redhat linux 6.1 with a 2.2.16 kernel. > > Below is a sample psql session that I think illustrates the problem. Wouldn't games natural join crate be on both gameid and touched as opposed to only gameid? I don't remember exactly but natural join joins on columns of the same name right? > > Script started on Wed Jan 9 11:17:06 2002 > [bruno@wolff bruno]$ psql > Welcome to psql, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > area=> \d games > Table "games" > Attribute | Type | Modifier > -----------+--------------------------+------------------------ > gameid | text | not null > title | text | not null > touched | timestamp with time zone | not null default 'now' > Indices: games_pkey, > title_idx > Constraints: (gameid ~ '^[A-Z0-9]+$'::text) > (title ~ '^[!-~]+( [!-~]+)*$'::text) > > area=> \d crate > Table "crate" > Attribute | Type | Modifier > -----------+--------------------------+------------------------ > areaid | text | not null > gameid | text | not null > rate | integer | not null default 5000 > frq | integer | not null default 0 > opp | integer | not null default 0 > rmp | integer | not null default 0 > trn | integer | not null default 0 > touched | timestamp with time zone | not null default 'now'