Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We use WHERE clause to filter when reading, updating, and deleting data from a table. If we run select, Update, or delete statements without using WHERE clause, we will be processing the entire table. Let’s do these operations with an example.
Example:
First, we create a table as below and add a few records to this table.
1 2 3 4 5 6 7 8 | USE [TestDB] GO CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTable] VALUES ('Nurullah ÇAKIR'),('Faruk ERDEM') |
Using WHERE Clause With SELECT:
We read the records with ID 1 as follows.
1 | SELECT * FROM [dbo].[MyTable] WHERE ID=1 |
Using WHERE Clause With UPDATE:
With the following query, we are updating the Name column of the records with ID 1 as “Nurullah”.
1 2 3 4 5 6 7 8 9 | USE [TestDB] GO SELECT * FROM [dbo].[MyTable] GO UPDATE [dbo].[MyTable] SET [Name] = 'Nurullah' WHERE ID=1 GO SELECT * FROM [dbo].[MyTable] |
Using WHERE Clause With DELETE:
We are deleting records with ID 1 from the table with the following query.
1 2 3 4 5 6 7 8 | USE [TestDB] GO SELECT * FROM [dbo].[MyTable] GO DELETE FROM [dbo].[MyTable] WHERE ID=1 GO SELECT * FROM [dbo].[MyTable] |
We can use the following operators in WHERE clause:
Comparison Opertors:
Where ID=1 | If ID is 1 |
Where ID>1 | If ID is greater than 1 |
Where ID>=1 | If ID is 1 and greater than 1 |
Where ID<1 | If ID is less than 1 |
Where ID<=1 | If ID is 1 and less than 1 |
Where ID<>1 | If ID is not 1 |
“AND” and “OR”:
Logical Operators:
We can use it as follows:
If the Name column has a Nuru expression and the ID is 1
or
If the name column has an ERDEM expression and ID is 2
1 | Select * FROM MyTable Where (ID=1 AND Name like '%Nuru%') OR (ID=2 AND Name like '%ERDEM%') |
BETWEEN:
If ID is between 0 and 2
1 | Select * FROM MyTable Where ID Between 0 and 2 |
LIKE:
If the name column contains a Nuru expression
1 | Select * FROM MyTable Where Name Like '%Nuru%' |
If ID is one of the values of 1,2,3,4
1 | Select * FROM MyTable Where ID IN(1,2,3,4) |
If all of the values returned from the subquery meets the condition.
1 | SELECT * from MyTable Where ID > ALL(Select ID FROM MyTable where ID>0) |
If a value is returned from the subquery.
1 | SELECT * from MyTable Where EXISTS(Select ID FROM MyTable where ID>0) |
We use NOT operator with other operators. For example, "Where Name NOT Like '%Nuru%'" means that if the name column doenst contains a Nuru expression.
SOME or ANY(They are equivalent):
If any of the values returned from the subquery meets the condition.
1 2 3 | SELECT * from MyTable Where ID > SOME(Select ID FROM MyTable where ID>0) GO SELECT * from MyTable Where ID > ANY(Select ID FROM MyTable where ID>0) |
