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) |
