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 only those employee whose EMPID are greate than 505.

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

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

 

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

Using Where clause with multiple conditions

To get only those employee whose EmpID’s are 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 whose date of birth year is 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.

 

Using WHERE clause to delete sepcific records  from table

Following statement uses WHERE clause to specify a condition for delete statement, and deletes 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 in table for empid = 504.     It means record for EmpID =514  has been deleted.

SELECT * FROM EMPLOYEE WHERE EMPID =514

 

WHERE Can not be used with TRUNCATE TABLE Statement

Lets remove records of all employees for EMPID 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.

 

 

Recommended for you

SQL Server Delete

SQL Server Update

 1,382 total views,  1 views today