SQLite | Querying Data | Setting Query Conditions with WHERE
Without a condition, a SELECT statement returns every row in a table. A WHERE clause limits the result to rows that satisfy a condition.
Setting conditions with WHERE
The syntax is:
SELECT column_name, ... FROM table_name WHERE condition;
Create a table and add sample data:
create table user (name text, old integer, address text);
insert into user values ('devkuma', 39, 'Seoul');
insert into user values ('kimkc', 34, 'Busan');
insert into user values ('araikuma', 26, 'Seoul');
insert into user values ('happykuma', 19, 'Seoul');
insert into user values ('mykuma', 27, 'Daejeon');
insert into user values ('yourkuma', 28, 'Gwangju');
insert into user values ('raccoon', 31, 'Busan');
With no condition, all rows are returned:
select * from user;
To return only rows whose address is Seoul:
select * from user where address = 'Seoul';
devkuma|39|Seoul
araikuma|26|Seoul
happykuma|19|Seoul
The same query can select rows whose address is Busan:
select * from user where address = 'Busan';
kimkc|34|Busan
raccoon|31|Busan
Conditions with comparison operators
| Operator | Description |
|---|---|
a = b |
a equals b |
a <> b |
a does not equal b |
a > b |
a is greater than b |
a >= b |
a is greater than or equal to b |
a < b |
a is less than b |
a <= b |
a is less than or equal to b |
a == b |
a equals b |
a != b |
a does not equal b |
Return rows where old is at least 30:
select * from user where old >= 30;
devkuma|39|Seoul
kimkc|34|Busan
raccoon|31|Busan
Return rows whose address is not Busan:
select * from user where address <> 'Busan';
devkuma|39|Seoul
araikuma|26|Seoul
happykuma|19|Seoul
mykuma|27|Daejeon
yourkuma|28|Gwangju
Conditions with logical operators
Complex conditions can use AND, OR, and NOT:
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition
AND requires both conditions, OR requires at least one, and NOT returns rows that do not satisfy its condition. The following query returns users older than 20 who live in Seoul:
select * from user where old > 20 and address = 'Seoul';
devkuma|39|Seoul
araikuma|26|Seoul