SELECT WHERE Clause- SQL Server Syntax Example: WHERE – T-SQL Example
SELECT WHERE Clause- SQL Server Syntax Example: WHERE – T-SQL Example
Purpose: – Illustrates the SQL Server syntax for SELECT WHERE Clause.
SYNTAX:
SELECT [ ALL | DISTINCT ]
[ [TOP (expression) [PERCENT] [ WITH TIES ] ]
column_list
[ INTO new_table ]
[ FROM { table_source } [ ,…n ] ]
[ WHERE select_condition]
[ GROUP BY ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ]
PURPOSE:
WHERE specifies the search condition for selecting the records to be included in the result set.
Code Sample for SELECT WHERE Clause:
/* WHERE example from http://idealprogrammer.com PURPOSE: WHERE specifies the search condition for selecting the records to be included in the result set. SYNTAX: SELECT [ ALL | DISTINCT ] [TOP ( expression ) [PERCENT] [ WITH TIES ] ] column_list [ INTO new_table ] [ FROM { table_source } [ ,...n ] ] [ WHERE select_condition] [ GROUP BY ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] */ CREATE TABLE people( ID INT, firstname VARCHAR (20), lastname VARCHAR (20), statecode VARCHAR (2), alive BIT, height INT ) GO CREATE TABLE addressbook( ID INT, firstname VARCHAR (20), lastname VARCHAR (20), email VARCHAR (50)) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (1, 'Paul', 'Revere', 'AL', 0, 72) GO INSERT INTO people (ID, firstname, lastname, alive, height) -- statecode NULL VALUES (2, 'John', 'Lennon', 0, 69) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (3, 'Steve', 'Martin', 'NY', 1, 75) GO INSERT INTO people (ID, firstname, lastname, statecode, alive, height) VALUES (4, 'George', 'Washington', 'VA', 0, 75) GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (1, 'Paul', 'Revere', 'paul@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (2, 'Wade', 'Harvey', 'wade@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (3, 'Jack', 'Powers', 'jack@email.com') GO INSERT INTO addressbook (ID, firstname, lastname, email) VALUES (4, 'George', 'Washington', 'george@email.com') GO --Display entire table --SELECT 'Entire Table' AS Example,* FROM people -- 1. Example of WHERE with simple inequality SELECT 'Example #1' AS Example, firstname, lastname, height FROM people p WHERE height > 72 GO -- 2. Example of WHERE with multiple tables and multiple conditions SELECT 'Example #2' AS Example, p.firstname, p.lastname, a.email FROM people p, addressbook a WHERE p.firstname = a.firstname AND p.lastname = a.lastname GO -- 3. Example of WHERE with variables DECLARE @height AS int; SELECT @height=72; SELECT 'Example #3' as Example,* FROM people p WHERE height = @height GO -- 4. Example of WHERE with LIKE SELECT 'Example #4' AS Example, firstname, lastname, height FROM people p WHERE firstname LIKE 'S%' GO -- 5. Example of WHERE with IN SELECT 'Example #5' AS Example, firstname, lastname, height FROM people p WHERE height IN (72,75) GO -- 6. Example of WHERE with BETWEEN SELECT 'Example #6' AS Example, firstname, lastname, height FROM people p WHERE height BETWEEN 73 AND 76 GO -- 7. Example of WHERE with NULL SELECT 'Example #7' AS Example, firstname, lastname, height FROM people p WHERE statecode is NULL GO DROP TABLE people; GO DROP TABLE addressbook; GO |