SQL Where — Getting At Your Dreams

Relational databases store information in tables — with columns that are analogous to elements in a data structure and rows which are one instance of that data structure — the order of which is undetermined; in no way guaranteed to reflect the order in which the data was inserted into the table.

The SQL Where clause restricts actions to those rows which satisfy a condition. The general form of SQL Where is:

SELECT column1, column2, … 
FROM table 
WHERE predicate;

Because SQL creates, reads, updates, and deletes (CRUD) database information, the WHERE predicate is used to corral its actions in SELECT, UPDATE, and DELETE statements.

Tuber Melanosporum in the Pantry

It’s a treat when I can cook with tuber melanosporum, but acquiring the Périgord (or French black truffle) can be a challenge. Luckily I have a database table prepared with truffle suppliers worldwide, and with use of the SQL Where predicate I can select from whom I will purchase. (The SQL source code to create and populate the table is included at the end of this blog entry.)

Let’s begin by selecting the entire contents of the table:

SELECT * FROM truffles ;

Which returns the following; the last column, km, is the distance in kilometers from San Francisco to each truffle supplier.

The Most Basic SQL Where Use Case

Because shipping costs are not a trivial matter, I want to minimize the money I spend. To find truffle suppliers in my country, the USA, I use the SQL Where clause to limit the SELECT to just that country string.

SELECT *
FROM truffles
WHERE country = 'USA' ;

The equals sign is a “comparison operator” used to compare values. Following are the valid SQL comparison operators as well as Boolean “logic operators.” 

SQL Where Comparison Operators

Comparison operators evaluate two elements and return a true or false value, which can then acted upon For example, the question  “is the current value of the variable named x less than the quantity 3” is implemented by the SQL comparison

x < 3

The SQL comparison operators are:

For example, to find all the truffle stores in my table that are closer than 3500 kilometers, the SELECT statement is restricted by a “less than” distance check.

SELECT *
FROM truffles
WHERE km < 3500 ;

SQL Where Boolean Logic Operators

Logical operators evaluate the “truthiness” of elements and return a true or false value, which can then be acted upon. For example, the requirement for starting a car that “the brake pedal is depressed and the driver’s seat belt is latched” is implemented by the following code, the logical operator being the keyword AND.

car_can_start = (
    brake_pedal_down AND
    driver_side_seat_belt_connected ) ;

Following is a complete list of the comparison and logical operators available for use with the SQL Where clause. Thereafter, use of the operators in SQL is shown.

When there is more than one condition that must be met, use the AND logical operator.

SELECT *
FROM truffles
WHERE country = 'USA' AND place = 'New York, New York' ;

The OR logical operator returns true if any of the conditions are true

SELECT *
FROM truffles
WHERE country = 'Italy' OR country = 'Tasmania' ;

The NOT logical operator negates the condition in the clause.

SELECT *
FROM truffles
WHERE
NOT country = 'Italy' AND
NOT country = 'USA' AND
NOT country = 'England' ;

SQL Where In Comparison Operator

The IN comparison operator checks whether a set  of elements contains a specified value. The SQL code:

WHERE country NOT IN ( 'Italy', 'USA', 'England' ) ;

is syntactically the same as the longer:

WHERE
NOT country = 'Italy' AND
NOT country = 'USA' AND
NOT country = 'England' ;

The In Comparison Operator As Segue To Better Programming

Because we’re talking professional programming, it’s important to consider that the list of countries to exclude may be assembled elsewhere in the software universe, perhaps dynamically, perhaps based upon criteria which change frequently and which are opaque or even protected for sensitive business reasons. Code encapsulation and specialization are important to long-term maintenance, and it makes sense to have this code be a good citizen and gracefully accept the country list. Here’s how the list-generation agent might build the actual list:

CREATE TABLE some_countries ( country VARCHAR(15) );

INSERT INTO some_countries
VALUES
( 'Italy' ),
( 'USA' ),
( 'England' );

And here’s how the SQL Where clause uses the provided list:

SELECT *
FROM truffles
WHERE country NOT IN ( SELECT * FROM some_countries ) ;

Conclusion

We’ve covered using the SQL Where clause, showing how

• SQL Where  restricts the number of data rows a query retrieves, modifies, or deletes
• Comparison operators compare elements against numeric or textual values
• Logical operators compare elements against Boolean true and false states, and combine comparisons with AND, OR, and NOT
• The IN comparison works on sets of values — whether built locally or provided by another bit of code — which  improve code encapsulation, encouraging code re-use, and simplify tests

SQL Where and its operators are vital to building robust consumers of database values.

Start Learning

To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.

SQL Order By Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE truffles (
company VARCHAR(33),
streetVARCHAR(50),
placeVARCHAR(30),
countryVARCHAR(15),
kmDECIMAL
);


INSERT INTO truffles
VALUES
( 'Caspian Caviar Trading Ltd', 'Grindstone Mill / Alderley / Wotton-under-Edge', 'Gloucestershire', 'England', 8486 ),
( 'Earthy Delights', '2871 Jolly Road', 'Okemos, Michigan', 'USA', 3243 ),
( 'Harrods', '87-135 Brompton Road - Knightsbridge', 'London', 'England', 8484 ),
( 'Le Comptoir de la Gastronomie', '34 Rue Montmartre 75001', 'Paris', 'France', 8953 ),
( 'Longino & Cardenal', 'Via Ambrogio Moroni 8', 'Pogliano Milanese', 'Italy', 9570 ),
( 'Perigord Truffles of Tasmania', 'Tasman Road Grove', 'Rockdale', 'Tasmania', 12721 ),
( 'Sabatiano Tartufi', '135 Front Avenue', 'West Haven, Connecticut', 'USA', 4203 ),
( 'Urbani', '10 West End Ave', 'New York, New York', 'USA', 4129 );

CREATE TABLE some_countries ( country VARCHAR(15) );
INSERT INTO some_countries
VALUES
( 'Italy' ),
( 'USA' ),
( 'England' );