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.`