SQL Query Order of Execution

SQL Query Order of Execution:

In SQL, the order of written code differs from the order of execution. So in order to understand how the SQL query works you’ll need to understand proper SQL query structure and the order in which SQL queries must be written using following keywords

Lets see them with examples

1. SELECT * FROM table_name;
2. SELECT * FROM table_name WHERE col_name=’input’;
3. SELECT col_name1,count(*) FROM table_name WHERE col_name=’input’ GROUP BY col_name1 ;
4. SELECT col_name1,count(*) FROM table_name WHERE col_name=’input’ GROUP BY col_name1 HAVING col_name2>’input2' ;
5. SELECT col_name1,count(*) FROM table_name WHERE col_name=’input’ GROUP BY col_name1 HAVING col_name2>’input2' ORDER BY count DESC;
6. SELECT col_name1,count(*) FROM table_name WHERE col_name=’input’ GROUP BY col_name1 HAVING col_name2>’input2' ORDER BY count DESC LIMIT 100;

GROUP BY is used aggregation functions (max(),min(),avg(),sum(),count())

In general, SQL queries are executed in the following order:

1. FROM: The first step is to identify the tables or views involved in the query. This is specified in the FROM clause of the query.

2. JOIN: If the query involves multiple tables and requires a join operation, the join conditions are evaluated to combine the data from different tables.

3. WHERE: The WHERE clause is applied to filter the rows based on specified conditions. Rows that do not meet the conditions are eliminated from further processing.

4. GROUP BY: If the query includes a GROUP BY clause, the rows are grouped based on the specified columns.

5. HAVING: If a HAVING clause is present, it is applied to filter the groups generated by the GROUP BY clause. Groups that do not meet the conditions are eliminated.

6. SELECT: The SELECT clause is applied to determine the columns to be included in the result set. Any calculations or functions specified in the SELECT clause are also evaluated at this stage.

7. DISTINCT: If the query includes the DISTINCT keyword, duplicate rows are removed from the result set.

8. ORDER BY: If an ORDER BY clause is present, the result set is sorted based on the specified columns.

9. LIMIT/OFFSET: If the query includes a LIMIT and/or OFFSET clause, the result set is limited to a specific number of rows and/or skipped by a certain number of rows.

It’s important to note that not all queries will include every step mentioned above. The order of execution may vary depending on the specific query and the presence of different clauses.

Know more about it :

1.Want to quickly find the most frequent values for a column? Use a GROUP BY, COUNT(*) and ORDER BY DESC

2.Want to find out how many duplicates there are in a table? Compare COUNT(DISTINCT *) with COUNT(*)

3.Want to get a feel for how many null values there are? Compare COUNT(<column-name>) with a COUNT(*) or simply run a COUNT(*) with a WHERE filter to remove the null values