how does the select statement works or retrieve the data from database

Question

here is the query

select emp1.EmployeeId as empss, EmployeeName, DepartmentId, IsActive, CreatedDate, Address, salary 
FROM employee as emp1 
where 4=(select count(emp.EmployeeId) as con from employee as emp where emp.EmployeeId < emp1.EmployeeId);

here is the table name employee where i am getting the number 5 row without limit,for writing this query i took some help over net but my question is how does the select statments work ??

1) does it select the column first then filter the where clause ?
2) does it filter the where clause then select column ??

please explain thanks so much in advance

Answer 1

In MySQL Where conditions (filtration) executes before select columns. Following is the order of execution for various clauses present in a SELECT statement

1. FROM (including joins)
2. WHERE
3. SELECT (select columns)
4. GROUP BY
5. ORDER BY
6. LIMIT

Can validated by following SQL

SELECT 1 as cnt FROM employee WHERE cnt = 1;

This statement will throw error as the cnt is defined in the SELECT clause which executes after the WHERE but following SQL

SELECT 1 as cnt FROM employee GROUP BY cnt;

will work.