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.
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:
ORDER BY clause
GROUP BY Clause
Demo Table: Magazine
To understand all types of Clause in SQL let’s consider a demo table: "Magazine"
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.
SELECT Column1,….ColumnN From Table_name WHERE[condition];
The SQL statement to get records from this table "Magazine" SQL Query:
SELECT MagazineName,Cost,City,Language From Magazine WHERE CatID>1;
In following above illustraion, we have fetched the rows from a table using WHERE clause where CatID is greater than 1. SQL Query:
SELECT Cost,NumPage,City From Magazine WHERE MagazineName='Spy Magazine';
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.
SELECT column1,…,columnN FROM TableName ORDER BY column1,...,column ASC|DESC;
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:
SELECT MagazineName,Cost From Magazine ORDER BY Cost ASC;
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:
SELECT MagazineName,Cost,NumPage From Magazine ORDER BY NumPage DESC;
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.
SELECT Column FROM Table WHERE condition GROUP BY Column[ORDER BY Column];
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:
SELECT COUNT(MagazineName),CatID From Magazine GROUP BY CatID;
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.
SELECT Column FROM Table WHERE condition GROUP BY Column HAVING condition[ORDER BY Column];
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:
SELECT COUNT(CatID),Language From Magazine GROUP BY Language HAVING COUNT(CATID)>1;
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.
SELECT ColumnName(s)FROM TableName WHERE condition LIMIT no;
For SQL Server / MS Access Query:
SELECT TOP no|percentage ColumName(s)FROM TableName WHERE condition;
SELECT ColumnName(s)FROM TableName WHERE ROWNUM<=no;
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:
SELECT TOP2*FROM Magazine;
SELECT *FROM Magazine LIMIT2;
SELECT *FROM Magazine WHERE ROWNUM<=2;
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.
SELECT *FROM tableName WHERE condition1 ANDcondition2;
Example #1: Let us find the details of Teachers whose age is between 35 to 45 and they earn less than 50,000.
Select *from Teachers_Details where age>35andage<45andsalary<50000;
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.
SELECT *FROM tableName WHERE condition1 ORcondition2;
Example #1: Let us find the details of teachers with age more than 45 or a salary more than 40000
Select *from Teachers_Details where salary>40000ORage>45
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.
Used to retrieve data/records points that satisfy the pattern passed using the like clause.
SELECT *FROM tableName WHERE column2 LIKE pattern;
Example #1: Let us find the details of teachers whose name starts with A.
Select *from Teachers_Details where Teacher_Name LIKE'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.
It will be used to limit the data that a query would return.
SELECT *FROM tableName LIMIT number;
Example #1: Let us view the first five(5) rows of data from our table Teachers_Details.
Select *from Teachers_Details limit5;
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.