One place for hosting & domains

      Operators

      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

      RxJS Operators for Dummies: forkJoin, zip, combineLatest, withLatestFrom


      If you are confused about the differences between forkJoin, zip, combineLatest and withLatestFrom, you are not alone! 🙂

      These 4 operators are what we know as combination operators – we use them when we need to join information from multiple observables.

      Which operator should I use?

      That is what this article is for! We will talk about the usage and differences between these 4 operators in an easy to understand way, so you know which one to choose when the time comes.

      Setup

      Imagine you are printing t-shirts. Ms. Color holds the color information and Mr. Logo holds the logo information. Both of them will pick color and logo spontaneously. You will need to wait and combine these two information continuously in order to print t-shirts. Ms. Color and Mr. Logo represent two observables in our code – color$ and logo$.

      you, ms. color & mr. logo

      // 0. Import Rxjs operators
      import { forkJoin, zip, combineLatest, Subject } from 'rxjs';
      import { withLatestFrom, take, first } from 'rxjs/operators';
      
      // 1. Define shirt color and logo options
      type Color="white" | 'green' | 'red' | 'blue';
      type Logo = 'fish' | 'dog' | 'bird' | 'cow';
      
      // 2. Create the two persons - color and logo observables, 
      // They will communicate with us later (when we subscribe)
      const color$ = new Subject<Color>();
      const logo$ = new Subject<Logo>();
      
      // 3. We are ready to start printing shirt. Need to subscribe to color and logo observables to produce shirts, we will write code here later
      ...
      
      // 4. The two persons(observables) are doing their job, picking color and logo
      color$.next('white');
      logo$.next('fish');
      
      color$.next('green');
      logo$.next('dog');
      
      color$.next('red');
      logo$.next('bird');
      
      color$.next('blue');
      
      // 5. When the two persons(observables) has no more info, they said bye bye.. We will write code here later
      ...
      

      I guess the code above is pretty expressive itself. We created two observables by using Subject. For part 4 in the code, every .next(<value>) means Ms. Color or Mr. Logo is picking color or logo.

      Take note of the sequence of information (part 4 in our code), here is the summary:-

      sequence of info

      1. Ms. Color picks WHITE
      2. Mr. Logo picks FISH
      3. Ms. Color picks GREEN
      4. Mr. Logo picks DOG
      5. Ms. Color picks RED
      6. Mr. Logo picks BIRD
      7. Ms. Color picks BLUE
      

      Later, we will update our code (part 3 & 5) to subscribe to both color and logo observables using the 4 different operators to see how the shirts are produced differently.

      All set. Let’s start exploring our first operator!

      zip – the love birds operator

      I call zip operator the love birds operator. Love birds need to always be together. Remember Titanic, the “you jump, I jump” type.

      Let’s replace our code (part 3) with below:

      // 3. We are ready to start printing shirt...
      zip(color$, logo$)
          .subscribe(([color, logo]) => console.log(`${color} shirt with ${logo}`));
      

      TL;DR

      For those of you who are not familar with JavaScript ES6/ES2015 destructuring assignment, you might find the syntax in subscribe [color, logo] a little bit odd.

      When we zip color$ and logo$, we expect to receive an array of 2 items during subscribe, first item is color and second is logo (follow their orders in zip function).

      The traditional way of writing it would be .subscribe((data) => console.log(${data[0]} shirt with ${data[1]})). As you can see, it’s not very obvious that data[0] is color.

      ES6 allows us to unpack the value from arrays. Therefore, we unpack data into [color, logo] straight away. More readable right?

      Result

      Alright, let’s go back to our code and run it. The shirt printing result would be:-

      zip - printed shirts

      Here is what get to log in the console:

      1. white shirt with fish
      2. green shirt with dog
      3. red shirt with bird
      

      How does zip work?

      Again, zip operator is the love birds operator. In our case, color will wait for logo whenever there are new value (vice versa). Both values must change then only the log gets triggered.

      1. Ms. Color picks WHITE
      2. Mr. Logo picks FISH <- log 01, WHITE + FISH in pair, love birds!
      3. Ms. Color picks GREEN
      4. Mr. Logo picks DOG <- log 02, GREEN + DOG in pair, love birds!
      5. Ms. Color picks RED
      6. Mr. Logo picks BIRD <- log 03, RED + BIRD in pair love birds!
      7. Ms. Color picks BLUE <- waiting for love...
      

      zip operator can accept more than 2 observables – no matter how many observables, they must all wait for each other, no man left behind!

      combineLatest – the go dutch operator

      I call combineLatest operator the go dutch operator. They are independent and doesn’t wait for each other, they take care of themselves.

      Let’s replace the setup code part 3 with the below code:

      // 3. We are ready to start printing shirt...
      combineLatest(color$, logo$)
          .subscribe(([color, logo]) => console.log(`${color} shirt with ${logo}`));
      

      The shirt printing result would be:-

      combinedLatest - printed shirts

      Here is what get to log in the console:

      1. white shirt with fish
      2. green shirt with fish
      3. green shirt with dog
      4. red shirt with dog
      5. red shirt with bird
      6. blue shirt with bird
      

      How does combineLatest work?

      As mentioned, combineLatest is the go dutch operator – once they meet their mates one time, they will wait for no man. In our case, first function is triggered after both color and logo values change. There onwards, either color or logo value changed will trigger the log.

      1. Ms. Color picks WHITE 
      2. Mr. Logo picks FISH <- log 01, color + logo first meet, let's go dutch!
      3. Ms. Color picks GREEN <- log 02, GREEN + FISH
      4. Mr. Logo picks DOG <- log 03, DOG + GREEN
      5. Ms. Color picks RED <- log 04, RED + DOG
      6. Mr. Logo picks BIRD <- log 05 BIRD + RED 
      7. Ms. Color picks BLUE <- log 06 BLUE + BIRD
      

      withLatestFrom – the master slave operator

      I call withLatestFrom operator the master slave operator. At first, master must meet the slave. After that, the master will take the lead, giving command. The slave will just follow and has no voice. 🙁

      Let’s replace the setup code part 3 with the below code:

      // 3. We are ready to start printing shirt...
      color$.pipe(withLatestFrom(logo$))
          .subscribe(([color, logo]) => console.log(`${color} shirt with ${logo}`));
      

      The shirt printing result would be:-

      withLatestFrom - printed shirts

      Here is what get to log in the console:

      1. green shirt with fish
      2. red shirt with dog
      3. blue shirt with bird
      

      How does withLatestFrom work?

      Can you guess who is the master and who is the slave in our case?

      You guessed it! color is the master while logo is the slave. At first (only once), color(master) will look for logo(slave). Once the logo(slave) has responded, color(master) will take the lead. Log will get triggered whenever the next color(master) value is changed. The logo(slave) value changes will not trigger the console log.

      1. Ms. Color picks WHITE <- nothing happen, waiting for slave
      2. Mr. Logo picks FISH <- slave found, wait for the master's command
      3. Ms. Color picks GREEN <- log 01, master says GREEN! So, GREEN + FISH
      4. Mr. Logo picks DOG
      5. Ms. Color picks RED <- log 02, master says RED! So, RED + DOG
      6. Mr. Logo picks BIRD
      7. Ms. Color picks BLUE <- log 03 master says BLUE! So, BLUE + BIRD
      

      forkJoin – the final destination operator

      Definitely not the horror movie) kind of final destination! I call forkJoin operator the final destination operator because they are very serious, they only commit once all parties are very sure that they are completely true, final destination of each other.

      Let’s replace the setup code part 3 with the below code:

      // 3. We are ready to start printing shirt...
      forkJoin(color$, logo$)
          .subscribe(([color, logo]) => console.log(`${color} shirt with ${logo}`));
      

      The shirt printing result would be:-
      forkJoin - printed shirts

      You see it right, the result is NOTHING! There is no log in console.

      How does forkJoin work?

      forkJoin is the final destination operator! They are very serious to make sure each other are their final destination. In our code, both color and logo observables are not complete, we can keep pushing value by calling .next – that means they are not serious enough and thus they are not final destination of each other.

      So, how do we be serious?

      We need to complete both observables. Let’s replace our setup code part 5 with the below:

      // 5. When the two persons(observables) ...
      color$.complete();
      logo$.complete();
      

      Great! With the above code changes, Here is our shirt printing result:-

      forkJoin (complete) - printed shirts

      Here is what get to log in the console:

      1. blue shirt with bird
      

      Here is the sequence of when the log happens:-

      1. Ms. Color picks WHITE
      2. Mr. Logo picks FISH
      3. Ms. Color picks GREEN
      4. Mr. Logo picks DOG
      5. Ms. Color picks RED
      6. Mr. Logo picks BIRD
      7. Ms. Color picks BLUE
      8. Ms. Color completed <-- color is serious!
      9. Mr. Logo completed <--- log no 01, both logo & color are completed. Final destination!
      

      There is more than one way to complete observable. There are operators that allow you to auto complete observable when conditions met, for example take, takeUntil, first.

      Let’s say, you only want to make 1 shirt, you only need to know the first color and logo, In this case, you don’t care about the rest of the info that Ms. Color & Mr. Logo provide. You can make use of take or first operator to achieve auto complete observable once first color and logo emit.

      Let’s replace the setup code part 3 with the below code:

      // 3. We are ready to start printing shirt...
      const firstColor$ = color$.pipe(take(1));
      const firstLogo$ = logo$.pipe(first());
      
      forkJoin(firstColor$, firstLogo$)
          .subscribe(([color, logo]) => console.log(`${color} shirt with ${logo}`));
      

      You can remove all the code in part 5 as well, we don’t need the two lines .complete() (as previous code) because take and first will auto complete the observable when the condition met.

      With the above change, you should see a white shirt with fish!

      forkjoin (auto complete) - printed shirtst

      Summary

      Phew~ this is a pretty long article huh? Here is the summary of all results.
      one page answer

      Let’s wrap up! In summary, these 4 operators trigger the next action (subscribe function in our case) in slightly different conditions:

      • zip – the love birds, always work as a team, triggers only when all observables return new values
      • combineLatest – the go dutch, start trigger once all observables return new values, then wait for no man, trigger every time when either observable return new value.
      • withLatestFrom – the master slave, master first waits for slave, after that, action get triggered every time only when master return new value.
      • forkJoin – the final destination, trigger once when all observables have completed.

      Which operator should I use?

      So I guess you can answer “which operator should I use?” better now. As a general rule of thumb – choose the one that works for you. In some cases, the outcome of using different operators might be the same (that’s why people get confused on which one to use), it would be good to understand the intention of the operator & decide accordingly.

      One of the most common use case of combination operators would be calling a few apis, wait for all results return, then executing next logic. Either forkJoin or zip will work and return same result because api calls are one-time only, auto-completed once result is returned (e.g. Angular httpClient.get).

      However, by understanding the operators more, forkJoin might be more suitable in this case. It is because we “seriously” want to wait for all http responses to complete before proceed to the next step. zip is intended for observables with multiple emits. In our case, we expect only one emit for each http request. Therefore, I think forkJoin is more appropriate (oh well, either way, your code will run just fine & return the same result, but it’s good to know right?).

      Demo

      Alright, here is the final code. Please note that the code is a little bit different from demo because I include the code to draw the UI. Logic and general code structure stay the same though.

      See the Demo on Stackblitz

      Quiz!

      As a bonus, let me give you a quiz!

      quiz

      Figure out the correct results with the provided code. Feel free to play around with it, and explore different scenarios (add more more observables maybe?)!

      Quiz Answer: Click here for the quiz’s answer! (please try yourself first)

      That’s all. Happy coding!



      Source link