WHERE

Syntax:: DELETE [fieldlist | *] FROM table WHERE selectcriteria
SELECT fieldlist | * FROM table WHERE selectcriteria
UPDATE table SET newvalue WHERE selectcriteria
fieldlist: The list of fields that are to be retrieved from the table.
table: The name of the table from which the information is being retrieved.
criteria: The condition(s) that dictate which rows are to be retrieved.

The WHERE clause is an optional clause that can be used with the DELETE, SELECT, and UPDATE statements to specify a selection criteria. The syntax for the DELETE, SELECT, and UPDATE statements are very similar.

Examples

Code:


				SELECT * FROM MusicArtists;

Output:


				
					MusicianID FirstName LastName Instrument Style
					
					1 Elvis Presley Guitar Rock and Roll
					2 B. B. King Guitar Blues
					3 Charlie Daniels Fiddle Country
					4 Johnny Cash Guitar Country		
					5 Henry Erickson Violin Classical
					6 Jonny Lang Guitar Blues
					7 Jerry Jamison Flute Classical
					8 Bobby Lee Fiddle Classical
					 
				
				(8 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
1	Elvis	Presley	Guitar	Rock and Roll
2	B. B.	King	Guitar	Blues
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country
5	Henry	Erickson	Violin	Classical
6	Jonny	Lang	Guitar	Blues
7	Jerry	Jamison	Flute	Classical
8	Bobby	Lee	Fiddle	Classical

Explanation:

The WHERE clause is optional. This simple query will return all rows from the table 'MusicArtists'.

Language(s): MS SQL Server

Code:


				SELECT * FROM MusicArtists WHERE Style='Country';

Output:


				
					MusicianID FirstName LastName Instrument Style
					
					3 Charlie Daniels Fiddle Country
					4 Johnny Cash Guitar Country		
					 
					
				(2 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country

Explanation:

The addition of a WHERE to the previous example allows you to efficiently narrow the values that will be selected. In this example, we narrow the criteria to only 'Country'.

Language(s): MS SQL Server

Code:


				SELECT * FROM MusicArtists 

				WHERE Style='Country' AND Instrument='Guitar';

Output:


				
					MusicianID FirstName LastName Instrument Style
					
					4 Johnny Cash Guitar Country		
					 
				
				(1 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
4	Johnny	Cash	Guitar	Country

Explanation:

By using the AND logical operator we can further narrow the criteria to 'Country' artists who play the 'Guitar'.

Language(s): MS SQL Server

Code:


				SELECT * FROM MusicArtists 

				WHERE Style='Country' OR Style='Blues';

Output:


				
					MusicianID FirstName LastName Instrument Style
					
					2 B. B. King Guitar Blues
					3 Charlie Daniels Fiddle Country
					4 Johnny Cash Guitar Country		
					6 Jonny Lang Guitar Blues
					 
				
				(4 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
2	B. B.	King	Guitar	Blues
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country
6	Jonny	Lang	Guitar	Blues

Explanation:

This example demonstrates how to widen a search to include multiple music styles using the OR logical operator.

Language(s): MS SQL Server

Code:


				SELECT * FROM MusicArtists 

				WHERE Style='Country' OR Style='Blues' OR Style='Rock and Roll' 

				AND Instrument='Guitar' OR Instrument='Fiddle';

Output:


				
					MusicianID FirstName LastName Instrument Style
					
					1 Elvis Presley Guitar Rock and Roll
					2 B. B. King Guitar Blues
					3 Charlie Daniels Fiddle Country
					4 Johnny Cash Guitar Country		
					6 Jonny Lang Guitar Blues
					 
				
				(5 row(s) affected)

MusicianID	FirstName	LastName	Instrument	Style
1	Elvis	Presley	Guitar	Rock and Roll
2	B. B.	King	Guitar	Blues
3	Charlie	Daniels	Fiddle	Country
4	Johnny	Cash	Guitar	Country
6	Jonny	Lang	Guitar	Blues

Explanation:

This example uses multiple criteria for the WHERE clause using the AND and OR operators. Within a WHERE clause, you can link up to a total of forty (40) such expressions using logical operators.

Primary links

JetSQL » Clauses » WHERE

Examples

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

Code:

Output:

Explanation:

Language(s): MS SQL Server

See Also: