SQL | WHERE Clause
WHERE keyword is used for fetching filtered data in a result set. It is used to fetch data according to particular criteria. WHERE keyword can also be used to filter data by matching patterns.
SELECT column1,column2 FROM table_name WHERE column_name operator value;
- column1,column2: fields in the table
- table_name: name of table
- column_name: name of field used for filtering the data
- operator: operation to be considered for filtering
- value: exact value or pattern to get related data in result
List of Operators that Can be Used with WHERE Clause
|>=||Greater than or Equal to|
|<=||Less than or Equal to|
|<>||Not Equal to|
|BETWEEN||In an inclusive Range|
|LIKE||Search for a pattern|
|IN||To specify multiple possible values for a column|
CREATE TABLE Emp1( EmpID INT PRIMARY KEY, Name VARCHAR(50), Country VARCHAR(50), Age int(2), mob int(10) ); -- Insert some sample data into the Customers table INSERT INTO Emp1 (EmpID, Name,Country, Age, mob) VALUES (1, 'Shubham', 'India','23','738479734'), (2, 'Aman ', 'Australia','21','436789555'), (3, 'Naveen', 'Sri lanka','24','34873847'), (4, 'Aditya', 'Austria','21','328440934'), (5, 'Nishant', 'Spain','22','73248679'); Select * from Emp1;
Where Clause with Logical Operators
To fetch records of Employee with ages equal to 24.
SELECT * FROM Emp1 WHERE Age=24;
To fetch the EmpID, Name and Country of Employees with Age greater than 21.
SELECT EmpID, Name, Country FROM Emp1 WHERE Age > 21;
Where Clause with BETWEEN Operator
It is used to fetch filtered data in a given range inclusive of two values.
SELECT column1,column2 FROM table_name
WHERE column_name BETWEEN value1 AND value2;
- BETWEEN: operator name
- value1 AND value2: exact value from value1 to value2 to get related data in result set.
To fetch records of Employees where Age is between 22 and 24 (inclusive).
SELECT * FROM Emp1 WHERE Age BETWEEN 22 AND 24;
Where Clause with LIKE Operator
It is used to fetch filtered data by searching for a particular pattern in the where clause.
SELECT column1,column2 FROM
table_name WHERE column_name LIKE pattern;
- LIKE: operator name
- pattern: exact value extracted from the pattern to get related data in the result set.
Note: The character(s) in the pattern is case sensitive.
To fetch records of Employees where Name starts with the letter S.
SELECT * FROM Emp1 WHERE Name LIKE 'S%';
The ‘%'(wildcard) signifies the later characters here which can be of any length and value.
To fetch records of Employees where Name contains the pattern ‘M’.
SELECT * FROM Emp1 WHERE Name LIKE '%M%';
Where Clause with IN Operator
It is used to fetch the filtered data same as fetched by ‘=’ operator just the difference is that here we can specify multiple values for which we can get the result set.
SELECT column1,column2 FROM table_name WHERE column_name IN (value1,value2,..);
- IN: operator name
- value1,value2,..: exact value matching the values given and get related data in the result set.
To fetch the Names of Employees where Age is 21 or 23.
SELECT Name FROM Emp1 WHERE Age IN (21,23);
This article is contributed by Harsh Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.
Please Login to comment...