Hi,
I'm getting unexpected results on a query which involves joining two
tables on two common variables (firstname and lastname).
This is the basic query:
SELECT table1.lastname, table1.firstname
FROM table1
INNER JOIN table2 ON
(table2.name = table1.name
AND
table2.vorname = table1.vorname)
This is returning many rows fewer than I expect and is ignoring a lot
where table1.firstname = table2.firstname AND table1.lastname =
table2.lastname. In fact when I extend the query by a WHERE clause
such as WHERE table1.lastname IS LIKE 'Sch%' I get results which are
not returned by the original query.
I'm not very au fait with the inner workings of PostgreSQL but
EXPLAIN does not seem, to me at least, to provide an explanation for
the missing results.
"Merge Join (cost=1987.97..2121.24 rows=34 width=22)"
" Merge Cond: (("outer"."?column3?" = "inner"."?column3?") AND
("outer"."?column4?" = "inner"."?column4?"))"
" -> Sort (cost=364.97..375.99 rows=4409 width=22)"
" Sort Key: (table1.lastname)::text, (table1.firstname)::text"
" -> Seq Scan on table1 (cost=0.00..98.09 rows=4409 width=22)"
" -> Sort (cost=1623.00..1667.00 rows=17599 width=21)"
" Sort Key: (table2.lastname)::text, (table2.firstname)::text"
" -> Seq Scan on table2 (cost=0.00..381.99 rows=17599 width=21)"
Am I missing something big and obvious here?
Charlie
--
Charlie Clark
Helmholtzstr. 20
Düsseldorf
D- 40215
Tel: +49-211-938-5360
GSM: +49-178-782-6226
