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?

asked 16 May '14, 08:02

Martin's gravatar image

Martin
8.6k118151237
accept rate: 14%

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).

(16 May '14, 08:53) Volker Barth
Replies hidden

In the example above Col1,Col2 is the foreign key

(16 May '14, 10:08) Martin

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.

(16 May '14, 10:16) Volker Barth

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.

permanent link

answered 16 May '14, 10:26

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

edited 16 May '14, 10:30

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×246
×51

question asked: 16 May '14, 08:02

question was seen: 550 times

last updated: 16 May '14, 10:37