SQL Clause

SQL Clause Details: You may already know that SQL is typically used when dealing with a large quantity of data stored in databases. With the help of clauses, like those found built-in to SQL, managing this data becomes much more straightforward. This article will teach you all about these kinds of clauses and their various types so that you can use them more effectively while filtering and analyzing your database table’s information.

What Is A Clause In SQL?

SQL clauses can be helpful in retrieving records from a database table. They allow you to access the data within the table according to your requirements. Whatever information you are looking for, SQL statements and clauses can help you obtain it quickly and efficiently.

SQL Clause allows you to filter and analyze queries by providing conditional expressions involving column names, functions, or statements.

What are the Types of Clauses in SQL?

It is helpful to be aware of SQL’s different types of clauses. Below are some examples:

FROM	The FROM clause in SQL allows you to select database tables and use SELECT, DELETE, or UPDATE statements for manipulation.
TOP	This clause is useful when dealing with a database containing many records; it allows you to specify precisely how many results should be returned.
LIMIT	If you are dealing with large databases, the LIMIT clause can be used to control how many rows will be retrieved from a table. This is helpful to keep your query results manageable and efficient.
WHERE	The WHERE clause in SQL is used to accurately pinpoint and retrieve data from the database that matches the conditions specified within UPDATE, DELETE, or other statements.
LIKE	The SQL LIKE clause can compare similar values using wildcard operators, such as the percent sign ( % ) and the underscore operator ( _ ). This is an incredibly useful tool when searching for information in your database.
AND	When looking for a specific data set, you can use the AND clause to provide multiple conditions that must be met for it to be returned. This ensures that your criteria are satisfied and you get exactly what is needed.
OR	If you have multiple conditions in a query, the OR clause can be used to return data when any of those conditions are satisfied.
GROUP BY	To organize the rows within a result set from database tables so that those with similar values are grouped together, use the GROUP BY clause.
ORDER BY	The ORDER BY clause in SQL allows you to sort the records of the database tables according to your preference.
HAVING	The HAVING clause can be included in a GROUP BY statement to specify search criteria for the groups identified. It is typically used when you want to filter out certain rows from the group and limit results based.
WITH	The WITH clause helps make complex and lengthy queries easier to follow. It acts as a temporary view, which only exists during the execution of SELECT, UPDATE, INSERT, DELETE commands and other related operations.

Now we will see how to use the above SQL Clauses one by one with detailed examples. For Example, we are going to use the below table :

FROM CLAUSE IN SQL

The FROM SQL Clause is used to identify the table(s) from which you are trying to extract data by using a SELECT statement. It can also be used for subqueries. For retrieving the required data, If needed, more than one table can be specified by separating them with commas in this clause.

When running a query, the system will first validate it against the FROM clause before checking that any SELECT or DELETE statements have been applied correctly.

Here are the syntaxes of FROM CLAUSE IN SQL

Simple from clause syntax

SELECT * FROM <table_name> WHERE <conditions>

From SQL Clause syntax with multiple tables and with JOIN conditions

SELECT * FROM <table_name1>, <table_name2> 
WHERE <conditions_relating_the_two_tables>

Or

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

We can better understand how the FROM clause works by looking at examples that involve one table and multiple tables with JOIN conditions.

Example 1: Get all the employees who belong to the BANKING department with FROM SQL Clause.

Example 2: We will retrieve information that corresponds to both tables by utilizing a Subquery. Here we will fetch the details of the EMPLOYEEs whose department is available in the DEPARTMENTS Table.

SQL TOP CLAUSE

The SELECT TOP SQL Clause enables you to restrict the number of rows or a portion of rows returned from a query result set. Using this statement with the ORDER BY clause is recommended since the order of stored information in tables is not specified; it limits the outcome set to its first N-ordered entries.

We can use the TOP CLAUSE in 3 ways, like below:

Using TOP CLAUSE With Constant

This example will demonstrate how to use a constant value to get the ten employees.

USING THE TOP CLAUSE WITH THE PERCENTAGE

This example shows how to use PERCENT with TOP SQL CLAUSE to get the number of EMPLOYEES returned from the result set.

USING THE TOP CLAUSE WITH TIES

In this example, we will see how to use the TOP CLAUSE WITH TIES. But when you execute the query, it will give you top queries and return the matching values. And also it returns the tie values. Let’s see an example:

From the above example, you can notice when we execute with select top 3 means, it will retrieve the first three records, and the last record is 2975. But as we have executed with the TIES, it also retrieves all the matching of 2975.

We have also discussed this in our SQL Select statement. You can check the post by following the links where we have discussed a few more examples and how to use the SQL TOP Clause.

SQL WHERE CLAUSE

We have now learned to get all records from a table or specific columns. In practical situations, however, we typically need to select, update and/or delete those entries that meet certain conditions, such as users within a specified age range or residing in an exact country, etc.

The WHERE clause can be used when executing SELECT, UPDATE, and DELETE statements. We’ll learn more about its use with other commands in other posts.

Syntax For SQL Where Clause

You can use the SELECT statement in combination with a WHERE clause to extract only those records that meet certain conditions. The basic syntax is as follows:

SELECT [columns] 
FROM [table] 
WHERE [condition];

Now let us discuss some examples to understand how to use the SQL Where Clause. And to understand the example, we will use the Employee Table

Example 1: Get the employee details whose salary is 1250.

Operators Allowed in WHERE Clause in SQL

The WHERE clause is used to filter values in a database. Examples of some of the possible operators that can be used are shown in the following table:

Operator	Description	Example
=	Equal	WHERE SAL= 1100
>	Greater than	WHERE MGR > 4
<	Less than	WHERE MG4 < 4
>=	Greater than or equal	WHERE MGR >= 4
<=	Less than or equal	WHERE SAL <= 1100
LIKE	Simple pattern matching	WHERE name LIKE ‘KIN’
IN	Check whether a specified value matches any value in a list or subquery	WHERE country IN (‘BANKING’, ‘SERVICES’)
BETWEEN	Check whether a specified value is within a range of values.	WHERE SAL BETWEEN 1300 AND 5000

SQL LIKE STATEMENT

The SQL Like operator or sql is like statement is a great tool that can be used to locate specific character strings in columns. It’s often utilized within the Where clause of an SQL query, and it helps us search for patterns instead of using equal or not equal comparisons. This can come in very handy when we need to retrieve rows based on certain string matches!

The like operator sql allows you to perform pattern matching using wildcard characters. To use this feature, two special wildcard characters are available, and they are below:

• The underscore (_) – Matches exactly one character.
• The percent sign (%) – Matches any number of characters, even zero characters.

Here is an example of how to use the above wildcard characters with the like operator sql:

LIKE OPERATOR SQL WITH UNDERSCORE (_) Example

Example 1: Find the employee records whose name ends with ARD and is preceded by at most one character.

LIKE OPERATOR SQL WITH PERCENT SIGN (%) Example

Example 2: Find the employee details whose name starts with S.

In a single sql like statement, we use multiple underscores (_), percent sign (%), or use both wildcard characters. Here are some examples:

Example 3: Multiple underscores (_) in a single sql like statement.

Example 4: Multiple percent sign (%) in a single sql like statement.

Example 5: Multiple wildcard characters in a single sql like statement.

SQL AND Clause

You can use the AND clause to incorporate multiple conditions into an SQL statement’s WHERE clause. And the basic syntax for that is:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

You can use the AND operator to combine any number of conditions. For a SQL statement, such as a transaction or query, to be executed successfully, all conditions are separated by the AND operator and must evaluate to TRUE.

Example: Suppose you want to retrieve all the records of the employee table whose salary is greater than 1500, and he/ she should also work in the SERVICES department.

SQL OR CLAUSE

The OR operator is used to join multiple expressions within the WHERE clause of an SQL statement. This allows for greater flexibility in specifying conditions that must be met.

The basic syntax for the OR clause with where:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

You can use the OR operator to combine various conditions. Just one of these conditions must be true for an action, such as a transaction or query, to take place in the SQL statement.

Example: Suppose you want to retrieve all the records of the employee table whose salary is greater than 5000, OR he/ she should also work in the SERVICES department.

If you saw the output, there is no employee whose salary is greater than 5000, but the Department condition is satisfied. So we are getting the below records.

SQL GROUP BY CLAUSE

The GROUP BY clause can be used to understand the summary data based on one or more groups. You can form these groups using multiple columns and determine important information such as the number of employees in each department or the total salaries for that particular department.

It is important to use aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() within your SELECT query. The GROUP BY clause will return a single row for each value in the specified column.

Syntax For SQL GROUP BY

SELECT column1, column2,...columnN FROM table_name
[WHERE]
[GROUP BY column1, column2...columnN]
[HAVING]
[ORDER BY]

To display the number of employees for each department, We need to include only DeptId in the SELECT clause. To view other columns, use aggregate functions like COUNT(EmpID).

Example 1:

SELECT DEPTID, COUNT(ENAME) as 'Number of Employees' 
FROM EMPLOYEE
GROUP BY DEPTID;

--The Following Query Will Also Return The Same Result
SELECT DEPTID, COUNT(*) as 'No of Employees' 
FROM EMPLOYEE
GROUP BY DEPTID;

This will give us a result that is aliased as ‘No of Employees’ as below:

Example 2: This query will return the department name instead of DeptId in the results.

SELECT dept.DEPTNAME as 'Department', count(emp.DEPTID) as 'No of Employees'
FROM Employee emp, departments dept
WHERE emp.DEPTID= dept.DEPTID
GROUP by dept.DEPTNAME;

Explanation:

The above SQL query retrieves the number of employees in each department from two tables, the “Employee” table, and the “departments” table, by joining them on the common “DEPTID” column. The result is a table with two columns: “Department” and “No of Employees”.

• SELECT statement: It defines the columns in the result set. The first column is an alias for the “DEPTNAME” column from the “departments” table, and the second column is the count of the number of employees in each department, calculated from the “DEPTID” column of the “Employee” table.
• FROM clause: It defines the source tables for the query. The two tables used are the “Employee” table and the “departments” table.
• WHERE clause: It specifies the join condition between the two tables. The query only returns rows where the “DEPTID” column in the “Employee” table matches the “DeptId” column in the “departments” table.
• GROUP BY clause: It groups the results by the “DEPTNAME” column from the “departments” table so that the count of employees in each department is calculated separately.

The query result will be a table showing the department name and the number of employees in each department.

Example 3: By following the same way, let’s find out the total salaries based on the departments using sql group by sum.

SELECT dept.DEPTNAME as 'Department', sum(emp.sal) as 'Total Salaries'
FROM Employee emp, departments dept
WHERE emp.DEPTID= dept.DEPTID
GROUP by dept.DEPTNAME;

SQL ORDER BY CLAUSE

In SQL Server, you can use the ORDER BY clause in your SELECT query to arrange the results in ascending or descending order based on one or more columns.

Syntax For the SQL ORDER BY CLAUSE is:

SELECT column1, column2,...columnN 
FROM table_name
[WHERE]
[GROUP BY]
[HAVING]
[ORDER BY column(s) [ASC|DESC]]

Example 1: Fetch all the records of the Employee table, but the records should be sorted in Descending order based on the name.

SQL Order By Multiple Columns

Using the ORDER BY clause, we can include multiple columns in different sorting orders for an organized output. If two or more records have similar values in the first ordered column, these will be sorted by their values on the second one.

Example 2:

SELECT * FROM Employee
ORDER BY SAL, ENAME;

When we execute the above query, you can notice the below result:

If you notice the retrieved records, then you can see. First, the records are sorted in ascending order based on their salary, and when there is the same salary for multiple records, the sorting of records is decided based on the second column name, which is the name.

Example 3:

Now let us try to use the GROUP By

Here we retrieve the records and sort them in descending order.

SQL HAVING CLAUSE

The GROUP BY clause is typically used with the HAVING clause, allowing you to filter your results based on certain conditions. The HAVING clause works similarly to the WHERE clause – both are used for filtering records in SQL queries.

However, it’s important to note that due to HAVING clause compatibility with aggregate functions (e.g., COUNT(), MAX(), SUM(), etc.) but with WHERE clause, we cannot be applied aggregate functions; this is where HAVING clause comes into play!

Here is the code block which has the syntax of Having Clause with Select Statement:

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

We will use the Employee and departments table to understand how to use the Having Clause.

Example 1:

If we execute the below SQL query, then we get the following result:

In the above query, we are trying to get the Department Name and Total Salary from the Employee and Departments table. To get the expected result, we need to combine both the Employee and Departments tables: for that, we can take the help of a common column in both tables, which is DEPTID.

But if we want to retrieve only those departments which have a total salary greater than 9000, in that case, we can add one more filter with the Having clause.

Conclusion:

In conclusion, the SQL clause is a fundamental component of the Structured Query Language (SQL) that enables developers and data professionals to manipulate and extract data from databases. SQL clauses such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY provide the necessary tools for querying data, filtering, aggregating, and sorting results.

Using clauses in SQL makes it a robust data management and analysis language. The versatility of SQL clauses means that they can be combined in various ways to meet specific data management needs, making it an essential tool for anyone working with databases.