Re: Weird behaviour on a join with multiple keys

What happens if you do an outer join instead of an inner join?

Charlie Clark wrote:
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>