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