In this post, we will learn about Different clauses in SQL. We will learn about below clauses in SQL –

1. WHERE Clause
2. ORDER BY Clause
3. GROUP BY Clause
4. HAVING Clause
5. UNION Clause
6. LIMIT Clause

1. WHERE Clause in SQL

If you want to fetch results based on some condition then we make use of WHERE clause. This is one of my most used clauses when you fetch results.

Example 1

If you want to fetch the employee whose salary is less than 50000.

mysql> select eid, fname, lname, salary from employee where salary < 50000

Result

4 rows in set (0.00 sec)

Example 2

mysql> select eid, fname, lname, salary from employee where eid < 101

Result

1 row in set (0.03 sec)

2. ORDER BY Clause in SQL

Query results can be sorted by a specific field (or fields ) using the ORDER BY clause in the SELECT statement. The sorting can be in ascending or descending order. Ascending is the default order.

Example 1

Sorting in ascending order by varchar data type.

mysql> SELECT fname, lname from employee ORDER BY fname;

Result

7 rows in set (0.00 sec)

Example 2

Sorting in ascending order by date data type.

mysql> SELECT fname, lname, dob from employee ORDER BY dob;

Result

7 rows in set (0.00 sec)

Example 3

Sorting in descending order;

mysql> SELECT fname, lname from employee ORDER BY fname desc;

Result

7 rows in set (0.00 sec)

3. GROUP BY clause

This clause is used with SELECT statement. It groups rows based on distinct values that exist for specified columns. It creates a dataset containing several sets of records grouped together based on a condition.

Example 1

mysql> SELECT sex from employee group by sex;

Result

2 rows in set (0.00 sec)

Example 2

Count total of male and female employees;

mysql> SELECT sex, count(*) from employee group by sex;

Result

2 rows in set (0.00 sec)

4. HAVING clause

This clause is used in conjunction with the GROUP BY clause. HAVING imposes a condition on the GROUP BY clause, which further filters the groups created by the GROUP BY clause. Each column specification specified in the HAVING clause must occur within a statistical function or must occur in the list of columns named in the GROUP BY clause.

Example 1

mysql> SELECT sex, count(*) from employee group by sex having sex= 'F'

Result

1 row in set (0.04 sec)

Example 2

Same above example but by using alias to the column name.

mysql> SELECT sex as gender, count(*) from employee group by sex having gender= 'F'

Result

1 row in set (0.04 sec)

5. UNION clause in SQL

In union clause we combine the results of two select queries. But when performing a union of both SQL queries we must ensure that the columns in both the queries are same. UNION clause will actually remove the duplicate rows.

Example 1

Let’s say we have two tables’ suppliers and orders.

Supplier

Orders

SELECT supplierID FROM suppliers
UNION
SELECT supplierID FROM orders

Result

6. LIMIT clause in SQL

Using Limits, we can specify the number of records to be displayed.

Example 1

In the below example, 4 specifies the number of records to be displayed.

mysql> SELECT * from employee limit 4;

Result

4 rows in set (0.00 sec)

Example 2

In the below example, 2 specifies the starting record number from where the records are to be displayed starting from record 0 and specifies number of records to be displayed.

mysql> SELECT * from employee limit 2, 3;

Result

3 rows in set (0.00 sec)

That’s end of our post on Different clauses in SQL