Re: [SQL] To query many tables.

[Andre Schnabel]

- Original Message -
From: "trebischt" <[EMAIL PROTECTED]>
Subject: [SQL] To query many tables.


> Hi,
>
> The query looks like this:
>
> select * from table1 f, table2 s, table3 t, table4 fo, table5 fi,
> table6 si, table 7 se, table8 e, table9 n, table10 ten, table 11 el,
> table 12 tw ...
> where f.id=s.id
> and f.id=t.id
> and f.id=fo.id
> and f.id=fi.id
> and so on...
>
> Is this the right way, or are there any better solution?
> How do the professionals make that big queries?

Yes, this is ONE right way. Professionals do it the same way ;-).
But you can use the "JOIN"-Clause instead. It's your desicion, what's more
readable.
With JOIN it would look like this:

select * FROM
table1 fJOINtable2 s ON f.id = s.id
JOINtable 3 t ON f.id = t.id
 more joins here 
WHERE xxx

If you use your way or this simple joins, there must be a row with the same
id in each table, to have it shown by your query. If you want to see all
records, that have an entry in at least one table you have to use outer
joins. More details on SELECT statemants and joins are here:
http://www.postgresql.org/idocs/index.php?queries.html.

Andre


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] optimizing queries and indexes...

[Tom Lane]

[ Sorry for slow response, I've been out of town ]

"Robert J. Sanford, Jr." <[EMAIL PROTECTED]> writes:
> [ some questions already ably answered by Josh and Stephan ]

I just wanted to throw in one more tidbit:

> for example, one thing that one of my friends said is:
>select X
>from big_table
>,little_table
>Generally speaking, Oracle optimizes better
>when the smaller/reducing tables are on the
>bottom and the larger tables are on the top.
>I believe SQLServer likes them in the opposite
>direction.

Postgres absolutely does not care: the optimizer will always consider
both A-join-B and B-join-A orders for every join it has to do.  As
Stephan and Josh noted, you can constrain the join pairs the optimizer
will consider if you use explicit-JOIN syntax --- but each pair will be
considered in both directions.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])