What total appears in row 6 of your query result?

SQL Lesson 12: Order of execution of a Query

Índice Show

1. FROM and JOINs
3. GROUP BY
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET
Parameters or Arguments

Now that we have an idea of all the parts of a query, we can now talk about how they all fit together in the context of a complete query.

Complete SELECT query

SELECT DISTINCT column, AGG_FUNC(column_or_expression), … FROM mytable JOIN another_table ON mytable.column = another_table.column WHERE constraint_expression GROUP BY column HAVING constraint_expression ORDER BY column ASC/DESC LIMIT count OFFSET COUNT;

Each query begins with finding the data that we need in a database, and then filtering that data down into something that can be processed and understood as quickly as possible. Because each part of the query is executed sequentially, it's important to understand the order of execution so that you know what results are accessible where.

1. FROM and JOINs

The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause, and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.

2. WHERE

Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.

3. GROUP BY

The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.

4. HAVING

If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.

5. SELECT

Any expressions in the SELECT part of the query are finally computed.

6. DISTINCT

Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.

7. ORDER BY

If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.

8. LIMIT / OFFSET

Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.

Conclusion

Not every query needs to have all the parts we listed above, but a part of why SQL is so flexible is that it allows developers and data analysts to quickly manipulate data without having to write additional code, all just by using the above clauses.

Here ends our lessons on SELECT queries, congrats of making it this far! This exercise will try and test your understanding of queries, so don't be discouraged if you find them challenging. Just try your best.

This SQL tutorial explains how to use the SQL COUNT function with syntax, examples, and practice exercises.

The SQL COUNT function is used to count the number of rows returned in a SELECT statement.

The syntax for the COUNT function in SQL is:

SELECT COUNT(aggregate_expression) FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]];

OR the syntax for the COUNT function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n, COUNT(aggregate_expression) FROM tables [WHERE conditions] GROUP BY expression1, expression2, ... expression_n [ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

expression1, expression2, ... expression_n Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression whose non-null values will be counted. tables The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. These are conditions that must be met for the records to be selected. ORDER BY expression Optional. The expression used to sort the records in the result set. If more than one expression is provided, the values should be comma separated. ASC Optional. ASC sorts the result set in ascending order by expression. This is the default behavior, if no modifier is provider. DESC Optional. DESC sorts the result set in descending order by expression.

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Not everyone realizes this, but the COUNT function will only count the records where the expression is NOT NULL in COUNT(expression). When the expression is a NULL value, it is not included in the COUNT calculations. Let's explore this further.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Enter the following SELECT statement that uses the COUNT function:

Try It SELECT COUNT(customer_id) FROM customers;

There will be 1 record selected. These are the results that you should see:

In this example, the query will return 6 since there are 6 records in the customers table and all customer_id values are NOT NULL (ie: customer_id is the primary key for the table).

But what happens when we encounter a NULL value with the COUNT function? Let's enter this next SELECT statement that counts the favorite_website column which can contain NULL values:

Try It SELECT COUNT(favorite_website) FROM customers;

There will be 1 record selected. These are the results that you should see:

COUNT(favorite_website)
5

This second example will return 5. Because one of the favorite_website values is NULL, it would be excluded from the COUNT function calculation. As a result, the query will return 5 instead of 6.

TIP: Use the primary key in the COUNT function or COUNT(*) if you want to be certain that records aren't excluded in the calculations.

Let's look at an example that shows how to use the COUNT function with a single expression in a query.

In this example, we have a table called employees with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

Try It SELECT COUNT(*) AS total FROM employees WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:

In this example, we will return the number of employees who have a salary above $50,000. We've aliased the COUNT(*) as total to make our query results more readable. Now, total will display as the column heading when the result set is returned.

In some cases, you will be required to use the GROUP BY clause with the COUNT function. This happens when you have columns listed in the SELECT statement that are not part of the COUNT function. Let's explore this further.

Again, using the employees table populated with the following data:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

Try It SELECT dept_id, COUNT(*) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

There will be 2 records selected. These are the results that you should see:

dept_id	total
500	2
501	1

In this example, the COUNT function will return the number of employees that make over $50,000 for each dept_id. Because the dept_id column is not included in the COUNT function, it must be listed in the GROUP BY clause.

Did you know that you can use the DISTINCT clause within the COUNT function? This allows you to count only the unique values.

Using the same employees table as the previous example:

employee_number	last_name	first_name	salary	dept_id
1001	Smith	John	62000	500
1002	Anderson	Jane	57500	500
1003	Everest	Brad	71000	501
1004	Horvath	Jack	42000	501

Enter the following SQL statement:

Try It SELECT COUNT(DISTINCT dept_id) AS total FROM employees WHERE salary > 50000;

There will be 1 record selected. These are the results that you should see:

In this example, the COUNT function will return the unique number of dept_id values that have at least one employee that makes over $50,000.

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the parentheses), you can use COUNT(1) to get better performance. Now the database engine will not have to fetch any data fields, instead it will just retrieve the integer value of 1.

For example, instead of entering this statement:

Try It SELECT dept_id, COUNT(*) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

You could replace COUNT(*) with COUNT(1) to get better performance:

Try It SELECT dept_id, COUNT(1) AS total FROM employees WHERE salary > 50000 GROUP BY dept_id;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

If you want to test your skills using the SQL COUNT function, try some of our practice exercises.

These exercises allow you to try out your skills with the COUNT function. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises