WHERE clause in sql

Donald Green Donald Green
Feb 4, 2022 - 18:38
0 14
WHERE clause in sql

What is a WHERE clause?

We covered how to query data from a database using the SELECT statement in the previous tutorial. The SELECT statement returned all results from the requested database table.

These are, however, times when we want to restrict the results of a query to a specified condition. The SQL WHERE clause comes in handy in these situations. 

WHERE Sentence syntax

The basic syntax for the WHERE clause when used in a SELECT statement is as follows.

SELECT * FROM tableName WHERE condition;

HERE

  • "SELECT * FROM tableName" is a standard SELECT statement
  • "WHERE" is a keyword that limits our select query result set, and "condition" is a filter that will be applied to the results. The filter can be a range, a single value, or a subquery.

Let's now look at a practical example .

Suppose we want to get member's personal details from member table given member number 1, for this we will use the following script.

SELECT * FROM `members` WHERE `membership_number` = 1;

Running the above script in MySQL Workbench on "myflixdb" will produce the following results.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 janetjones@yagoo.cm

 

WHERE clause combined with operator - AND LOGICAL

The WHERE clause, when used in conjunction with the logical AND operator, is only executed if all of the specified filter criteria are met. 

Let's now look at a practical example - suppose we want to get a list of all category 2 movies that were released in 2008, we will use the script shown below to achieve this.
 
SELECT * FROM `movies` WHERE `category_id` = 2 AND `year_released` = 2008;
 

Running the above script in MySQL Workbench against "myflixdb" produces the following results.

movie_id title director year_released category_id
2 Forgetting Sarah Marshall Nicholas Stoller 2008 2

WHERE clause combined with OR OR LOGICAL operator

The WHERE clause, used in conjunction with the OR operator, is only executed if any or all of the specified filter criteria are met. 
The following script gets all movies in category 1 or 2
 
SELECT * FROM `movies` WHERE `category_id` = 1 OR `category_id` = 2;

Running the above script in MySQL Workbench against "myflixdb" produces the following results.

 
movie_id title director year_released category_id
one Pirates of the Caribbean 4 Rob Marshall 2011 one
2 Forgetting Sarah Marshall Nicholas Stoller 2008 2
 

WHERE clause combined with the IN keyword

 
The WHERE clause, when used in conjunction with the IN keyword, only affects rows whose values ​​match the list of values ​​provided in the IN keyword. IN helps reduce the number of OR clauses you might have to use 
The following query returns rows where member_number is 1, 2, or 3.
 
SELECT * FROM `members` WHERE `membership_number` IN (1,2,3);

Running the above script in MySQL Workbench against "myflixdb" produces the following results.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='&#109;ai&#108;&#116;o:j&#97;n&#101;tj&#111;n&#101;s&#64;y& #97;g&#111;&#111;&#46;cm'>j&#97;n&#101;tj&#111;n&#101;s&#64;y&# 97;g&#111;&#111;&#46;cm</a>
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='&#109;ai&#108;&#116;o:jj&#64;fstr&#101;&#101;t&#46;c&#111;m' >jj&#64;fstr&#101;&#101;t&#46;c&#111;m</a>
3 Robert Phil Male 12-07-1989 3rd Street 34 NULL 12345 <a href='&#109;ai&#108;&#116;o:rm&#64;tstr&#101;&#101;t&#46;c&#111;m' >rm&#64;tstr&#101;&#101;t&#46;c&#111;m</a>

 

WHERE clause combined with the NOT IN keyword

The WHERE clause, when used in conjunction with the NOT IN keyword, does NOT affect rows whose values ​​match the list of values ​​provided in the NOT IN keyword.
The following query returns rows where member_number is NOT 1, 2, or 3.
 
SELECT * FROM `members` WHERE `membership_number` NOT IN (1,2,3);

Running the above script in MySQL Workbench against "myflixdb" produces the following results.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL

 

WHERE clause combined with - COMPARATIVE OPERATORS

Comparison operators less than (), equal (=), not equal () can be used with the Where clause
 

= Equal

 
The following script gets all the female elements from the members table using the equals comparison operator.
 
SELECT * FROM `members` WHERE `gender` = 'Female';

Running the above script in MySQL Workbench against "myflixdb" produces the following results.

membership_number full_names gender date_of_birth physical_address postal_address contct_number email
one Janet Jones Female 21-07-1980 First Street Plot No 4 Private Bag 0759 253 542 <a href='&#109;ai&#108;&#116;o:j&#97;n&#101;tj&#111;n&#101;s&#64;y& #97;g&#111;&#111;&#46;cm'>j&#97;n&#101;tj&#111;n&#101;s&#64;y&# 97;g&#111;&#111;&#46;cm</a>
2 Janet Smith Jones Female 23-06-1980 Melrose 123 NULL NULL <a href='&#109;ai&#108;&#116;o:jj&#64;fstr&#101;&#101;t&#46;c&#111;m' >jj&#64;fstr&#101;&#101;t&#46;c&#111;m</a>
4 Gloria Williams Female 14-02-1984 2nd Street 23 NULL NULL NULL

 

> more than

The following script gets all payments greater than 2000 from the payments table.
SELECT * FROM `payments` WHERE `payment_amount` > 2000;
Running the above script in MySQL Workbench against "myflixdb" produces the following results.
 
payment_id membership_number payment_date description amount_paid external_reference_number
one one 23-07-2012 Movie rental payment 2500 eleven
3 3 30-07-2012 Movie rental payment 6000 NULL
 

<> Not Equal 

The following script gets all movies whose category id is not equal to 1.
SELECT * FROM `movies` WHERE `category_id`<> 1;
Running the above script in MySQL Workbench against "myflixdb" produces the following results.
 
movie_id title director year_released category_id
2 Forgetting Sarah Marshall Nicholas Stoller 2008 2
5 Daddy's Little Girls NULL 2007 eight
6 Angels and Demons NULL 2007 6
7 Davinci Code NULL 2007 6
9 honey mooners John Schultz 2005 eight
 

Summary

  • The SQL WHERE clause is used to limit the number of rows affected by a SELECT, UPDATE, or DELETE query.
  • The WHERE clause can be used along with logical operators like AND and OR, comparison operators like =, etc.
  • When used with the logical AND operator, all criteria must be met.
  • When used with the logical OR operator, either of the criteria must be met.
  • The IN keyword is used to select rows that match a list of values.

brain teaser

 
Let's say we want to get a list of rented movies that weren't returned on time on 06/25/2012. To do this, we can use the WHERE clause along with the "less than" comparison operator and the logical "AND" operator.
 
SELECT * FROM `movierentals` WHERE `return_date` < '2012-06-25' AND movie_returned = 0;
Running the above script in MySQL Workbench gives the following results.
 
reference_number transaction_date return_date membership_number movie_id movie_returned
14 21-06-2012 24-06-2012 2 2 0