WHERE
A clause used to filter rows by a condition. You can specify various conditions, including comparison operators and range checks.
Syntax
-- Retrieves rows that match the condition. SELECT column FROM table WHERE condition; -- Retrieves rows within a range using BETWEEN. SELECT column FROM table WHERE column BETWEEN value1 AND value2; -- Retrieves rows that match any value in a list. SELECT column FROM table WHERE column IN (value1, value2, value3);
Operators
| Operator | Description |
|---|---|
| = | Retrieves rows where the value is equal. |
| <> or != | Retrieves rows where the value is not equal. |
| < | Retrieves rows where the value is less than the specified value. |
| > | Retrieves rows where the value is greater than the specified value. |
| <= | Retrieves rows where the value is less than or equal to the specified value. |
| >= | Retrieves rows where the value is greater than or equal to the specified value. |
| BETWEEN value1 AND value2 | Retrieves rows within the range from value1 to value2, inclusive. |
| IN (value1, value2, ...) | Retrieves rows that match any value in the list. |
| NOT IN (value1, value2, ...) | Retrieves rows that do not match any value in the list. |
Sample Code
-- Retrieves employees in the Sales department.
SELECT name, department FROM employees WHERE department = 'Sales';
-- Retrieves employees with a salary of 300,000 or more.
SELECT name, salary FROM employees WHERE salary >= 300000;
-- Retrieves employees with a salary between 250,000 and 350,000.
SELECT name, salary FROM employees WHERE salary BETWEEN 250000 AND 350000;
-- Retrieves employees in the Sales or Engineering department.
SELECT name, department FROM employees WHERE department IN ('Sales', 'Engineering');
-- Retrieves employees who are not in the General Affairs department.
SELECT name, department FROM employees WHERE department <> 'General Affairs';
Result
-- Example result of: SELECT name, salary FROM employees WHERE salary BETWEEN 250000 AND 350000; -- +----------+--------+ -- | name | salary | -- +----------+--------+ -- | Tanaka | 300000 | -- | Suzuki | 280000 | -- | Yamada | 320000 | -- +----------+--------+
Database-Specific Notes
The basic syntax of WHERE — including comparison operators, BETWEEN, and IN — works consistently across all major databases.
SELECT name, salary FROM employees WHERE salary >= 300000;
SELECT name, salary FROM employees WHERE salary BETWEEN 250000 AND 350000;
SELECT name, department FROM employees WHERE department IN ('Sales', 'Engineering');
For the not-equal operator, MySQL, PostgreSQL, and SQLite both support != and <>. For Oracle and SQL Server, the standard SQL <> is recommended.
-- Standard SQL (works in all databases) SELECT name FROM employees WHERE department <> 'General Affairs';
Notes
The WHERE clause can be used with SELECT, UPDATE, DELETE, and many other SQL statements. You can combine multiple conditions using AND and OR. See AND / OR / NOT for details.
BETWEEN performs a range search that includes both endpoints. salary BETWEEN 250000 AND 350000 is equivalent to salary >= 250000 AND salary <= 350000.
You cannot use = to check for NULL values. Use IS NULL instead. See IS NULL / IS NOT NULL for details.
If you find any errors or copyright issues, please contact us.
シェアする