Clever Geek Handbook

MySQL Qn Certification Certification Guide - Views' Column Names

question:

Which of the following methods for providing explicit names for the columns in a view work?

a. Include a column list
b. Provide column aliases in the view SELECT statement
c. Rename the columns when you select from the view

answer

a. Works: Include a column list
b. Works: Provide column aliases in the view SELECT statement
c. Does not work: Rename the columns when you select from the view

regarding (c) what do they mean under "Rename Columns When Selecting From View"?

+2


source to share


2 answers


I think the question in the certification guide is poorly formulated. You can give explicit names to columns when selected from a view, and this works:

CREATE VIEW MyView AS SELECT a, b, c FROM MyTable;
SELECT a AS d, b AS e, c AS f FROM MyView;

The problem is not explicitly giving column aliases. Here's the problem: if you are relying on this instead of defining a view with different column names, and the view consists of a concatenation such that the column names are ambiguous, you are running into a problem:

CREATE VIEW MyView AS 
  SELECT m.a, m.b, m.c, o.a, o.b, o.c 
  FROM MyTable m JOIN OtherTable o;

This is not a valid view because in the view definition all column names must be different. For example, you get ambiguous results when you request a view:

SELECT a FROM MyView;

Does it select the first column a

or the second column a

?

Thus, you must have a separate set of column names in the view definition, this is not enough to make them different when you query the view.



That's why I think the certification guide question was poorly formulated. This is not about renaming the columns explicitly, but about the view columns having different names. This is a common reason for renaming columns, which is probably why the person who wrote the question wrote it like this.

Any of the other methods mentioned in the question can resolve the ambiguity:

CREATE VIEW MyView (a, b, c, d, e, f) AS 
  SELECT m.a, m.b, m.c, o.a, o.b, o.c 
  FROM MyTable m JOIN OtherTable o;

or

CREATE VIEW MyView AS 
  SELECT m.a, m.b, m.c, o.a AS d, o.b AS e, o.c AS f 
  FROM MyTable m JOIN OtherTable o;

Either way, you end up with aliased columns:

SELECT * FROM MyView; -- returns result with columns a, b, c, d, e, f
+1


source


By "rename on select" they certainly mean something like SELECT a AS b FROM theview

, etc. The reason it doesn't work for the given task of "providing explicit names for columns" is because there shouldn't be an explicit, unambiguous a

in the view so you can "rename" ... IF you are already ambiguous with the methods (a ) or (b) [[in which case you can also "rename" this path, but that's pretty much a secondary issue -.)]]



0


source






More articles:


All Articles