When querying a relational database, database programmers and directors will usually must filter the data returned or extract particular data that meet a given standards. On this database programming tutorial, we’ll have a look at the SQL WHERE clause, be taught its syntax, and preview a number of code examples exhibiting its use.
Learn: Use the SQL SELECT Assertion
SQL WHERE Clause
As said within the intro, the SQL WHERE clause is used when we have to filter the data returned from a desk to indicate a end result primarily based on a given situation or set of standards. The syntax for the WHERE clause is:
SELECT column1, column2, column3
FROM identify-of-desk
WHERE situation
Along with getting used alongside the SELECT assertion, the SQL WHERE clause can be utilized with UPDATE and DELETE statements as nicely.
SQL WHERE Clause Code Instance
For our first code instance, let’s assume that we’ve got a database with a desk named Superheroes that incorporates the HeroNames, HeroID, and SuperPowers of recognized tremendous heroes. As a pattern dataset, let’s say the next entries exist in our Superheroes desk:
HeroNames SuperPowers HeroID
AwesomePossum Play lifeless 110
Whatawoman Soar rope of doom 120
TheSpoiler Ruins film endings in a single certain 130
If we need to solely pull up the document for tremendous heroes whose identify is AwesomePossum, we’d write the observe SQL question, which reveals methods to use the WHERE clause:
SELECT * FROM Superheroes
WHERE HeroNames'AwesomePossum;'
Operating this question would end result within the following output:
AwesomePossum Play lifeless 110
The above SQL instance confirmed us methods to use the WHERE clause to question a situation primarily based on a textual content worth – therefore the inclusion of single citation marks in our question (don’t use double quotations!). If we need to question a numeric worth utilizing the WHERE clause, we’d omit the only citation marks, as proven within the following SQL instance:
SELECT * FROM Superheroes
WHERE HeroID=110;
Operating this question will give us the end result:
AwesomePossum Play lifeless 110
Learn:Â Use SQL SELECT DISTINCT Assertion
SQL WHERE Clause Operators
After all, utilizing solely the equals or = operator to find out standards in an SQL WHERE clause may be very limiting. Thankfully, there are a variety of different operators you need to use at the side of the WHERE clause to set question standards. SQL WHERE clause operators embrace:
- = Signifies values should equal x
- > Signifies values have to be larger than x
- < Signifies values have to be lower than x
- >= Signifies values have to be larger than or equal to x
- <= Signifies values have to be lower than or equal to x
- <> Signifies values should not be equal to x
- BETWEEN Signifies values have to be inside a spread
- IN Used to specifiy a couple of potential worth in a column
- LIKE Used for looking a given sample
SQL BETWEEN Operator Instance
To make use of the BETWEEN operator with a WHERE clause on our instance desk, we’d write the next SQL assertion:
SELECT * FROM Superheroes
WHERE HeroID BETWEEN 110 and 115;
Executing this SQL question would end result within the output:
AwesomePossum Play lifeless 110
SQL IN Operator Instance
Right here is an instance of methods to use the IN operator with a WHERE clause in SQL:
SELECT * FROM Superheroes
WHERE HeroNames ('AwesomePossum', 'Whatawoman');
Operating the above question on our pattern dataset would return the info for the AwesomePossum and Whatawoman data.
SQL LIKE Operator Instance
Right here is an instance exhibiting methods to use the LIKE operator with a WHERE clause in SQL:
SELECT * FROM Superheroes
WHERE HeroNames LIKE 'A%';
On this instance SQL question, we’re looking the Superheroes desk for any data the place HeroNames begin with A. The % image is used to say that any worth after A is okay. Utilizing our dataset pattern, the results of operating this question could be:
AwesomePossum Play lifeless 110
Had their been one other hero with a reputation that started with A, it, too, would have been returned.
