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:

 T-SQL | 
  |
?
 
 
/* 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
 
 
 
 

Sample Output for SELECT WHERE Clause Example