Category: Operators and Clauses

The SQL ORDER BY Clause

The SQL ORDER BY clause is used to sort the data in either ascending or descending order, based on one or more columns. This clause can sort data by a single column or by multiple columns. Sorting by multiple columns can be helpful when you need to sort data hierarchically, such as sorting by state, city, and then by the person’s name.

ORDER BY is used with the SQL SELECT statement and is usually specified after the WHERE, HAVING, and GROUP BY clauses.

Following are the important points about ORDER BY Clause −

• Some databases sort the query results in an ascending order by default.
• To sort the data in ascending order, we use the keyword ASC.
• To sort the data in descending order, we use the keyword DESC.

In addition to sorting records in ascending order or descending order, the ORDER BY clause can also sort the data in a database table in a preferred order.

This preferred order may not sort the records of a table in any standard order (like alphabetical or lexicographical), but they could be sorted based on external condition(s).

For instance, in the CUSTOMERS table containing the details of the customers of an organization, the records can be sorted based on the population of the cities they are from. This need not be alphabetically sorted, instead, we need to define the order manually using the CASE statement.

Syntax

The basic syntax of the ORDER BY clause is as follows −

SELECTcolumn-list
FROM table_name
[ORDERBY column1, column2,.. columnN][ASC|DESC];

Where, column-list is list of the columns we want to retrieve; and ASC or DESC specifies the sort order.

Note: We can use more than one column in the ORDER BY clause, but we need to make sure that the column we are using to sort is specified in the column-list.

ORDER BY Clause with ASC

We can sort the result-set of a query in ascending order (based on one or more columns) using the SQL ORDER BY clause by specifying ASC as the sort order. ASC is the default sort order for this clause, i.e. while using the ORDER BY clause if you do not explicitly specify the sort order, the data will be sorted in ascending order.

Example

Assume we have created a table with name CUSTOMERS in the MySQL database using CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Following query inserts values into this table using the INSERT statement −

insertINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table obtained is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

In the following query, we are sorting the records of the CUSTOMERS table in ascending order based on the column NAME −

SELECT*FROM CUSTOMERS ORDERBY NAME ASC;

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
3	Kaushik	23	Kota	2000.00
2	Khilan	25	Delhi	1500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00
1	Ramesh	32	Ahmedabad	2000.00

ORDER BY Clause with DESC

To sort the result-set of a query in descending order (based on one or more columns), we need to use the ORDER BY clause by specifying DESC as the sort order.

Example

The following query sorts the records of the CUSTOMER table based on the descending order of the name of the customers −

SELECT*FROM CUSTOMERS ORDERBY NAME DESC;

Output

This would produce the result as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
7	Muffy	24	Indore	10000.00
6	Komal	22	Hyderabad	4500.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00

ORDER BY Clause on Multiple Columns

We can use the ORDER BY clause to sort the result-set of a query by multiple (more than one) columns. When sorting by multiple columns, the sorting is done in the order that is specified in the ORDER BY clause. In other words, the table will be sorted based on the first column (specified in the query), then the second column, and so on.

Example

In the following query, we are retrieving all records from the CUSTOMERS table and sorting them first by their address in ascending order, and then by their salary in descending order −

SELECT*FROM CUSTOMERS ORDERBY AGE ASC, SALARY DESC;

Output

Following is the result produced −

ID	NAME	AGE	ADDRESS	SALARY
6	Komal	22	Hyderabad	4500.00
3	Kaushik	23	Kota	2000.00
7	Muffy	24	Indore	10000.00
4	Chaitali	25	Mumbai	6500.00
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
1	Ramesh	32	Ahmedabad	2000.00

ORDER BY with WHERE Clause

We can also use the WHERE clause with the ORDER BY clause to sort the rows that meet certain conditions. This can be useful when we want to sort a subset of the data in a table based on the specific criteria.

Example

Now, we are retrieving all records from the CUSTOMERS table where the age of the customer is 25, and sorting them as per the descending order of their names −

SELECT*FROM CUSTOMERS 
WHERE AGE =25ORDERBY NAME DESC;

Output

Following is the output of the above query −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00

ORDER BY with LIMIT Clause

We can use the LIMIT clause with ORDER BY clause to limit the specified number of rows by sorting them either in ascending or in descending order.

Syntax

Following is the syntax of using the LIMIT clause with the ORDER BY clause in MySQL database −

SELECT column1, column2,...FROM table_name
ORDERBY column_name1 [ASC|DESC], column_name2 [ASC|DESC],...LIMIT N;

Example

In here, we are retrieving the top 4 records from the CUSTOMERS table based on their salary, and sorting them in ascending order based on their name −

SELECT SALARY FROM CUSTOMERS ORDERBY NAME LIMIT4;

Output

Following is the output of the above query −

SALARY
6500.00
8500.00
2000.00
1500.00

Sorting Results in a Preferred Order

One can also sort the records of a table in their own preferred order using the CASE statement within the ORDER BY clause. All the values are specified in the clause along with the position they are supposed to be sorted in; if the values are not given any number, they are automatically sorted in ascending order.

Example

To fetch the rows with their own preferred order, the SELECT query used would be as follows −

SELECT*FROM CUSTOMERS ORDERBY(CASE ADDRESS
   WHEN'MUMBAI'THEN1WHEN'DELHI'THEN2WHEN'HYDERABAD'THEN3WHEN'AHMEDABAD'THEN4WHEN'INDORE'THEN5WHEN'BHOPAL'THEN6WHEN'KOTA'THEN7ELSE100END);

Output

The above query sorts the CUSTOMERS table based on the custom order defined using the CASE statement. Here, we are sorting the records based on the population of the cities specified in the ADDRESS column.

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
2	Khilan	25	Delhi	1500.00
6	Komal	22	Hyderabad	4500.00
1	Ramesh	32	Ahmedabad	2000.00
7	Muffy	24	Indore	10000.00
5	Hardik	27	Bhopal	8500.00
3	Kaushik	23	Kota	2000.00

The SQL DISTINCT Keyword

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table.

We use DISTINCT keyword with the SELECT statetment when there is a need to avoid duplicate values present in any specific columns/tables. When we use DISTINCT keyword, SELECT statement returns only the unique records available in the table.

The SQL DISTINCT Keyword can be associated with SELECT statement to fetch unique records from single or multiple columns/tables.

Syntax

The basic syntax of SQL DISTINCT keyword is as follows −

SELECTDISTINCT column1, column2,.....columnN 
FROM table_name;

Where, column1, column2, etc. are the columns we want to retrieve the unique or distinct values from; and table_name represents the name of the table containing the data.

DISTINCT Keyword on Single Columns

We can use the DISTINCT keyword on a single column to retrieve all unique values in that column, i.e. with duplicates removed. This is often used to get a summary of the distinct values in a particular column or to eliminate redundant data.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Following query inserts values into this table using the INSERT statement −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table obtained is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

First, let us retrieve the SALARY values from the CUSTOMERS table using the SELECT query −

SELECT SALARY FROM CUSTOMERS ORDERBY SALARY;

This would produce the following result. Here, you can observe that the salary value 2000 is appearing twice −

SALARY
1500.00
2000.00
2000.00
4500.00
6500.00
8500.00
10000.00

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result −

Open Compiler

SELECTDISTINCT SALARY FROM CUSTOMERS ORDERBY SALARY;

Output

This would produce the following result where we do not have any duplicate entry −

SALARY
1500.00
2000.00
4500.00
6500.00
8500.00
10000.00

DISTINCT Keyword on Multiple Columns

We can also use the DISTINCT keyword on multiple columns to retrieve all unique combinations of values across those columns. This is often used to get a summary of distinct values in multiple columns, or to eliminate redundant data.

Example

In the following query, we are retrieving a list of all unique combinations of customer’s age and salary using the DISTINCT keyword −

SELECTDISTINCT AGE, SALARY FROM CUSTOMERS ORDERBY AGE;

Output

Though the AGE column have the value “25” in two records, each combination of “25” with it’s specific ‘salary’ is unique, so both rows are included in the result set −

AGE	SALARY
22	4500.00
23	2000.00
24	10000.00
25	1500.00
25	6500.00
27	8500.00
32	2000.00

DISTINCT Keyword with COUNT() Function

The COUNT() function is used to get the number of records retuned by the SELECT query. We need to pass an expression to this function so that the SELECT query returns the number of records that satisfy the specified expression.

If we pass the DISTINCT keyword to the COUNT() function as an expression, it returns the number of unique values in a column of a table.

Syntax

Following is the syntax for using the DISTINCT keyword with COUNT() function −

SELECTCOUNT(DISTINCT column_name)FROM table_name WHERE condition;

Where, column_name is the name of the column for which we want to count the unique values; and table_name is the name of the table that contains the data.

Example

In the following query, we are retrieving the count of distinct age of the customers −

SELECTCOUNT(DISTINCT AGE)as UniqueAge  FROM CUSTOMERS;

Output

Following is the result produced −

UniqueAge
6

DISTINCT Keyword with NULL Values

In SQL, when there are NULL values in the column, DISTINCT treats them as unique values and includes them in the result set.

Example

First of all let us update two records of the CUSTOMERS table and modify their salary values to NULL

UPDATE CUSTOMERS SET SALARY =NULLWHERE ID IN(6,4);

The resultant CUSTOMERS table would be −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	NULL
7	Muffy	24	Indore	10000.00

Now, we are retrieving the distinct salary of the customers using the following query −

SELECTDISTINCT SALARY FROM CUSTOMERS ORDERBY SALARY;

Output

Following is the output of the above query −

SALARY
NULL
1500.00
2000.00
8500.00
10000.00

The SQL TOP Clause

While we are retrieving data from an SQL table, the SQL TOP clause is used to restrict the number of rows returned by a SELECT query in SQL server. In addition, we can also use it with UPDATE and DELETE statements to limit (restrict) the resultant records.

For instance, if you have a large number of data stored in a database table, and you only want to perform operations on first N rows, you can use the TOP clause in your SQL server query.

MySQL database does not support TOP clause instead of this, we can use the LIMIT clause to select a limited number of records from a MySQL table. Similarly, Oracle supports the ROWNUM clause to restrict the records of a table. The TOP clause is similar to the LIMIT clause.

Syntax

The basic syntax of the SQL TOP clause is as follows −

SELECTTOPvalue column_name(s)FROM table_name
WHERE[condition]

Where, value is the number/ percentage of number of rows to return from the top.

Example

To understand it better let us consider the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Now, insert values into this table using the INSERT statement as follows −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table will be created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, we are using the TOP clause to fetch the top 4 records from the CUSTOMERS table without specifying any conditional clauses such as WHERE, ORDER BY, etc. −

SELECTTOP4*FROM CUSTOMERS;

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00

TOP with ORDER BY Clause

The ORDER BY clause in SQL is used to sort the result-set of a query in ascending or descending order. We can use it along with the TOP clause to retrieve (or, update or, delete etc.) first N records in sorted order.

Example

Using the following query we are retrieving the top 4 records of the CUSTOMERS table in a sorted order. Here, we are sorting the table in descending order based on the SALARY column −

SELECTTOP4*FROM CUSTOMERS ORDERBY SALARY DESC;

Output

We obtain the result as follows −

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	10000.00
5	Hardik	27	Bhopal	8500.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00

Note − By default, the ORDER BY clause sorts the data in ascending order. So, if we need to sort the data in descending order, we must use the DESC keyword.

TOP Clause with PERCENT

We can also restrict the records by specifying percentage value instead of number, using the PERCENT clause along with the TOP clause.

Example

The following query selects the first 40% of the records from the CUSTOMERS table sorted in the ascending order by their SALARY −

SELECTTOP40PERCENT*FROM CUSTOMERS ORDERBY SALARY

Output

We have the total of 7 records in our table. So 40% of 7 is 2.8. Therefore, SQL server rounds the result to three rows (the next whole number) as shown in the output below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
1	Ramesh	32	Ahmedabad	2000.00

TOP with WHERE Clause

We can use the TOP clause with the WHERE clause to limit the given number of rows and filter them based on a specified condition.

Example

Following is the query to show the details of the first two customers whose name starts with K from the CUSTOMERS table −

SELECTTOP2*FROM CUSTOMERS WHERE NAME LIKE'k%'

Output

Following result is produced −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

TOP Clause With DELETE Statement

The TOP clause can be used with the DELETE statement to delete a specific number of rows that meet the given criteria.

Example

In the following query, we are using DELETE statement with TOP clause. Here, we are deleting the top 2 customers whose NAME starts with K −

DELETETOP(2)FROM CUSTOMERS WHERE NAME LIKE'K%';

Output

We get the output as shown below −

(2 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as shown below −

SELECT*FROM CUSTOMERS;

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

TOP and WITH TIES Clause

While sorting the data in a table using the ORDER BY clause based on a column, some times multiple rows may contain same values in the column(s) specified in the ORDER BY clause.

If you try to restrict the number of records using the TOP clause, all the eligible columns may not be filtered.

The WITH TIES clause is used to ensure that the records having the same values (records with “tied” values) are included in the query results.

Example

Consider the above created table CUSTOMERS. If we need to retrieve the top 2 customers sorted by the ascending order of their SALARY values, the query would be −

SELECTTOP2*FROM CUSTOMERS ORDERBY SALARY;

The resultant table would be −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00

But, the first two salary values (in ascending order) in the table are 1500 and 2000 and there is another column in the CUSTOMERS table with salary value 2000 which is not included in the result.

If you want to retrieve all the columns with first two salary values (when arranged in the ascending order). We need to use the WITH TIES clause as showb below −

SELECTTOP2WITH TIES *FROM CUSTOMERS ORDERBY SALARY;

Output

The resultant table would be −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
1	Ramesh	32	Ahmedabad	2000.00

Uses of TOP Clause

Some common use cases for the TOP clause include −

• Pagination − When displaying a large number of records, the TOP clause can be used to return only a certain number of records at a time, allowing for more manageable and efficient pagination of the data.
• Sampling data − The TOP clause can be used to quickly retrieve a sample of data from a table for testing or analysis.
• Improving performance − By limiting the number of rows returned, the TOP clause can help improve the performance of a query, especially when dealing with large tables.
• Debugging − When developing or debugging a query, the TOP clause can be used to quickly return a small number of rows to test the correctness of the query.
• Data visualization − The TOP clause can be used to limit the number of rows returned for visualization purposes, such as creating charts or graphs.

The SQL Where Clause

The SQL WHERE clause is used to filter the results obtained by the DML statements such as SELECT, UPDATE and DELETE etc. We can retrieve the data from a single table or multiple tables(after join operation) using the WHERE clause.

For instance, you can use the WHERE clause to retrieve details of employees of a department in an organization, or employees earning salary above/below certain amount, or details of students eligible for scholarships etc. This clause basically provides the specification of which records to be retrieved and which are to be to be neglected.

Syntax

The basic syntax of the SQL WHERE clause is as shown below −

DML_Statement column1, column2,... columnN
FROM table_name
WHERE[condition];

Here, the DML_Statement can be any statement, such as SELECT, UPDATE, DELETE etc.

You can specify a condition using the comparison or logical operators such as, >, <, =, LIKE, NOT, etc.

WHERE Clause with SELECT Statement

Typically, the SELECT statement is used to retrieve data from a table. If we use the WHERE clause with the SELECT statement, we can filter the rows to be retrieved based on a specific condition (or expression). Following is the syntax for it −

SELECT column1, column2,...FROM table_name
WHERE condition;

Example

Assume we have created a table named CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

CREATETABLE CUSTOMERS (
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18,2),PRIMARYKEY(ID));

Following INSERT query inserts 7 records into this table −

INSERTINTO CUSTOMERS VALUES(1,'Ramesh',32,'Ahmedabad',2000.00),(2,'Khilan',25,'Delhi',1500.00),(3,'Kaushik',23,'Kota',2000.00),(4,'Chaitali',25,'Mumbai',6500.00),(5,'Hardik',27,'Bhopal',8500.00),(6,'Komal',22,'Hyderabad',4500.00),(7,'Muffy',24,'Indore',10000.00);

The table created is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

In the following query, we are fetching the ID, NAME and SALARY fields from the CUSTOMERS table for the records where the SALARY is greater than 2000 −

SELECT ID, NAME, SALARY FROM CUSTOMERS 
WHERE SALARY >2000;

Output

This would produce the following result −

ID	NAME	SALARY
4	Chaitali	6500.00
5	Hardik	8500.00
6	Komal	4500.00
7	Muffy	10000.00

WHERE Clause with UPDATE Statement

The UPDATE statement is used to modify the existing records in a table. Using the SQL WHERE clause with the UPDATE statement, we can update particular records. If the WHERE clause is not used, the UPDATE statement would affect all the records of a table. Following is the syntax −

UPDATE table_name
SET column1 = value1, column2 = value2,...WHERE condition;

Example

In the following query, we are incrementing the salary of the customer named Ramesh by 10000 by using the WHERE clause along with the UPDATE statement −

UPDATE CUSTOMERS set SALARY = SALARY+10000where NAME ='Ramesh';

Output

We get the following result. We can observe that the age of 2 customers have been modified −

Query OK, 2 rows affected (0.02 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Verification

To verify if the changes are reflected in the table, we can use SELECT statement as shown in the following query −

SELECT*FROM CUSTOMERS WHERE NAME ='Ramesh';

The table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	12000.00

WHERE Clause with IN Operator

Using the IN operator you can specify the list of values or sub query in the where clause. If you use WHERE and IN with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified list. Following is the syntax for it −

WHERE column_name IN(value1, value2,...);

Where, the column_name is the column of a table and value1, value2, etc. are the list of values that we want to compare with the column_name.

Example

Suppose you want to display records with NAME values Khilan, Hardik and Muffy from the CUSTOMERS table, you can use the following query −

SELECT*from CUSTOMERS 
WHERE NAME IN('Khilan','Hardik','Muffy');

Output

The result obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

WHERE Clause with NOT IN Operator

The WHERE clause with NOT IN operator is the negation of WHERE clause with the IN operator.

• If you use WHERE with the IN operator, the DML statement will act on the the list of values (of a column) specified
• Whereas, if you use WHERE with the NOT IN operator, the DML operation is performed on the values (of a column) that are not there in the specified list.

Hence, if you use WHERE Clause with NOT IN Operator along with the SELECT statement, the rows that do not match the list of values are retrieved. Following is the syntax −

WHERE column_name NOTIN(value1, value2,...);

Example

In this example, we are displaying the records from CUSTOMERS table, where AGE is NOT equal to 25, 23 and 22.

SELECT*from CUSTOMERS WHERE AGE NOTIN(25,23,22);

Output

We obtain the result as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	12000.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

WHERE Clause with LIKE Operator

The WHERE clause with LIKE operator allows us to filter rows that matches a specific pattern. This specific pattern is represented by wildcards (such as %, _, [] etc). Following is the syntax −

WHERE column_name LIKE pattern;

Where, column_name is the column that we want to compare the pattern against and pattern is a string that can contain wildcards (such as %, _, [] etc).

Example

Following is the query which would display all the records where the name starts with K and is at least 4 characters in length −

SELECT*FROM CUSTOMERS WHERE NAME LIKE'K___%';

Output

The result obtained is given below −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
6	Komal	22	Hyderabad	4500.00

WHERE Clause with AND, OR Operators

We can use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria. The AND operator will make sure only those rows are filtered that satisfy all the conditions and the OR operator will filter records that satisfy any one of the specified conditions. However, this is only used when specifying one condition is not enough to filter all the required rows.

Following is the syntax for using the AND and OR operators in a WHERE clause −

WHERE(condition1 OR condition2)AND condition3;

Example

In the following query, we are retrieving all rows from the CUSTOMERS table based on some conditions. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −

SELECT*FROM CUSTOMERS
WHERE(AGE =25OR salary <4500)AND(name ='Komal'OR name ='Kaushik');

Output

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	23	Kota	2000.00