CODEWITHSUNDEEP
sqlselectoracle11g
Mangesh Kherdekar
Mangesh Kherdekar

Reputation: 313

How SQL select statements work

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?

Upvotes: 2

Views: 150

Answers (3)

Webeng
Webeng

Reputation: 7143

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:

52
52
52
52
52

Upvotes: 3

cableload
cableload

Reputation: 4385

If you dont want to reference any existing table and you still want to calculate an expression (note a single expression) you can use the DUAL table in oracle. 

select 50+2 from dual;

select 1*2*3 from dual;

From the oracle doc,

The table named DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. This table has one column called DUMMY and one row containing the value X.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

If you want to give expression then just use SELECT .. Dual 

select 50+2 from dual

This will also result 52

Why select 50+2 from employees because it is a just a constant value which will be displayed in all the rows of your table, Example the below query will also result constant value 'A' for all the rows 

Select 'A',col1,col2 from yourtable

Upvotes: 1

Related Questions