- SQL - Discussion
- SQL - Useful Resources
- SQL - Useful Functions
- SQL - Quick Guide
- SQL - Questions and Answers
- SQL - Datatype Functions
- SQL - Conversion Functions
- SQL - JSON Functions
- SQL - Cursor Functions
- SQL - Logical Functions
- SQL - Statistical Functions
- SQL - Text & Image Functions
- SQL - Numeric Functions
- SQL - Aggregate Functions
- SQL - String Functions
- SQL - Date Functions
- SQL - Database Tuning
- SQL - IN vs EXISTS
- SQL - Group By vs Order By
- SQL - Common Table Expression
- SQL - Cursors
- SQL - Date & Time
- SQL - Auto Increment
- SQL - Using Sequences
- SQL - Handling Duplicates
- SQL - Sub Queries
- SQL - Transactions
- SQL - NULL Values
- SQL - Stored Procedures
- SQL - Default Constraint
- SQL - Check Constraint
- SQL - Null Functions
- SQL - Min & Max
- SQL - Hosting
- SQL - Injection
- SQL - Comments
- SQL - Wildcards
- SQL - Non-Clustered Index
- SQL - Clustered Index
- SQL - Unique Index
- SQL - Primary Key
- - 工会诉Join
- SQL - Inner Join
- SQL - Using Joins
- SQL - Aliases
- SQL - EXCEPT Operator
- SQL - INTERSECT Operator
- SQL - UNION vs UNION ALL
- SQL - UNION Operator
- SQL - BETWEEN Operator
- SQL - NOT NULL
- SQL - IS NOT NULL
- SQL - IS NULL
- SQL - NOT EQUAL
- SQL - NOT Operator
- SQL - CASE
- SQL - EXISTS Operator
- SQL - ANY, ALL Operators
- SQL - IN Operator
- SQL - LIKE Operator
- SQL - BOOLEAN (BIT) Operator
- SQL - AND & OR
- SQL - Having Clause
- SQL - Group By Clause
- SQL - Order By Clause
- SQL - Distinct Clause
- SQL - Top Clause
- SQL - Where Clause
- SQL - Rename Views
- SQL - Drop Views
- SQL - Update Views
- SQL - Create Views
- SQL - Sorting Results
- SQL - Delete Query
- SQL - Update Query
- SQL - Insert Into Select
- SQL - Select Into
- SQL - Select Query
- SQL - Insert Query
- SQL - Constraints
- SQL - Delete Table
- SQL - Drop Table
- SQL - Alter Tables
- SQL - Temporary Tables
- SQL - Clone Tables
- SQL - Truncate Table
- SQL - Rename Table
- SQL - Show Tables
- SQL - Create Table
- SQL - Backup Database
- SQL - Show Database
- SQL - Rename Database
- SQL - Select Database
- SQL - Drop Database
- SQL - Create Database
- SQL - Expressions
- SQL - Operators
- SQL - Data Types
- SQL - Syntax
- SQL - Databases
- SQL - RDBMS Concepts
- SQL - Overview
- SQL - Home
5. 图瓦卢
- 页: 1
- 页: 1
- 结构-创建指数
- 页: 1
- 页: 1
- 页: 1
- SQL - Foreign Key
- 文 件
- ∗ E/CN.6/2009/1。
- 页: 1
- 页: 1
- 文 件
- 页: 1
- 页: 1
- 文 件
- 页: 1
Selected Reading
- Who is Who
- Computer Glossary
- HR Interview Questions
- Effective Resume Writing
- Questions and Answers
- UPSC IAS Exams Notes
SQL - WHERE Clause
The SQL WHERE clause is used to filter records while retrieving data from a single table or multiple tables (after join operation). This specifies a condition while fetching the data and if this condition is satisfied, then it returns filtered data from the table.
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 epgible for scholarships etc. This clause basically provides the specification of what type of data to be retrieved and the type of data to be neglected.
The WHERE clause is not only used in the SELECT statement, but it is also used in the UPDATE, DELETE statement, etc., which we will learn about in the subsequent chapters.
Syntax
The basic syntax of the WHERE clause is as shown below.
Statement_Reference column1, column2,... columnN FROM table_name WHERE [condition]
Here, the Statement_Reference can be any statement, such as SELECT, UPDATE, DELETE etc.
You can specify a condition using the
pke >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.WHERE Clause in SELECT statement
We can use the WHERE clause in a SELECT statement to retrieve filtered rows 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 with name CUSTOMERS in SQL database using CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Following query inserts values into this table using the INSERT statement −
insert INTO CUSTOMERS VALUES(1, Ramesh , 32, Ahmedabad , 2000.00); insert INTO CUSTOMERS VALUES(2, Khilan , 25, Delhi , 1500.00); insert INTO CUSTOMERS VALUES(3, kaushik , 23, Kota , 2000.00); insert INTO CUSTOMERS VALUES(4, Chaitap , 25, Mumbai , 6500.00); insert INTO CUSTOMERS VALUES(5, Hardik , 27, Bhopal , 8500.00); insert INTO CUSTOMERS VALUES(6, Komal , 22, MP , 4500.00); insert INTO CUSTOMERS VALUES(7, Muffy , 24, Indore , 10000.00);
If we verify the contents of the CUSTOMERS table using the SELECT statement, we can observe the inserted records as shown below −
SELECT * from CUSTOMERS; +----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitap | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
The following query is an example which would fetch the ID, Name and Salary fields from the CUSTOMERS table created above using the SELECT statement, 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 | Chaitap | 6500.00 | | 5 | Hardik | 8500.00 | | 6 | Komal | 4500.00 | | 7 | Muffy | 10000.00 | +----+----------+----------+
WHERE Clause in UPDATE statement
We can also use the WHERE clause in a UPDATE statement to update rows in a table which satisfy a certain condition. 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 trying to update the age of all the customers whose age is ‘25’ and updating it to ‘30’ using UPDATE statement −
UPDATE CUSTOMERS set AGE = 30 where AGE = 25;
Output
We get the following result. We can observe that the age of 2 customers have been modified −
(2 rows affected)
Verification
To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables as shown in the following query −
SELECT * from CUSTOMERS;
The table is displayed as follows −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 30 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitap | 30 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
As we can see in the above table, the AGE of ‘Khilan’ and ‘Chaitap’ has been updated to ‘30’.
WHERE Clause with IN operator
The WHERE clause, when with IN operator, compares the records of a table with a certain pst of values. If this clause is used with the SELECT statement, it allows us to retrieve the rows in a table that match any of the values in the specified pst. 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 pst of values that we want to compare with the column_name.
Example
Suppose you want to display records with NAME equalpng to ‘Khilan’, ‘Hardik’ and ‘Muffy’ (string values) from the CUSTOMERS table, you can use the following query IN operator −
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
Using the WHERE clause with NOT IN operator will perform the negation of WHERE clause with IN operation. The IN operator will compare the rows of table with the pst of values and the NOT operator will except the rows that are matched. Hence, when used with SELECT statement, the rows that do not match the pst of values are retrieved. Following is the syntax −
WHERE column_name NOT IN (value1, value2, ...);
Example
In this example, we are trying to display the records from CUSTOMERS table, where AGE is NOT equal to ‘25’, ‘23’ and ‘22’ as shown in the query below −
SELECT * from CUSTOMERS WHERE AGE NOT IN (25, 23, 22);
Output
We obtain the result as given below −
+----+--------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+--------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.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 | MP | 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. AND operator will make sure only those rows are filtered that satisfy all the conditions and OR operator will filtered records that satisfy any one condition specified. 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 trying to retrieve all rows from the "CUSTOMERS" table based on some conditions. The parentheses control the order of evaluation so that the OR operator is appped first, followed by the AND operator −
SELECT * FROM CUSTOMERS WHERE (AGE = 25 OR 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 | +----+----------+-----+-----------+----------+Advertisements