MySQL Certification Guide Practice Qn - Views' Column Names

Question

the question is

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 by "Rename the columns when you select from the view"?

Answer 1

By "rename when you select" they surely 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 the columns" is that there need not be an explicit, unambigous a in the view for you to "rename"... UNLESS you've already disambiguated by methods (a) or (b) [[in which case you may also "rename" this way, but that's pretty much a secondary issue!-)]].

Answer 2

I think the question in the certification guide is worded poorly. You can give explicit names to columns when you select 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 with giving aliases to columns explicitly. Here's the problem: if you rely on this instead of defining the view with distinct column names, and the view consists of a join such that the column names are ambiguous, you run into trouble:

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 distinct. For instance, you would get ambiguous results when you query the view:

SELECT a FROM MyView;

Does this select the first a column or the second a column?

So you must have a distinct set of column names in the view definition, it's not enough to make them distinct as you query the view.

This is the reason I think the certification guide question was poorly worded. It's not about renaming columns explicitly, it's about ensuring that the columns of the view have distinct names. This is a common reason for renaming columns, so that's probably why the person writing the question wrote it that way.

---

Either of the other techniques 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 get the aliased columns:

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