HAVING CLAUSE

 

This SQL tutorial explains how to use the SQL HAVING clause with syntax and examples.

DESCRIPTION

The SQL HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.

SYNTAX

The syntax for the SQL HAVING Clause is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause near the end of the SQL statement.

aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

aggregate_expression

This is the column or expression that the aggregate_function will be used on.

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 the conditions for the records to be selected.

HAVING condition

This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

EXAMPLE - USING SUM FUNCTION

Let's look at a SQL HAVING clause example that uses the SQL SUM function.

You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department). The SQL HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;

EXAMPLE - USING COUNT FUNCTION

Let's look at how we could use the HAVING clause with the SQL COUNT function.

You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The SQL HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;

EXAMPLE - USING MIN FUNCTION

Let's next look at how we could use the HAVING clause with the SQL MIN function.

You could also use the SQL MIN function to return the name of each department and the minimum salary in the department. The SQL HAVING clause will return only those departments where the minimum salary is greater than $35,000.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) > 35000;

EXAMPLE - USING MAX FUNCTION

Finally, let's look at how we could use the HAVING clause with the SQL MAX function.

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department. The SQL HAVING clause will return only those departments whose maximum salary is less than $50,000.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;

GROUP BY CLAUSE

This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.

DESCRIPTION

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

SYNTAX

The syntax for the SQL GROUP BY clause is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause at the end of the SQL statement.

aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

aggregate_expression

This is the column or expression that the aggregate_function will be used on.

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.

EXAMPLE - USING SUM FUNCTION

Let's look at a SQL GROUP BY query example that uses the SQL SUM function.

This GROUP BY example uses the SUM function to return the name of the department and the total sales (for the department).

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column (the department field) in your SQL SELECT statement that is not encapsulated in the SUM function, you must use the GROUP BY Clause. The department field must, therefore, be listed in the GROUP BY clause.

EXAMPLE - USING COUNT FUNCTION

Let's look at how we could use the GROUP BY clause with the SQL COUNT function.

This GROUP BY example uses the COUNT function to return the department and the number of employees (in the department) that make over $25,000 / year.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

EXAMPLE - USING MIN FUNCTION

Let's next look at how we could use the GROUP BY clause with the SQL MIN function.

This GROUP BY example uses the MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

EXAMPLE - USING MAX FUNCTION

Finally, let's look at how we could use the GROUP BY clause with the SQL MAX function.

This GROUP BY example uses the MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

ORDER BY CLAUSE

This SQL tutorial explains how to use the SQL ORDER BY clause with syntax and examples. Click the "Try It" button next to an example to test the ORDER BY clause for yourself in our SQL Editor.

DESCRIPTION

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

SYNTAX

The syntax for the SQL ORDER BY clause is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

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. Conditions that must be met for the records to be selected.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is default behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

NOTE

• If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order. This is equivalent to ORDER BY expression ASC.

DDL/DML FOR EXAMPLES

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

EXAMPLE - SORTING WITHOUT USING ASC/DESC ATTRIBUTE

The SQL ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the SQL ORDER BY clause, the sort order is defaulted to ASC or ascending order. 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 SQL statement:

Try It

SELECT *
FROM customers
ORDER BY last_name;

These are the results that you should see:

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

This example would return all records from the customers sorted by the last_name field in ascending order and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT *
FROM customers
ORDER BY last_name ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

EXAMPLE - SORTING IN DESCENDING ORDER

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause. Let's take a closer look.

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

supplier_id	supplier_name	city
100	Microsoft	Redmond
200	RIM	Waterloo
300	Oracle	Redwood City
400	Google	Mountain View
500	Intel	Santa Clara
600	Samsung	Seoul

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_id > 200
ORDER BY supplier_id DESC;

These are the results that you should see:

supplier_id	supplier_name	city
600	Samsung	Seoul
500	Intel	Santa Clara
400	Google	Mountain View
300	Oracle	Redwood City

This example would sort the result set by the supplier_id field in descending order.

EXAMPLE - SORTING BY RELATIVE POSITION

You can also use the SQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1, the second field is 2, the third field is 3, and so on.

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

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY 1 DESC;

These are the results that you should see:

product_id	product_name
7	Kleenex
6	Sliced Ham
4	Apple
3	Orange
2	Banana
1	Pear

This example would sort the results by the product_id field in descending order, since the product_id field is in position #1 in the result set and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY product_id DESC;

EXAMPLE - USING BOTH ASC AND DESC ATTRIBUTES

When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

In this example, let's use the same products table as the previous example:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_id <> 7
ORDER BY category_id DESC, product_name ASC;

These are the results that you should see:

product_id	product_name	category_id
5	Bread	75
4	Apple	50
2	Banana	50
3	Orange	50
1	Pear	50
6	Sliced Ham	25

This example would return the records sorted by the category_id field in descending order, with a secondary sort byproduct_name in ascending orde

WHERE CLAUSE

This SQL tutorial explains how to use the SQL WHERE clause with syntax and examples. Click the "Try It" button next to an example to test the WHERE clause for yourself in our SQL Editor.

DESCRIPTION

The SQL WHERE clause is used to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the SQL WHERE Clause is:

WHERE conditions;

Parameters or Arguments

conditions

The conditions that must be met for records to be selected.

DDL/DML FOR EXAMPLES

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

EXAMPLE - WITH SINGLE CONDITION

It is difficult to explain the syntax for the SQL WHERE clause, so let's start with an example that uses the WHERE clause to apply 1 condition.

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

supplier_id	supplier_name	city
100	Microsoft	Redmond
200	RIM	Waterloo
300	Oracle	Redwood City
400	Google	Mountain View
500	Intel	Santa Clara
600	Samsung	Seoul

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_name = 'Intel';

These are the results that you should see:

supplier_id	supplier_name	city
500	Intel	Santa Clara

In this example, we've used the SQL WHERE clause to filter our results from the suppliers table. The SQL statement above would return all rows from the suppliers table where the supplier_name is Intel. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

EXAMPLE - USING AND CONDITION

You can use the AND condition in the WHERE clause to specify more than 1 condition that must be met for the record to be selected. Let's explore how to do this.

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

Now enter the following SQL statement:

Try It

SELECT *
FROM customers
WHERE favorite_website = 'techonthenet.com'
AND customer_id > 6000;

These are the results that you should see:

customer_id	last_name	first_name	favorite_website
9000	Johnson	Derek	techonthenet.com

This example uses the WHERE clause to define multiple conditions. In this case, this SQL statement uses the AND condition to return all customers whose favorite_website is techonthenet.com and whose customer_id is greater than 6000.

EXAMPLE - USING OR CONDITION

You can use the OR condition in the WHERE clause to test multiple conditions where the record is returned if any one of the conditions are met.

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

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_name = 'Pear'
OR product_name = 'Apple';

These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
4	Apple	50

This example uses the WHERE clause to define multiple conditions, but instead of using the AND condition, it uses the OR condition. In this case, this SQL statement would return all records from the products table where the product_name is either Pear or Apple.

EXAMPLE - COMBINING AND & OR CONDITIONS

You can also combine the AND condition with the OR condition to test more complex conditions.

Let's use the products table again for this example.

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE (product_id > 3 AND category_id = 75)
OR (product_name = 'Pear');

These are the results that you should see:

product_id	product_name	category_id
1	Pear	50
5	Bread	75

This example would return all products whose product_id is greater than 3 and category_id is 75 as well as all products whoseproduct_name is Pear.

The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

FROM CLAUSE

This SQL tutorial explains how to use the SQL FROM clause with syntax and examples. Click the "Try It" button next to an example to test the FROM clause for yourself in our SQL Editor.

DESCRIPTION

The SQL FROM clause is used to list the tables and any joins required for the SQL statement.

SYNTAX

The syntax for the FROM Clause in SQL is:

FROM table1
[ { INNER JOIN
  | LEFT [OUTER] JOIN
  | RIGHT [OUTER] JOIN
  | FULL [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2

These are the tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

NOTE

• When using the FROM clause in a SQL statement, there must be at least one table listed in the FROM clause.
• If there are two or more tables listed in the SQL FROM clause, these tables are generally joined using INNER or OUTER joins.

DDL/DML FOR EXAMPLES

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

EXAMPLE - ONE TABLE LISTED

We'll start by looking at how to use the FROM clause that lists only a single table in the SQL statement.

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

supplier_id	supplier_name	city
100	Microsoft	Redmond
200	RIM	Waterloo
300	Oracle	Redwood City
400	Google	Mountain View
500	Intel	Santa Clara
600	Samsung	Seoul

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_id < 400
ORDER BY city DESC;

These are the results that you should see:

supplier_id	supplier_name	city
200	RIM	Waterloo
300	Oracle	Redwood City
100	Microsoft	Redmond

In this example, we've used the FROM clause to list the table called suppliers. There are no joins performed in this query since we have only listed one table.

EXAMPLE - TWO TABLES WITH INNER JOIN

Let's look at how to use the FROM clause to INNER JOIN two tables together.

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

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

And a table called categories with the following data:

category_id	category_name
25	Deli
50	Produce
75	Bakery
100	General Merchandise

Enter the following SQL statement:

Try It

SELECT products.product_name, categories.category_name
FROM products 
INNER JOIN categories
ON products.category_id = categories.category_id
WHERE product_name <> 'Pear';

These are the results that you should see:

product_name	category_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli

This example uses the FROM clause to join two tables - products and categories. In this case, we are using the FROM clause to specify an INNER JOIN between the products and categories tables based on the category_id column in both tables.

EXAMPLE - TWO TABLES WITH OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

Let's use the same products and categories tables from the INNER JOIN example above, but this time we will join the tables using a LEFT OUTER JOIN. Enter the following SQL statement:

Try It

SELECT products.product_name, categories.category_name
FROM products 
LEFT OUTER JOIN categories
ON products.category_id = categories.category_id
WHERE product_name <> 'Pear';

These are the results that you should see:

product_name	category_name
Banana	Produce
Orange	Produce
Apple	Produce
Bread	Bakery
Sliced Ham	Deli
Kleenex	NULL

This example uses the FROM clause to LEFT OUTER JOIN the products and categories tables based on the category_id in both tables.

Now, the last record with the product_name of 'Kleenex' will appear in our result set with a NULL value for the category_name. This record did not appear in our results when we performed an INNER JOIN.