One place for hosting & domains

      Operators

      Understanding Comparison and Logical Operators in JavaScript


      Introduction

      The field of computer science has many foundations in mathematical logic. If you have a familiarity with logic, you know that it involves truth tables, Boolean algebra, and comparisons to determine equality or difference.

      The JavaScript programming language uses operators to evaluate statements that can aid in control flow within programming.

      In this tutorial, we’ll go over logical operators. These are commonly used with conditional statements, and the if, else, and else if keywords, as well as the ternary operator. If you are interested in learning more about conditional statements first, refer to How To Write Conditional Statements in JavaScript.

      Comparison Operators

      In JavaScript, there are a number of comparison operators that you can use to evaluate whether given values are different or equal, as well as if a value is greater than or less than another. Often, these operators are used with stored values in variables.

      Comparison operators all return a Boolean (logical) value of true or false.

      The table below summarizes the comparison operators available in JavaScript.

      Operator What it means
      == Equal to
      != Not equal to
      === Strictly equal to with no type conversion
      ! == Strictly unequal to with no type conversion
      > Greater than
      >= Greater than or equal to
      < Less than
      <= Less than or equal to

      Let’s go into each operator in detail.

      Equality

      The equality operator measures whether values on either side of the operator are equal.

      Let’s consider the following:

      let x = 3;
      
      x == 3;
      

      Because 3 is equivalent to 3, the output received will be the Boolean value of true.

      Output

      true

      If we instead test whether x is equal to another integer, we’ll receive output stating that the statement is validated to be false.

      let x = 3;
      
      x == 5;
      

      Output

      false

      With this equivalency expression, you can also test other data types such as strings and Booleans.

      We’ll use a string example below.

      let shark = 'sammy';
      
      shark == 'sammy';
      shark == 'taylor';
      

      Output

      true false

      In the first instance, the expression returned true because the strings were equivalent. In the second instance, of shark == 'taylor', the expression returned false because the strings were not equal.

      Worth noting, is that the == operator is not a strict equivalency, so you can mix numbers and strings that evaluate to being equivalent. Consider the following example.

      let x = 3;
      
      x == '3';
      

      Even though the first line uses a number data type, and the second line tests x against a string data type, both values equal 3, and the output you will receive indicates that the expression is true.

      Output

      true

      Because this operator is not strict about data type, it can support users entering strings instead of numbers, for example. There is no need to convert data types to test equivalency.

      There are many cases where you may use comparison operators like the == operator. You may want to test equivalency when grading a test, for example. That way you can validate whether a given answer is correct or not.

      let answer = 10;
      let response = prompt("What is 5 + 5?");
      
      if (answer == response) {
        console.log("You're correct!");
      }
      

      Here, if the student enters 10 in response to the question when prompted, they will receive the feedback that they are correct.

      There are many potential applications of comparison operators in JavaScript, and they will help you control the flow of your program.

      Now that you have a foundation with a few examples for ==, we’ll be a bit briefer going forward.

      Inequality

      The != operator tests inequality, to determine whether the values on either side of the operator are not equal.

      Let’s consider an example.

      let y = 8;
      
      y != 9;
      

      For this example, 8 does not equal 9, so the expression will be evaluated to be true:

      Output

      true

      For a statement of inequality to be considered false, the two values on either side would need to actually be equal, as in the following.

      let y = 8;
      
      y != 8
      

      Output

      false

      In this second example, the two values on either side of the operator are equal, so the expression is not true.

      Identity

      The === operator determines whether two values are both of equal value and of equal type. This is also known as a strict equality operator. This means you cannot mix number and string data types.

      Here’s an example:

      let z = 4;
      
      z === 4;
      
      z === '4'; 
      

      We’ll receive the following output.

      Output

      true false

      The example indicates that z is strictly equal to 4 (as it is assigned the numeric value of 4), but that it is not strictly equal to the string '4'.

      Because this operator is strict, you will need to keep in mind that you may need to convert user-entered data from one data type to another, for instance, when working with the identity operator. This may help you keep data types consistent throughout your program.

      Non Identity

      Like ===, the operator !== evaluates a strict inequality, which considers both the value and the type of the operands on either side of the operator.

      We’ll review the following examples.

      let a = 18;
      
      a !== 18;
      
      a !== '18';
      
      a !== 29;
      

      The output for the above will be as follows.

      Output

      false true true

      In this example, since a does strictly equal 18, the first expression evaluates to false as we are testing inequality. In the next two examples, a is determined to be unequal to the string '18' and the number 29, so those two expressions evaluate to true (since they are not equal).

      Greater than

      The greater than symbol in JavaScript may be familiar to you from math: >. This evaluates whether one value (on the left side of the expression) is greater than another value (on the right side of the expression).

      Like the == operator above, the greater than operator is not strict, and therefore will allow you to mix strings and numbers.

      Let’s consider the following examples.

      let f = 72;
      
      f > 80;
      
      f > '30';
      

      We’ll receive the following output:

      Output

      false true

      In the first instance, 72 is less than 80, so the first expression evaluates to false. In the second instance, 72 is in fact greater than '30', and the operator does not care that the number is a string, so the expression evaluates to true.

      Greater than or equal

      Similarly, the operator for greater than or equal to will evaluate whether one operand meets the threshold of the other. This operator is typed as >= a kind of compound between greater than (>) and the equal sign (=).

      Our examples:

      let g = 102;
      
      g >= 90;
      
      g >= 103;
      

      Output

      true false

      Because 102 is a larger number than 90, it is considered to be greater than or equal to 90. Because 102 is less than 103, it is false to state that 102 >= 103. If either 90 or 103 were a string data type, the expressions would also evaluate the same.

      Less than

      The less than operator appears as the mirror version of the greater than operator: <.

      Consider the following examples as a demonstration.

      let w = 1066;
      
      w < 476;
      
      w < 1945;
      

      Output

      false true

      Here, 1066 is greater than 476, so the expression evaluates to false. However, 1066 is less than 1945, so the second statement evaluates to true. Again, the 476 or 1945 values could also be strings.

      Less than or equal

      The opposite of greater than or equal, the less than or equal operator — <= — will evaluate whether the value on the left side of the operator is less than or equal to the value on the right side.

      Here are a few examples.

      let p = 2001;
      
      p <= 1968;
      
      p <= 2001;
      
      p <= 2020;
      

      Output

      false true true

      The first expression evaluates to false because 2001 is not less than or equal to 1968. In the second expression, because the variable and 2001 are equal values, the output is true. In the third expression, the output is also true because 2001 is less than 2020. Again, these values could also be represented as strings, as in '2001', and would evaluate in the same manner.

      Note: Be sure not to confuse the less than or equal operator (<=) with the arrow function (=>) in JavaScript. Learn more about arrow functions in our tutorial Understanding Arrow Functions in JavaScript.

      To understand how these comparison operators can work together in a program, refer to our grades.js example in our How To Write Conditional Statements in JavaScript tutorial.

      Logical Operators

      In JavaScript, there are three logical operators, which connect two or more programming statements to return a true (also called “truthy”) or false (“falsy”) value. These are most often used with Boolean (logical) types, but can be applied to values of any data type.

      These logical operators are summarized in the table below.

      Operator Syntax Description
      AND && Returns true if both operands are true
      OR || Returns true if either operand is true
      NOT ! Returns true if operand is false

      Let’s review each of these operators in more detail.

      AND

      The AND operator is represented by two ampersands — && — it will return true if the operands to the left and right evaluate to be true.

      For example, with AND we can check if something is both high quality and has a low price.

      // High quality and low price are true
      const highQuality = true;
      const lowPrice = true;
      
      (highQuality && lowPrice);
      

      Output

      true

      Since both variables evaluate to be true, the AND operation within the parentheses returns true. If either one of the variables were initialized as false, the && expression would evaluate to false.

      OR

      The OR operator is represented by two pipes — || — it will return true if one of the operands is true.

      In this example, we’ll check if something is either highQuality or lowPrice.

      // Only low price is true
      const highQuality = false;
      const lowPrice = true;
      
      (highQuality || lowPrice);
      

      Output

      true

      Since one of the two conditions (highQuality or lowPrice) was true, the whole operation returns true. This would only evaluate to false if both conditions were false.

      NOT

      The NOT operator is represented by an exclamation point — ! — it will return true if the operand is set to false, and vice versa.

      const highQuality = true;
      
      !(highQuality);
      

      Output

      false

      In the above statement, highQuality has the value of true. With the NOT operator, we are checking to see if hiqhQuality evaluates to false. If it were false, the output would return true, but since it is true, the output returns false.

      The NOT operator is a bit tricky to understand at first. The important part to remember is that NOT checks whether something evaluates to be false.

      Conclusion

      Logical operators are the building blocks of flow control in JavaScript programming. Using these operators effectively will help you develop programs that evaluate statements and move to the next stage based on whether a statement is true or false.

      To continue learning more about JavaScript, check out our How To Code in JavaScript series, and our JavaScript tag.



      Source link

      How To Use Comparison and IS NULL Operators in SQL


      Introduction

      In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. They do this by defining specific criteria that each row must meet for it to be impacted, known as a search condition. Search conditions are made up of one or more predicates, which are special expressions that evaluate to either “true,” “false,” or “unknown,” and operations only affect those rows for which every predicate in the WHERE clause evaluates to “true.”

      SQL allows users to write search conditions that include a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates and the operators they use: comparison operators and the IS NULL operator.

      Although this guide will exclusively use SELECT statements in its examples, the concepts explained here can be used in a number of SQL operations. In particular, WHERE clauses and their search conditions are critical components of UPDATE and DELETE operations.

      Prerequisites

      In order to follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      • A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
      • MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.
      • You’ll also need a database with some tables loaded with sample data which you can use to practice using the comparison and IS NULL operators. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, including PostgreSQL and SQLite, the exact syntax or output may differ if you test them on a system other than MySQL.

      Connecting to MySQL and Setting up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the database server prompt. If you’re using MySQL, do so by running the following command, making sure to replace sammy with the name of your MySQL user account:

      From the prompt, create a database named comparison_null_db:

      • CREATE DATABASE comparison_null_db;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the comparison_null_db database, run the following USE statement:

      Output

      Database changed

      After selecting comparison_null_db, create a table within it.

      To follow along with the examples used in this guide, imagine that you and a group of your friends all decide to become more physically active and take up running as exercise. To this end, your friends all set a personal goal for how many miles they want to run in the next month. You decide to track your friends’ mileage goals, as well as the number of miles they actually ran, in an SQL table that has the following three columns:

      • name: each of your friends’ names, expressed using the varchar data type with a maximum of 15 characters
      • goal: each friends’ goal for how many miles they hoped to run over the past month, expressed as an integer using the int data type
      • result: the number of miles each friend ultimately ran over the course of the month, again expressed as an int

      Run the following CREATE TABLE statement to create a table named running_goals that has these three columns:

      • CREATE TABLE running_goals (
      • name varchar(15),
      • goal int,
      • result int
      • );

      Output

      Query OK, 0 rows affected (0.012 sec)

      Then load the running_goals table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of your friends, their running goals, and their results:

      • INSERT INTO running_goals
      • VALUES
      • ('Michelle', 55, 48),
      • ('Jerry', 25, NULL),
      • ('Milton', 45, 52),
      • ('Bridget', 40, NULL),
      • ('Wanda', 30, 38),
      • ('Stewart', 35, NULL),
      • ('Leslie', 40, 44);

      Output

      Query OK, 7 rows affected (0.004 sec) Records: 7 Duplicates: 0 Warnings: 0

      Note that three of these rows’ result values are NULL. For the purposes of this example, assume that these friends just haven’t reported the number of miles they ran over the past month so their result values were entered as NULL.

      With that, you’re ready to follow the rest of the guide and begin learning how to use the comparison and IS NULL operators in SQL.

      Understanding WHERE Clause Predicates

      In any SQL operation that reads data from an existing table, you can follow the FROM clause with a WHERE clause to limit what data the operation will affect. WHERE clauses do this by defining a search condition; any row that doesn’t meet the search condition is excluded from the operation, but any row that does is included.

      A search condition is made up of one or more predicates, or expressions that can evaluate one or more value expressions and return a result of either “true,” “false,” or “unknown.” In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value, like a string or numeric value, a mathematical expression, or a column name. Note that it’s almost always the case that at least one value expression in a WHERE clause predicate is the name of a column in the table referenced in the operation’s FROM clause.

      When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition to every row in the logical table defined by the FROM clause. It will then return only the rows for which every predicate in the search condition evaluates to “true.”

      The SQL standard defines 18 types of predicates, although not every RDBMS includes each of them in its implementation of SQL. Here are five of the most commonly used predicate types, as well as a brief explanation of each one and the operators they use:

      Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

      • =: tests whether the two values are equivalent
      • <>: tests whether two values are not equivalent
      • <: tests whether the first value is less than the second
      • >: tests whether the first value is greater than the second
      • <=: tests whether the first value is less than or equal to the second
      • >=: tests whether the first value is greater than or equal to the second

      Null: Predicates that use the IS NULL operator test whether values in a given column are Null
      Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others
      Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set
      Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern

      As mentioned in the introduction, this guide focuses on how to use SQL’s comparison and IS NULL operators to filter data. If you’d like to learn how to use the BETWEEN or IN operators with range and membership predicates, respectively, we encourage you to check out this guide on How To Use the BETWEEN and IN Operators in SQL. Alternatively, if you’d like to learn how to use the LIKE operator to filter data based on a string pattern containing wildcard characters, follow our guide on How To Use Wildcards in SQL. Lastly, if you’d like to learn more about WHERE clauses generally, you may be interested in our tutorial on How To Use WHERE Clauses in SQL.

      Comparison Predicates

      WHERE clause comparison predicates use one of six comparison operators to compare one value expression with another. They typically follow a syntax like this:

      • SELECT column_list
      • FROM table_name
      • WHERE column_name OPERATOR value_expression;

      Following the WHERE keyword is a value expression which, in most SQL operations, is the name of a column. Providing a column name as a value expression in a search condition tells the RDBMS to use each row’s value from that column as the value expression for that row’s iteration of the search condition. Because the database system applies search conditions to each row in sequence, the comparison operator will then include or filter out a row based on whether the search condition is true for its value from the specified column.

      To illustrate, run the following query which will return values from the running_goals table’s name and goal columns. Note how the WHERE clause uses a comparison predicate that will cause the query to only return rows whose goal value is equal to 40:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal = 40;

      Only two of your friends’ goals were to run exactly 40 miles over the past month, so the query returns just those two rows:

      Output

      +---------+------+ | name | goal | +---------+------+ | Bridget | 40 | | Leslie | 40 | +---------+------+ 2 rows in set (0.00 sec)

      To illustrate how the other comparison operators work, run the following queries which are identical to the previous example except that each uses a different comparison operator.

      The <> operator tests whether two values are not equivalent, so this query returns every row whose goal value is not equal to 40:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal <> 40;

      Output

      +----------+------+ | name | goal | +----------+------+ | Michelle | 55 | | Jerry | 25 | | Milton | 45 | | Wanda | 30 | | Stewart | 35 | +----------+------+ 5 rows in set (0.00 sec)

      The < operator tests whether the first value expression is less than the second:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal < 40;

      Output

      +---------+------+ | name | goal | +---------+------+ | Jerry | 25 | | Wanda | 30 | | Stewart | 35 | +---------+------+ 3 rows in set (0.00 sec)

      The > operator tests whether the first value expression is greater than the second:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal > 40;

      Output

      +----------+------+ | name | goal | +----------+------+ | Michelle | 55 | | Milton | 45 | +----------+------+ 2 rows in set (0.00 sec)

      The <= operator tests whether the first value is less than or equal to the second:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal <= 40;

      Output

      +---------+------+ | name | goal | +---------+------+ | Jerry | 25 | | Bridget | 40 | | Wanda | 30 | | Stewart | 35 | | Leslie | 40 | +---------+------+ 5 rows in set (0.00 sec)

      The >= operator tests whether the first value is greater than or equal to the second:

      • SELECT name, goal
      • FROM running_goals
      • WHERE goal >= 40;

      Output

      +----------+------+ | name | goal | +----------+------+ | Michelle | 55 | | Milton | 45 | | Bridget | 40 | | Leslie | 40 | +----------+------+ 4 rows in set (0.00 sec)

      The equivalence (=) and inequivalence (<>) operators work with string values as one might expect. The following query returns every row’s name value that’s equal to 'Leslie':

      • SELECT name
      • FROM running_goals
      • WHERE name="Leslie";

      Because there’s only one friend in the table named “Leslie,” the query just returns that row:

      Output

      +--------+ | name | +--------+ | Leslie | +--------+ 1 row in set (0.00 sec)

      When comparing string values, the <, >, <=, and >= operators all evaluate how the strings relate alphabetically. Put differently, if you write a predicate that tests whether one string is “less than” another, you’re testing whether the first string comes before the second alphabetically. Likewise, if your predicate tests whether one string is “greater than” another, you’re testing whether the first string comes after the second one alphabetically.

      To illustrate, run the following query. This will return the name and goal values of every row whose name value is “less than” the letter 'M'. In other words, the search condition will evaluate to “true” for every row whose name value comes before M alphabetically:

      • SELECT name
      • FROM running_goals
      • WHERE name < 'M';

      Output

      +---------+ | name | +---------+ | Jerry | | Bridget | | Leslie | +---------+ 3 rows in set (0.00 sec)

      Notice that this result set doesn’t include Michelle or Milton. This is because, alphabetically, the single letter “M” comes before any string that starts with the letter “M” and has more than one letter, so these two friends are excluded from this result set.

      Null Predicates

      In SQL, NULL is a reserved keyword used to represent missing or unknown values. Null is a state, rather than an actual value; it does not represent zero or an empty string.

      You can use the IS NULL operator to test whether a given value expression is Null:

      • . . .
      • WHERE column_name IS NULL
      • . . .

      With this type of predicate, the database system will look at every row’s value from the specified column and evaluate whether or not each one is Null. If values in the column are indeed Null, the search condition will evaluate to “true” for those rows and they will be included in the result set.

      To illustrate, run the following query which returns the name and result columns:

      • SELECT name, result
      • FROM running_goals
      • WHERE result IS NULL;

      The search condition in this query’s WHERE clause tests whether each row’s result value is Null. If so, the predicate evaluates to “true” and the row is included in the result set:

      Output

      +---------+--------+ | name | result | +---------+--------+ | Jerry | NULL | | Bridget | NULL | | Stewart | NULL | +---------+--------+ 3 rows in set (0.00 sec)

      Because three of your friends haven’t yet reported the number of miles they ultimately ran over the past month, those values were recorded as NULL when you loaded the table with data. Consequently, the search condition in the query evaluates to “true” for these three rows, so they’re the only ones included in the result set.

      Conclusion

      By following this guide, you learned how to use SQL’s comparison and IS NULL operators in WHERE clauses to limit the rows that an operation will affect. While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the SQL standard. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link

      How To Use the BETWEEN and IN Operators in SQL


      Introduction

      In certain Structured Query Language (SQL) statements, WHERE clauses can be used to limit what rows the given operation will affect. They do this by defining specific criteria that each row must meet for it to be impacted, known as a search condition. Search conditions are made up of one or more predicates, or special expressions that evaluate to either “true,” “false,” or “unknown,” and operations only affect those rows for which every predicate in the WHERE clause evaluates to “true.”

      SQL allows users to retrieve granular result sets by providing a variety of different types of predicates, each of which use a specific operator to evaluate rows. This guide will outline two types of predicates: range predicates which use the BETWEEN operator, and set membership predicates which use the IN operator.

      Although this guide will exclusively use SELECT statements in its examples, the concepts explained here can be used in a number of SQL operations. In particular, WHERE clauses and their search conditions are critical components of UPDATE and DELETE operations.

      Prerequisites

      To follow this guide, you will need a computer running some type of relational database management system (RDBMS) that uses SQL. The instructions and examples in this guide were validated using the following environment:

      • A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW, as described in our initial server setup guide for Ubuntu 20.04.
      • MySQL installed and secured on the server, as outlined in How To Install MySQL on Ubuntu 20.04. This guide was verified with a newly-created user, as described in Step 3.
      • You’ll also need a database with some tables loaded with sample data which you can use to practice using different WHERE clause predicates. We encourage you to go through the following Connecting to MySQL and Setting up a Sample Database section for details on how to connect to a MySQL server and create the testing database used in examples throughout this guide.

      Note: Please note that many RDBMSs use their own unique implementations of SQL. Although the commands outlined in this tutorial will work on most RDBMSs, including PostgreSQL and SQLite, the exact syntax or output may differ if you test them on a system other than MySQL.

      Connecting to MySQL and Setting up a Sample Database

      If your SQL database system runs on a remote server, SSH into your server from your local machine:

      Then open up the database server prompt. If you’re using MySQL, do so by running the following command, making sure to replace sammy with the name of your MySQL user account:

      From the prompt, create a database named between_in_db:

      • CREATE DATABASE between_in_db;

      If the database was created successfully, you’ll receive output like this:

      Output

      Query OK, 1 row affected (0.01 sec)

      To select the between_in_db database, run the following USE statement:

      Output

      Database changed

      After selecting between_in_db, create a table within it.

      To follow along with the examples used in this guide, imagine that you manage a company’s sales team. This company only sells three products: widgets, doodads, and gizmos. You begin tracking the number of units of each product each member of your team has sold in an SQL database. You decide that this database will have one table with four columns:

      • name: the names of each member of your sales team, expressed using the varchar data type with a maximum of 20 characters
      • widgets: the total number of widgets each salesperson has sold, expressed with the int data type
      • doodads: the number of doodads each salesperson has sold, also expressed as an int
      • gizmos: the number of gizmos each salesperson has sold, again expressed as an int

      Run the following CREATE TABLE statement to create a table named sales that has these four columns:

      • CREATE TABLE sales (
      • name varchar(20),
      • widgets int,
      • doodads int,
      • gizmos int
      • );

      Output

      Query OK, 0 rows affected (0.01 sec)

      Then load the sales table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing the team’s salespeople and the number of each product that they’ve sold:

      • INSERT INTO sales
      • VALUES
      • ('Tyler', 12, 22, 18),
      • ('Blair', 19, 8, 13),
      • ('Lynn', 7, 29, 3),
      • ('Boris', 16, 16, 15),
      • ('Lisa', 17, 2, 31),
      • ('Maya', 5, 9, 7),
      • ('Henry', 14, 2, 0);

      With that, you’re ready to follow the rest of the guide and begin learning how to use the BETWEEN and IN operators to filter data.

      Understanding WHERE Clause Predicates

      In any SQL operation that reads data from an existing table, you can follow the FROM clause with a WHERE clause to limit what data the operation will affect. WHERE clauses do this by defining a search condition; any row that doesn’t meet the search condition is excluded from the operation, but any row that does is included.

      A search condition is made up of one or more predicates, or expressions that can evaluate one or more value expressions and return a result of either “true,” “false,” or “unknown.” In SQL, a value expression — also sometimes referred to as a scalar expression — is any expression that will return a single value. A value expression can be a literal value, like a string or numeric value, a mathematical expression, or a column name. Note that it’s almost always the case that at least one value expression in a WHERE clause predicate is the name of a column in the table referenced in the operation’s FROM clause.

      When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition to every row in the logical table defined by the FROM clause. It will then return only the rows for which every predicate in the search condition evaluates to “true.”

      The SQL standard defines 18 types of predicates, although not every RDBMS includes each of them in its implementation of SQL. Here are five of the most commonly used predicate types, as well as a brief explanation of each one and the operators they use:

      Comparison: Comparison predicates compare one value expression with another; in queries, it’s almost always the case that at least one of these value expressions is the name of a column. The six comparison operators are:

      • =: tests whether the two values are equivalent
      • <>: tests whether two values are not equivalent
      • <: tests whether the first value is less than the second
      • >: tests whether the first value is greater than the second
      • <=: tests whether the first value is less than or equal to the second
      • >=: tests whether the first value is greater than or equal to the second

      Null: Predicates that use the IS NULL operator test whether values in a given column are Null
      Range: Range predicates use the BETWEEN operator to test whether one value expression falls between two others
      Membership: This type of predicate uses the IN operator to test whether a value is a member of a given set
      Pattern Match: Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern

      As mentioned in the introduction, this guide focuses on outlining how to use SQL’s BETWEEN and IN operators to filter data. If you’d like to learn how to use the comparison or IS NULL operators, we encourage you to check out this guide on How To Use Comparison and IS NULL Operators in SQL. Alternatively, if you’d like to learn how to use the LIKE operator to filter data based on a string pattern containing wildcard characters, follow our guide on How To Use Wildcards in SQL. Lastly, if you’d like to learn more about WHERE clauses generally, you may be interested in our tutorial on How To Use WHERE Clauses in SQL.

      Range Predicates

      Range predicates use the BETWEEN operator to test whether one value expression falls between two other value expressions. A WHERE clause that includes a range predicate in its search condition will follow this general syntax:

      • SELECT column_list
      • FROM table_name
      • WHERE column_name BETWEEN value_expression1 AND value_expression2;

      Following the WHERE keyword is a value expression which, in most SQL operations, is the name of a column. Because the database system applies search conditions to each row in sequence, providing a column name as a value expression in a search condition tells the RDBMS to use each row’s value from that column as the value expression for that row’s iteration of the search condition.

      After the column name comes the BETWEEN operator and two more value expressions separated by AND. The search condition will resolve to “true” for any rows whose value from the specified column is greater than or equal to the first of the two values separated by AND, but less than or equal to the second.

      To illustrate how range predicates work, run the following query. This will return the name and widgets columns of any rows whose widgets value is between 14 and 19, inclusive:

      • SELECT name, widgets
      • FROM sales
      • WHERE widgets BETWEEN 14 AND 19;

      Output

      +-------+---------+ | name | widgets | +-------+---------+ | Blair | 19 | | Boris | 16 | | Lisa | 17 | | Henry | 14 | +-------+---------+ 4 rows in set (0.00 sec)

      Keep in mind that the range you define after the BETWEEN operator can consist of any pair of value expressions, including column names.

      The following query returns every column from the sales table. Rather than listing out every column to return, it instead follows the SELECT keyword with an asterisk (*); this is SQL shorthand for “every column.” This query’s WHERE clause limits it to returning only rows whose gizmos value is greater than its doodads value but less than its widgets value:

      • SELECT *
      • FROM sales
      • WHERE gizmos BETWEEN doodads AND widgets;

      Only one member of the sales team has a gizmos value that falls between their widgets and doodads values, so only that row appears in the result set:

      Output

      +-------+---------+---------+--------+ | name | widgets | doodads | gizmos | +-------+---------+---------+--------+ | Blair | 19 | 8 | 13 | +-------+---------+---------+--------+ 1 row in set (0.00 sec)

      Be aware of the order in which you list the value expressions that define the range: the first value after the BETWEEN operator is always the lower end of the range and the second is always the upper end. The following query is identical to the previous one, except that it flips the order of the columns defining each end of the range:

      • SELECT *
      • FROM sales
      • WHERE gizmos BETWEEN widgets AND doodads;

      This time, the query returns the two rows where the gizmos value is greater than or equal to the row’s widgets value but less than or equal its doodads value. As this output indicates, changing the order like this will return a completely different result set:

      Output

      +-------+---------+---------+--------+ | name | widgets | doodads | gizmos | +-------+---------+---------+--------+ | Tyler | 12 | 22 | 18 | | Maya | 5 | 9 | 7 | +-------+---------+---------+--------+ 2 rows in set (0.00 sec)

      Like the <, >, <=, and >= comparison operators, when used to evaluate a column holding string values the BETWEEN operator will determine whether those values fall between two string values alphabetically.

      To illustrate, run the following query which returns the name values from any row in the sales table whose name value is between the letters A and M, alphabetically.

      This example uses two string literals as the value expressions that make up either end of the range. Note that these literal values must be wrapped in single or double quotes; otherwise, the DBMS will look for columns named A and M and the query will fail:

      • SELECT name
      • FROM sales
      • WHERE name BETWEEN 'A' AND 'M';

      Output

      +-------+ | name | +-------+ | Blair | | Lynn | | Boris | | Lisa | | Henry | +-------+ 5 rows in set (0.00 sec)

      Notice that this result set doesn’t include Maya even though the range provided in the search condition is from A to M. This is because, alphabetically, the letter “M” comes before any string that starts with the letter “M” and has more than one letter, so Maya is excluded from this result set along with any other salespeople whose names do not lie within the given range.

      Membership Predicates

      Membership predicates allow you to filter query results based on whether a value is a member of a specified set of data. In WHERE clauses, they generally follow this syntax:

      • . . .
      • WHERE column_name IN (set_of_data)
      • . . .

      After the WHERE keyword comes a value expression; again, this first value expression is usually the name of a column. Following that is the IN operator, itself followed by a set of data. You can explicitly define this set by listing any number of valid value expressions separated by commas, including literals or column names, or mathematical expressions involving either of these.

      To illustrate, run the following query. This will return the name and gizmos columns for every row whose gizmos value is a member of the set defined after the IN operator:

      • SELECT name, doodads
      • FROM sales
      • WHERE doodads IN (1, 2, 11, 12, 21, 22);

      Only three members of the sales team’s doodads values scores are equal to any of the values in this set, so only those rows get returned:

      Output

      +-------+---------+ | name | doodads | +-------+---------+ | Tyler | 22 | | Lisa | 2 | | Henry | 2 | +-------+---------+ 3 rows in set (0.00 sec)

      Instead of writing out each member of a set yourself, you can derive a set by following the IN operator with a subquery. A subquery — also known as a nested or inner query — is a SELECT statement embedded within one of the clauses of another SELECT statement. A subquery can retrieve information from any table in the same database as the table defined in the FROM clause of the “outer” operation.

      Note: When writing a subquery to define a set as part of a membership predicate, make sure that you use a scalar subquery, or a subquery that only returns a single column. Database management systems generally disallow subqueries that return multiple columns in a membership predicate, as it wouldn’t be clear to the database system which column it should evaluate as the set.

      As an example of using a subquery to define a set in a membership predicate, run the following statement to create a table named example_set_table that only has one column. This column will be named prime_numbers and will hold values of the int data type:

      • CREATE TABLE example_set_table (
      • prime_numbers int
      • );

      Then load this table with a couple rows of sample data. In keeping with the name of the table’s sole column, the following INSERT statement will load ten rows of data into the table, with each holding one of the first ten prime numbers:

      • INSERT INTO example_set_table
      • VALUES
      • (2),
      • (3),
      • (5),
      • (7),
      • (11),
      • (13),
      • (17),
      • (19),
      • (23),
      • (29);

      Then run the following query. This returns values from the name and widgets columns from the sales table, and its WHERE clause tests whether each value in the widgets column is in the set derived by the subquery SELECT prime_numbers FROM example_set_table:

      • SELECT name, widgets
      • FROM sales
      • WHERE widgets IN (SELECT prime_numbers FROM example_set_table);

      Output

      +-------+---------+ | name | widgets | +-------+---------+ | Blair | 19 | | Lynn | 7 | | Lisa | 17 | | Maya | 5 | +-------+---------+ 4 rows in set (0.00 sec)

      Because only four salespeople have sold a number of widgets equal to any of the prime numbers stored in the example_set_table, this query only returns those four rows.

      Conclusion

      By following this guide, you learned how to use SQL’s BETWEEN operator to test whether values in a column fall within a given range. You also learned how to use the IN operator to test whether values in a column are members of a set.

      While the commands shown here should work on most relational databases, be aware that every SQL database uses its own unique implementation of the language. You should consult your DBMS’s official documentation for a more complete description of each command and their full sets of options.

      If you’d like to learn more about working with SQL, we encourage you to check out the other tutorials in this series on How To Use SQL.



      Source link