CODEWITHSUNDEEP
sqlmysqldatabaseselect
srikanta
srikanta

Reputation: 2999

Mysql select statement with wildcard

I have a mysql table EMPLOYEE

+-------------------+--------------+------+-----+---------+-------+
| Field             | Type         | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+-------+
| id                | int(11)      | YES  |     | NULL    |       |
| name              | varchar(30)  | YES  |     | NULL    |       |
| age               | int(11)      | YES  |     | NULL    |       |
| start_salary      | int(11)      | YES  |     | NULL    |       |
| current_salary    | int(11)      | YES  |     | NULL    |       |
| address           | varchar(255) | YES  |     | NULL    |       |
| designation       | varchar(10)  | YES  |     | NULL    |       |
+-======------------+--------------+------+-----+---------+-------+

Now, I would want to do a select statement like

SELECT (current_salary/start_salary) appraisal, * from EMPLOYEE;

But the above statement is not valid in mysql. This is not homework but a dummy eg. Is it possible to have an extra column along with all existing columns of a table using * as wildcard operator?

What is the best way to do something like this?

Upvotes: 2

Views: 980

Answers (2)

MestreLion
MestreLion

Reputation: 13676

Richard's answer is correct, but you CAN use * as last column too!

To specify where the * columns will appear (instead of first), all you need is give the table an alias, and then use alias.*, like this:

SELECT (E.current_salary/E.start_salary) appraisal, E.* from EMPLOYEE E;

In this case, i used E as alias, but it could be anything (emp, EMP).

The E. in current_salary and start_salary is optional, but highly recommended once you start using alias in your queries. In queries using multible tables (INNER/LEFT/RIGHT JOINs), the alias is required if 2 or more tables have the same column name (otherwise, how would MySql know which table you want name or ID come from?)

Note that you can only use alias.* for tables whose column names are unique within the query (ie, no other table from the same query has columns with same names)

And * (without any alias, meaning "all columns from all tables") is only allowed if all column names from all tables are unique.

Upvotes: 4

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Yes absolutely. Trick is simple, just put the wildcard FIRST.
That's it!

SELECT *, (current_salary/start_salary) appraisal from EMPLOYEE;

Upvotes: 4

Related Questions