SQL SERVER WHERE

SQL WHERE clause is used to specify a condition while fetching the data from a tables.

If the given condition is satisfied, then only it returns a specific value from the table.  

It is also used in the UPDATE, DELETE statement.

It can not be used with TRUNCATE Statement.

SYNTAX

SELECT column1, column2,…  FROM table_name

WHERE conditions..

conditions can be any logical ,comparison , like and wild card operators .

Lets look at an example of where clause in SQL Server.

First we create a sample table named as EMPLOYEE, then insert some records into table.

CREATE TABLE EMPLOYEE (EMPID INT NOT NULL, 
EMPNAME VARCHAR(50), 
EMPCODE VARCHAR(15), 
DOB DATE)

INSERT INTO EMPLOYEE(EMPID, EMPNAME, EMPCODE, DOB)
VALUES(
501, 'RAJESH MITAL', 'XCV567', '1976-11-11'),
(502, 'SUJOY SINGH', 'XCB787', '1970-10-19'),
(503, 'ABHISHEK MISHRA', 'FJV542', '1976-09-19'),
(504, 'HARSHIT KAPOOR', 'NMD557', '1980-12-10'),
(505, 'MANISH RAWAT', 'QJB987', '1985-02-15'),
(506, 'DEEPAK KUMAR', 'FZV704', '1986-11-05'),
(507, 'HARSH KUMAWAT', 'NET657', '1986-07-01'),
(508, 'ROZER JR', 'DDB987', '1987-08-02'),
(509, 'BALAJI NEGI', 'KL0542', '1980-09-03'),
(510, 'NARAYANA SWAMI', 'NSD447', '1980-10-10'),
(511, 'LOKESH MANTRI', 'BBB007', '1990-05-19'),
(512, 'SUMIT VIYAS', 'FFNM04', '1990-06-29'),
(513, 'BABA NAYAK', 'NEG157', '1989-07-13')

Using Where clause with conditional operator

To get employee whose EMPID is grater than 505.

Following statement uses WHERE clause to specify the condition and returns only those employee whose Empid is greater than 505.

SELECT EMPID, EMPNAME ,EMPCODE, DOB FROM EMPLOYEE 
WHERE EMPID >505

You can see, it returns only employee for EmpID is greater than 505.

Using Where clause with multiple conditions

To get employee whose EmpID’s is greater than 505 and EmpName contains a letter ‘KU’. 

SELECT EMPID, EMPNAME ,EMPCODE, DOB 
FROM EMPLOYEE 
WHERE EMPID >505 AND EMPNAME LIKE '%KU%'

Using Where clause with Date

To get the details of only those employees having date of birth year in between 1980 and 1990.

SELECT EMPID, EMPNAME ,EMPCODE, DOB FROM EMPLOYEE 
WHERE YEAR(DOB) BETWEEN 1980 and 1990

As you can see, it returns all the employee whose date of birth year is between 1980 and 1990.

Using WHERE clause with Update statement

Following statement uses where clause and update the EMPCODE value for EMPID = 507

UPDATE EMPLOYEE SET EMPCODE ='ABC879' 
WHERE EMPID =507

Lets check the record to see whether the value for EMPCODE is updated or not,  and  you can see it has been updated with new value.

SELECT * FROM EMPLOYEE  WHERE EMPID =507

 

Using WHERE clause to delete specific records  from table

Following statement uses WHERE clause to specify a condition in delete statement, and delete the record for EMPID = 514

DELETE FROM EMPLOYEE WHERE EMPID =514

Lets check for EMPID =514, in employee table, you can see there is no data aviable in table for Empid = 504. It means record for EmpID =514  has been deleted.

SELECT * FROM EMPLOYEE WHERE EMPID =514

WHERE condition can not be used with TRUNCATE TABLE Statement

Lets remove the records from table where EMPID is greater than 503 using TRUNCATE table statement.

TRUNCATE TABLE EMPLOYEE 
WHERE EMPID > 505

You can see, it gives an error saying ‘Incorrect syntax near the keyword where’. That means WHERE clause can not be used with Truncate Statement.

 

Also Read..

SQL Server Delete

SQL Server Update