Mangesh Kherdekar Mangesh Kherdekar - 2 years ago 89
SQL Question

Expressions in select statement

I executed the following query and it worked. I want to understand how it works.

select 50+2 from employees

This works only when the 'employees' table exists. If I mention a non existent table, then it throws an error.

How can such expressions be evaluated for user-defined tables?

Answer Source

What MySQL does first is it uses its parser to read the SQL statement and separate it into it's logical parts. A parser (used not only by MySQL but very often in programming) is just a block of code that retrieves raw information, and turns it into something that the program can use.

In the case of MySQL, it will separate

SELECT 50+2 FROM employees

Into SELECT 50+2 and FROM employees

So lets analyse what each of these two do. The SELECT reserved word is used to identify what a user wishes to obtain from the operation. It usually contains a column name. However if you include a string called "hello", MySQL interprets that you want to select "hello". In this case you want to select a number that is the result of 50+2.

Now what FROM employees does is that it informs MySQL which table you wish to select this information from. It doesn't matter that 50+2 isn't a column or even anything located in the table. MySQL isn't an AI system, and won't ask you questions on why you decided to do that, it just executes commands that are designed following the rules established.

Now MySQL will look through the whole table, and return the selected columns of each row that is consistent with the condition that exists in the WHERE ... section of a query. If a query does not have the WHERE ... section, it is assumed you want to return all the rows.

The result of your query will then be a column full of the value 52 with the same amount of rows as your table has. If your table has 5 rows, the result will be:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download