SQL WHERE Clause
SQL WHERE clause is basically use for fetching specific criteria matched data only return. SQL WHERE clause is optionally clause in DML statement.
SQL WHERE clause we can use with SELECT, UPDATE, DELETE statements. SELECT statement with WHERE clause execute, fetching all table rows and apply WHERE clause filtering and finally return the result data.
Notes: You can't use INSERT statement with WHERE clause. But you can use INSERT Statement with WHERE cause only when you get the filter data from another TABLE.
WHERE clause use with SELECT Statement
When you get table data with specific filtered data, you should use where clause, considering following syntax that help you to understanding WHERE clause.
Syntax
SELECT * FROM table_name WHERE condition;
Example
SQL> SELECT * FROM users_info WHERE name = 'Opal Kole';
NO NAME ADDRESS CONTACT_NO
--- ------------------- --------------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
WHERE clause use with UPDATE Statement
When you want to update specific table data you should use UPDATE statement with where clause. You can update/set more than one columns value.
Syntax
Considering following syntax that help you to understanding WHERE clause,
UPDATE table_name SET column_name=value1, ... WHERE condition;
Example
SQL> UPDATE users_info SET address = '145 Taxo court.' WHERE no = 10;
1 row updated.
WHERE clause use with DELETE Statement
When you want to delete specific table row(s) you should use DELETE statement with WHERE clause, Considering following syntax that help you to understanding WHERE clause.
Syntax
DELTE FROM table_name WHERE condition;
Example
SQL> DELETE FROM users_info WHERE no = 10;
1 row deleted.
WHERE clause use with INSERT Statement
When you want to insert filter data from another table, you should use INSERT statement with WHERE clause, Considering following syntax that help you to understanding WHERE clause,
Syntax
INSERT INTO table_name (column_name1, column_name2, ...)
SELECT column_name1, column_name2, ...
FROM another_table_name
WHERE condition;
Example
SQL> INSERT INTO userinfo (no, name, address)
SELECT no, name, address
FROM users_info
WHERE no = 1;
1 row inserted.
WHERE clause Operators
You can use WHERE clause with following operators.
| Operator |
Description |
Example |
| = |
Equal to |
SQL> SELECT * FROM users_info WHERE no = 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
5 Ken Myer 137 Clay Road 000-444-7528
|
| <> |
Not equal |
SQL> SELECT * FROM users_info WHERE no <> 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
2 Max Miller 41 NEW ROAD. 000-444-8292
3 Beccaa Moss 2500 green city. 000-444-7586
4 Paul Singh 1343 Prospect St 000-444-7585
6 Jack Evans 1365 Grove Way 000-444-8401
7 Reed Koch 1274 West Street 000-444-5228
8 Gabe Hee 1220 Dallas Drive 000-444-5028
9 Ben Mares 101 Candy Road 000-444-5928
10 Sariya Vargas 145 Taxo court. 000-444-5927
9 rows selected.
|
| > |
Greater than |
SQL> SELECT * FROM users_info WHERE no > 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
6 Jack Evans 1365 Grove Way 000-444-8401
7 Reed Koch 1274 West Street 000-444-5228
8 Gabe Hee 1220 Dallas Drive 000-444-5028
9 Ben Mares 101 Candy Road 000-444-5928
10 Sariya Vargas 145 Taxo court. 000-444-5927
|
| < |
Less than |
SQL> SELECT * FROM users_info WHERE no < 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
2 Max Miller 41 NEW ROAD. 000-444-8292
3 Beccaa Moss 2500 green city. 000-444-7586
4 Paul Singh 1343 Prospect St 000-444-7585
|
| >= |
Greater than or equal |
SQL> SELECT * FROM users_info WHERE no >= 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
5 Ken Myer 137 Clay Road 000-444-7528
6 Jack Evans 1365 Grove Way 000-444-8401
7 Reed Koch 1274 West Street 000-444-5228
8 Gabe Hee 1220 Dallas Drive 000-444-5028
9 Ben Mares 101 Candy Road 000-444-5928
10 Sariya Vargas 145 Taxo court. 000-444-5927
6 rows selected.
|
| <= |
Less than or equal |
SQL> SELECT * FROM users_info WHERE no <= 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
2 Max Miller 41 NEW ROAD. 000-444-8292
3 Beccaa Moss 2500 green city. 000-444-7586
4 Paul Singh 1343 Prospect St 000-444-7585
5 Ken Myer 137 Clay Road 000-444-7528
|
| BETWEEN |
Fetching within range data |
SQL> SELECT * FROM users_info WHERE no BETWEEN 1 AND 5;
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
2 Max Miller 41 NEW ROAD. 000-444-8292
3 Beccaa Moss 2500 green city. 000-444-7586
4 Paul Singh 1343 Prospect St 000-444-7585
5 Ken Myer 137 Clay Road 000-444-7528
|
| LIKE |
Search for a pattern |
SQL> SELECT * FROM users_info WHERE name LIKE 'Be%';
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
3 Beccaa Moss 2500 green city. 000-444-7586
9 Ben Mares 101 Candy Road 000-444-5928
2 rows selected.
|
| IN |
Allows only specified values |
SQL> SELECT * FROM users_info WHERE no IN (1,5,10);
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
1 Opal Kole 63 street Ct. 000-444-7847
5 Ken Myer 137 Clay Road 000-444-7528
10 Sariya Vargas 145 Taxo court. 000-444-5927
3 rows selected.
|
| NOT IN |
Not allows specified values |
SQL> SELECT * FROM users_info WHERE no NOT IN (1,3,5,7,9);
NO NAME ADDRESS CONTACT_NO
-- ----------------- ------------------- --------------
2 Max Miller 41 NEW ROAD. 000-444-8292
4 Paul Singh 1343 Prospect St 000-444-7585
6 Jack Evans 1365 Grove Way 000-444-8401
8 Gabe Hee 1220 Dallas Drive 000-444-5028
10 Sariya Vargas 145 Taxo court. 000-444-5927
5 rows selected.
|
