Which table to choose from in Select?

[MCMartin]

When 2 tables are joined, with a relation 1:100 like:

Table A: Col1, Col2, Col3
Table B: Col1, Col2, Col4, Col5

Executing a statement like:

Select Col1,Col2,Col4,Col5 from A key join B where Col3=x

When thinking of performance which one is better, selecting Col1 and Col2 from table A or from table B or is it equal?

[VolkerBarth]

Don't know - but thinking in terms of normalization a choice like that should usually not appear, i.e. why do two tables contain identical column data? 🙂

You don't tell about the FK relationship - I would think the choice should matter if the selected columns of only one of the tables could be accessed via indexes (an index-only retrieval).

[MCMartin]

In the example above Col1,Col2 is the foreign key

[VolkerBarth]

OK, then I have to withdraw my hint at missing normalization, apparently:)

If both columns from each table are required to build the join condition, then I would guess it should not matter which ones are used within the select list.

FWIW, personally, I would always use those from the parent table, as that seems "natural" IMHO...

---

Aside: For declared FKs like here, I guess the performance should not matter as these relationships are (at least usually) maintained by logical indexes that share the same physical index.

[Former Member]

I always prefer to choose cols from central table (in your case it's table A). But it's not because of performance, but because of factor that any sql in future can be modified. In the case of key join may be there are no difference, but if in future you must switch from key join for example to outer joins, so it's be much easy to made modifications to sql query. And I am always prefer to use table aliases.