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
1,382 total views, 1 views today