Introduction
The WHERE clause is a fundamental component of SQL queries that allows you to filter rows based on specific conditions. This enables you to retrieve only the data that meets your criteria, making your queries more efficient and informative. In this blog post, we'll explore various WHERE clause conditions and provide examples using a sample STUDENTS table.
Our Sample Table: STUDENTS
Column Name | Data Type |
STUDENT_ID | NUMBER(10) |
STUDENT_NAME | VARCHAR2(50) |
ADDRESS | VARCHAR2(100) |
PHONE | VARCHAR2(20) |
CITY | VARCHAR2(50) |
DISTRICT | VARCHAR2(50) |
STATE | VARCHAR2(50) |
Basic WHERE Clause
The simplest form of the WHERE clause compares a column value to a specific value:
SQL
SELECT *
FROM STUDENTS
WHERE column_name = value;
Example:
To retrieve information about students from Chandigarh:
SQL
SELECT *
FROM STUDENTS
WHERE CITY = 'Chandigarh';
Comparison Operators
You can use various comparison operators in the WHERE clause:
=``: Equal to
<: Less than
>: Greater than
<=: Less than or equal to
>=: Greater than or equal to
<> or !=: Not equal to
Example:
To find students whose STUDENT_ID is greater than 100:
SELECT *
FROM STUDENTS
WHERE STUDENT_ID > 100;
Logical Operators
To combine multiple conditions, you can use logical operators:
AND: Both conditions must be true.
OR: At least one condition must be true.
NOT: Reverses the result of a condition.
Example:
To find students from Chandigarh who are studying in the 'Computer Science' department (assuming there's a DEPARTMENT column):
SELECT *
FROM STUDENTS
WHERE CITY = 'Chandigarh' AND DEPARTMENT = 'Computer Science';
LIKE Operator
The LIKE operator is used to search for patterns within strings:
%: Represents any number of characters.
_: Represents a single character.
Example:
To find students whose names start with 'A':
SELECT *
FROM STUDENTS
WHERE STUDENT_NAME LIKE 'A%';
IN Operator
The IN operator is used to check if a value exists within a list of values:
Example:
To find students from Chandigarh or Mohali:
SELECT *
FROM STUDENTS
WHERE CITY IN ('Chandigarh', 'Mohali');
BETWEEN Operator
The BETWEEN operator is used to check if a value is within a specified range:
Example:
To find students whose STUDENT_ID is between 100 and 200:
SELECT *
FROM STUDENTS
WHERE STUDENT_ID BETWEEN 100 AND 200;
IS NULL and IS NOT NULL
To check for null values:
Example:
To find students who have not provided their phone number:
SELECT *
FROM STUDENTS
WHERE PHONE IS NULL;
By effectively using the WHERE clause and its various conditions, you can extract the specific data you need from your tables, making your SQL queries more powerful and efficient.
Comments