I am on an oracle DB. Lets say I have one view that joins to three tables. The view has two fields each. Each field only needs data from two of the three tables.
If I query the view and return only one field, does the view still join to three tables or just to the two tables that it needs to calculate the field?
That likely depends on the type of join being used. If they are all inner joins, it will definitely need to examine all three tables.
In general, the database engine would join all three tables to ensure it got the right answer.
Oracle will sometimes eleminate one of the tables where this does not change the result.
This can be done if:-
There is a foreign key constraint to the table to be eleminated (i.e. a row in the table can be guaranteed to be found)
The table is otherwise unused. i.e. not selected from, in the where clause, etc.
Generally it will have to hit the three tables. Consider
SELECT A.VAL, B.VAL, C.VAL FROM A JOIN B ON A.ID = B.ID JOIN C ON A.ID = C.ID
It is possible that a single ID in "A" to have zero, 1 or multiple matches in either B or C. If table "C" were empty, the view would never return a row, so even just querying A.VAL or B.VAL, it would still need to see if there was a corresponding row in "C".
The exception is when, because of an enforced referential integrity constraint, the optimizer knows that a row in 'B' will always have a parent row in 'A'. In that case, a select of B.VAL would not need to actually check the existence of the parent row in 'A'. This is demonstrated by this article