Clause in SQL | List of Main Types of SQL Clauses with Syntax & Example

Summary: In this article, you will learn Clause in SQL – List of Main Types of SQL Clause with Syntax and Example. These Clause in SQL plays an important role. Let’s understand all main types of clause in SQL with details.
Clause in SQL | Types of SQL Clauses

Introduction and Role of SQL Clauses

MySQL queries are SQL functions that facilitate us to access a specific/particular set of records from a database table. We can request or demand any info or data from the database using the clauses or, let’s say, SQL statements. For illustration, SQL Clauses receives a conditional expression that can be a column name or valid term involving columns where this supports the MySQL functions to calculate the result values for a table in the database.

Basically, there are five main kinds of SQL Clauses in MySQL Server. They are listed as following:

  • WHERE Clause
  • ORDER BY clause
  • HAVING Clause
  • TOP Clause
  • GROUP BY Clause

Demo Table: Magazine

To understand all types of Clause in SQL let’s consider a demo table: "Magazine"

ID MagazineName Cost NumPage City CatID Language
1 Playpen 250 30 Lucknow 2 English
2 Little Women 150 35 Delhi 1 Hindi
3 Runway 220 40 Noida 3 English
4 Spy Magazine 180 25 Ghaziabad 1 Hindi
5 Philadelphia Story 240 21 Jhasi 2 English
6 Woollett 120 11 Delhi 1 Hindi

Types of SQL Clauses

There are various types of SQL clauses are given below to execute the statement:

1). SQL WHERE Clause

We use the SQL SELECT statement to select data/information from a table in the database. Here, the WHERE clause permits filtering certain or specific records that exactly match a given condition. Therefore, it helps us to fetch only the required information from the database that satisfies the given expressional conditions. Further, The WHERE clause is used with the SELECT statement similarly to UPDATE, DELETE type statements, and aggregate functions to restrict the number of records or data to be retrieved by the table. Thus, we can also use logical or comparison operators such as LIKE, =, etc. with the WHERE clause to fulfill certain conditions.


The SQL statement to get records from this table "Magazine"
SQL Query:

Example of SQL WHERE Clause
In following above illustraion, we have fetched the rows from a table using WHERE clause where CatID is greater than 1.
SQL Query:
Example of WHERE Clause in SQL

2). SQL ORDER BY Clause

The ORDER BY clause is used in SQL for the purpose of sorting records/data. also, it’s used to arrange the result set either in ascending (ASC) or descending (DESC) order. Furthermore, when we query using a SELECT statement the result isn’t in an ordered form. Hence, the result rows can be sorted when we combine or amalgamate the SELECT statement with the SQL ORDER BY clause.


We add ASC for ascending order and DSC for descending order with the column name in the query to display/show the result rows in an ordered form.
SQL Query:

Example of SQL ORDER BY
The result table is retrieved with columns that are sorted in ascending order(ASC) and the given below SQL query will be returned in descending(DESC) order.
SQL Query:

3). SQL GROUP BY Clause

The GROUP BY statement is used to arrange identical or similar data into groups with the help of a few functions. i.e if a particular/specific column has an identical value in different rows then it will arrange these rows in a group. Remember Note: The GROUP BY clause is utilized with the SELECT statement.

In another way, The GROUP BY clause is used to group rows that have identical values in the result set. Like if we find out the names of Magazines from the table grouped by CatID.


This clause is mainly used with aggregate functions that permit grouping the query result rows by multiple columns. Thus, the aggregate functions are COUNT, SUM, AVG, MAX, MIN, etc.

We have the following given example:

Example of SQL GROUP BY
The SQL GROUP BY clause in SQL returns the aggregated value applying the functions on the columns of the table. The above screenshot shows that the result is returned grouped by CatID the where the number of MagazineName present in those CatID is fetched.

4). SQL HAVING Clause

The SQL HAVING clause specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified or given conditions. HAVING & WHERE are often confused by beginners, but they serve different purposes.

Actually, this clause is introduced or presented to apply functions in the query with the SQL WHERE clause. In SQL, the HAVING clause was added because the WHERE clause couldn’t be applied with aggregate functions.


We can also utilize the SQL HAVING clause with logical operators like OR and AND.
Let us consider the SQL statement given below to learn the clause:

Here you can see a screenshot that the result table is returned where the columns are grouped by Language and the number of rows is restricted by the HAVING clause by providing a condition that CatID should be greater than 1.

5). SQL TOP Clause

The TOP clause is used to determine or find out the number of record rows to be shown in the result. Furthermore, this TOP clause is used with SELECT statements especially implemented or enforced on large tables with several records. But the clause isn’t supported in many database systems such as MySQL supports the LIMIT clause to select the limited number of rows & in Oracle ROWNUM is used.

MySQL Query:
For SQL Server / MS Access Query:
Oracle Query:
For example, we can describe this clause by these SQL statements where we can return the rows using TOP Clause with SELECT and the WHERE for different or several database platforms:

SQL Query:

SQL TOP Clause

Clause in SQL With Logical Operators Like OR, AND, Like, Limit

I) And Clause in SQL

We use And clause whereas specifying multiple conditions together in a query with the SQL Where clause.
Rules and Usage:
1). Rules:
When using an And clause, we are required to mention at least two conditions(the result would satisfy each.)
2). Usage:

  • It is used to query and transact the database.
  • Also can be used to update & delete statements to make sure the right data points are deleted.
  • Returns a data point only if all conditions meet/match with the requirements.

Example #1: Let us find the details of Teachers whose age is between 35 to 45 and they earn less than 50,000.

II) OR Clause in SQL

Or clause is beneficial or we can say that helpful when we need to pass multiple conditions, and we required data that satisfies any one of those specified conditions.
Rules and Usage:
1). Rules:
When using an Or clause, then we need to mention at least two conditions(i.e. the result would be at least 1 of the specified conditions.)
2). Usage:

  • It can be used while transacting and querying a database and also can be used in the update and delete statements.
  • Furthermore, Or clause returns a data point when any one of the specified conditions is matched.
Example #1: Let us find the details of teachers with age more than 45 or a salary more than 40000

III) Like Clause in SQL

LIKE clause is helpful to find specific patterns in the data. Furthermore, we use specific symbols i.e (%) and ( _ ). The rules and usage is described below.
Rules and Usage:
1). Rules:

  • % :– It will represent zero, one, or multiple characters.
  • _ :– This represents one single character.

2). Usage:
Used to retrieve data/records points that satisfy the pattern passed using the like clause.

Example #1: Let us find the details of teachers whose name starts with A.

IV) Limit Clause in SQL

We can use the limit clause when we have a large quantity of data in the table. Furthermore, with the help of the limit clause, we can restrict the no. of rows our query returns.
Rules and Usage:
1). Rules:

  • We are required to specify a number after the limit clause.
  • Float & exponential values can not be utilized.

2). Usage:
It will be used to limit the data that a query would return.

Example #1: Let us view the first five(5) rows of data from our table Teachers_Details.

Conclusion: Clause in SQL

These SQL Clauses support in many ways for database access and data extraction by applying any filter that describes the conditions and if the conditions qualify for satisfying it then we get the actual result rows.

Furthermore, the information from a table that doesn’t satisfy/assure the conditions isn’t used. We have to deal with several big databases that are used today in many fields such as in E-commerce shopping, Education, Banking sector, Management, and other digital fields where a large number of data or information are stored and we are required to be retrieved from time to time.

Thus, SQL statements, operators, functions, and keywords in combination/together with SQL clauses make the info access proper & manageable to deal with different tables in a database.