One place for hosting & domains

      How To SELECT Rows FROM Tables in SQL


      Introduction

      One of the most fundamental parts of working with databases is the practice of retrieving information about the data held within them. In relational database management systems, any operation used to retrieve information from a table is referred to as a query.

      In this guide, we will discuss the syntax of queries in Structured Query Language (SQL) as well as some of their more commonly used functions and operators.

      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:

      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, the exact syntax or output may differ if you test them on a system other than MySQL.

      • You’ll also need a database with some tables loaded with sample data which you can use to practice writing queries. 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.

      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 queries_db:

      • CREATE DATABASE queries_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 queries_db database, run the following USE statement:

      Output

      Database changed

      After selecting queries_db, create a few tables within it.

      To follow along with the examples used in this guide, imagine that you run a public parks cleanup initiative in New York City. The program is made up of volunteers who commit to cleaning up a city park near their homes by regularly picking up litter. Upon joining the initiative, these volunteers each set a goal of how many trash bags of litter they’d like to pick up each week. You decide to store information about the volunteers’ goals in an SQL database with a table that has five columns:

      • vol_id: each volunteer’s identification number, expressed with the int data type. This column will serve as the table’s primary key, meaning that each value will function as a unique identifier for its respective row. Because every value in a primary key must be unique, this column will also have a UNIQUE constraint applied to it
      • name: each volunteer’s name, expressed using the varchar data type with a maximum of 20 characters
      • park: the name of the park where each volunteer will pick up litter, expressed using the varchar data type with a maximum of 20 characters. Note that multiple volunteers can clean up litter in the same park
      • weekly_goal: each volunteer’s goal for how many bags of litter they’d like to pick up in a week, expressed with the int type
      • max_bags: each volunteer’s personal record for the most bags of litter they picked up in a single week, expressed as an int

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

      • CREATE TABLE volunteers (
      • vol_id int UNIQUE,
      • name varchar(20),
      • park varchar(30),
      • weekly_goal int,
      • max_bags int,
      • PRIMARY KEY (vol_id)
      • );

      Then load the volunteers table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of the program’s volunteers:

      • INSERT INTO volunteers
      • VALUES
      • (1, 'Gladys', 'Prospect Park', 3, 5),
      • (2, 'Catherine', 'Central Park', 2, 2),
      • (3, 'Georgeanna', 'Central Park', 2, 1),
      • (4, 'Wanda', 'Van Cortland Park', 1, 1),
      • (5, 'Ann', 'Prospect Park', 2, 7),
      • (6, 'Juanita', 'Riverside Park', 1, 4),
      • (7, 'Georgia', 'Prospect Park', 1, 3);

      With that, you’re ready to follow the rest of the guide and begin learning how to create queries in SQL.

      Required Query Components: the SELECT and FROM Clauses

      In SQL, a statement is any operation sent to the database system that will perform some sort of task, like creating a table, inserting or deleting data, or changing the structure of a column or table. A query is an SQL statement that retrieves information about data held in a database.

      On its own, a query will not change any existing data held in a table. It will only return the information about the data which the author of the query explicitly requests. The information returned by a given query is referred to as its result set. Result sets typically consist of one or more columns from a specified table, and each column returned in a result set can hold one or more rows of information.

      Here’s the general syntax of an SQL query:

      • SELECT columns_to_return
      • FROM table_to_query;

      SQL statements are made up of various clauses, which consist of certain keywords and the information that these keywords require. At a minimum, SQL queries only require you to include two clauses: the SELECT and FROM clauses.

      Note: In this example syntax, both clauses are written on their own line. However, any SQL statement can alternatively be written on a single line, like this:

      • SELECT columns_to_return FROM table_to_query;

      This guide will follow the common SQL style convention of separating statements onto multiple lines so each line contains only one clause. This aimed to make each example more readable and understandable, but be aware that as long as you don’t include any syntax errors you can write any query on a single line or on as many lines as you’d like.

      Every SQL query begins with a SELECT clause, leading some to refer to queries generally as SELECT statements. After the SELECT keyword comes a list of whatever columns you want returned in the result set. These columns are drawn from the table specified in the FROM clause.

      In SQL queries, the order of execution begins with the FROM clause. This can be confusing since the SELECT clause is written before the FROM clause, but keep in mind that the RDBMS must first know the full working data set to be queried before it starts retrieving information from it. It can be helpful to think of queries as SELECT-ing the specified columns FROM the specified table. Lastly, it’s important to note that every SQL statement must end with a semicolon (;).

      As an example, run the following query. This will retrieve the name column from the volunteers table:

      • SELECT name
      • FROM volunteers;

      Here’s this query’s result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Even though this operation looked at the entire volunteers table, it only returns the specified column, name.

      You can retrieve information from multiple columns by separating each one’s name with a comma, as in the following query. This will return the vol_id, name, and park columns from the volunteers table:

      • SELECT park, name, vol_id
      • FROM volunteers;

      Output

      +-------------------+------------+--------+ | park | name | vol_id | +-------------------+------------+--------+ | Prospect Park | Gladys | 1 | | Central Park | Catherine | 2 | | Central Park | Georgeanna | 3 | | Van Cortland Park | Wanda | 4 | | Prospect Park | Ann | 5 | | Riverside Park | Juanita | 6 | | Prospect Park | Georgia | 7 | +-------------------+------------+--------+ 7 rows in set (0.00 sec)

      Notice that this result set returns the park column first, followed by the name column and then vol_id. SQL databases will generally return columns in whatever order they’re listed in the SELECT clause.

      There may be times when you want to retrieve every column from a table. Rather than writing out the name of every column in your query, you can instead enter an asterisk (*). In SQL, this is shorthand for “every column.”

      The following query will return every column from the volunteers table:

      • SELECT *
      • FROM volunteers;

      Output

      +--------+------------+-------------------+-------------+----------+ | vol_id | name | park | weekly_goal | max_bags | +--------+------------+-------------------+-------------+----------+ | 1 | Gladys | Prospect Park | 3 | 5 | | 2 | Catherine | Central Park | 2 | 2 | | 3 | Georgeanna | Central Park | 2 | 1 | | 4 | Wanda | Van Cortland Park | 1 | 1 | | 5 | Ann | Prospect Park | 2 | 7 | | 6 | Juanita | Riverside Park | 1 | 4 | | 7 | Georgia | Prospect Park | 1 | 3 | +--------+------------+-------------------+-------------+----------+ 7 rows in set (0.00 sec)

      Notice how this result set’s columns are listed in the same order in which they were defined in the CREATE TABLE statement from the previous Connecting to MySQL and Setting up a Sample Database section. This is how most relational database systems will order columns in the result set when running a query that uses an asterisk in place of individual column names.

      Be aware that you can retrieve information from multiple tables in the same query with the JOIN keyword. We encourage you to follow our guide on How To Use Joins in SQL for details on how to do this.

      Removing Duplicate Values with DISTINCT

      By default, RDBMSs will return every value from a column returned by a query, including duplicate values.

      As an example, run the following query. This will return the values from the volunteers table’s park column:

      • SELECT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Central Park | | Van Cortland Park | | Prospect Park | | Riverside Park | | Prospect Park | +-------------------+ 7 rows in set (0.00 sec)

      Notice how this result set includes two duplicated values: Prospect Park and Central Park. This makes sense, since multiple volunteers can clean up litter in the same park. There may be times, though, when you only want to know what unique values are held in a column. You can issue queries that remove duplicate values by following SELECT with the DISTINCT keyword.

      The following query will return every unique value in the parks column, removing any duplicates. It’s identical to the previous query except that it includes the DISTINCT keyword:

      • SELECT DISTINCT park
      • FROM volunteers;

      Output

      +-------------------+ | park | +-------------------+ | Prospect Park | | Central Park | | Van Cortland Park | | Riverside Park | +-------------------+ 4 rows in set (0.00 sec)

      This query’s result set has three fewer rows than that of the previous one, since it removed one of the Central Park values and two of the Prospect Park values.

      Note that SQL treats every row of a result set as an individual record, and DISTINCT will only eliminate duplicates if multiple rows share identical values in each column

      To illustrate this, issue the following query that includes the DISTINCT keyword but returns both the name and park columns:

      • SELECT DISTINCT name, park
      • FROM volunteers;

      Output

      +------------+-------------------+ | name | park | +------------+-------------------+ | Gladys | Prospect Park | | Catherine | Central Park | | Georgeanna | Central Park | | Wanda | Van Cortland Park | | Ann | Prospect Park | | Juanita | Riverside Park | | Georgia | Prospect Park | +------------+-------------------+ 7 rows in set (0.00 sec)

      The duplicate values in the park column — three occurrences of Prospect Park and two of Central Park — appear in this result set, even though the query included the DISTINCT keyword. Although individual columns in a result set may contain duplicate values, an entire row must be an exact duplicate of another for it to be removed by DISTINCT. In this case, every value in the name column is unique so DISTINCT doesn’t remove any rows when that column is specified in the SELECT clause.

      Filtering Data with WHERE clauses

      There may be times when you want to retrieve more granular information from tables in your database. You can filter out certain rows by including a WHERE clause in your query after the FROM clause, as in:

      • SELECT columns_to_return
      • FROM table_to_query
      • WHERE search_condition;

      Following the WHERE keyword in this example syntax is a search condition, which is what actually determines which rows get filtered out from the result set. A search condition is a set of one or more predicates, or expressions that can evaluate one or more value expressions. 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.

      Predicates in a WHERE clause search condition can take many forms, but they typically follow this syntax:

      . . .
      WHERE value expression OPERATOR value_expression
      . . .
      

      After the WHERE keyword, you provide a value expression followed by one of several special SQL operators used to evaluate the column’s values against the value expression (or value expressions) that comes after the operator. There are several such operators available in SQL and this guide will briefly highlight a few of them later in this section, but for illustration purposes it will focus only on one of the most commonly used operators: the equals sign (=). This operator tests whether two value expressions are equivalent.

      Predicates always return a result of either “true,” “false,” or “unknown.” When running SQL queries that contain a WHERE clause, the DBMS will apply the search condition sequentially to every row in the table defined in the FROM clause. It will only return the rows for which every predicate in the search condition evaluates to “true.”

      To illustrate this idea, run the following SELECT statement. This query returns values from the volunteers table’s name column. Instead of evaluating values from one of the table’s columns, however, this WHERE clause tests whether two value expressions — (2 + 2) and 4 — are equivalent:

      • SELECT name
      • FROM volunteers
      • WHERE (2 + 2) = 4;

      Because (2 + 2) is always equal to 4, this search condition evaluates to “true” for every row in the table. Consequently, every row’s name value gets returned in the result set:

      Output

      +------------+ | name | +------------+ | Gladys | | Catherine | | Georgeanna | | Wanda | | Ann | | Juanita | | Georgia | +------------+ 7 rows in set (0.00 sec)

      Because this search condition always returns a result of “true,” it isn’t very useful. You may as well not include a WHERE clause at all, since SELECT name FROM volunteers; will produce the same result set.

      Rather than comparing two literal values like this, you’ll typically use a column name as one of the value expressions in a WHERE clause’s search condition. By doing so, you’re telling the database management system to use each row’s value from that column as a value expression for that row’s iteration of the search condition.

      The following query’s WHERE clause applies a more exclusive search condition to each row. It will return the name and max_bags values from any row whose max_bags value is equal to 4:

      • SELECT name, max_bags
      • FROM volunteers
      • WHERE max_bags = 4;

      Only one volunteer’s max_bags value is exactly equal to 4, so the query only returns that volunteer’s record:

      Output

      +---------+----------+ | name | max_bags | +---------+----------+ | Juanita | 4 | +---------+----------+ 1 row in set (0.00 sec)

      You can also evaluate character string values in search condition predicates. The following query returns the vol_id and name values of every row whose name value is equal to 'Wanda':

      • SELECT vol_id, name
      • FROM volunteers
      • WHERE name="Wanda";

      Because there’s only one volunteer named Wanda, the query only returns the information from that row:

      Output

      +--------+-------+ | vol_id | name | +--------+-------+ | 4 | Wanda | +--------+-------+ 1 row in set (0.00 sec)

      To reiterate, this section’s examples all use the same search condition operator — the equals sign — to filter data. However, there are a number of other types of operators that allow you to write a variety of predicates, offering a high level of control over what information your queries return.

      The SQL standard defines 18 different types of predicates, though not all of them are supported on every RDBMS. Here are five of the most commonly used search condition predicate types 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 containing wildcard values

      It’s beyond the scope of this tutorial to go into each of these predicate types in greater detail. If you’d like to learn more about them, though, we encourage you to check out the following guides:

      To learn more about WHERE clauses generally, please see our guide on How To Use WHERE Clauses in SQL.

      Sorting Query Results with ORDER BY

      Sometimes queries will return information in ways that may not be intuitive, or may not suit your particular needs. You can sort query results by appending an ORDER BY clause to the end of your query statement.

      Here’s the general syntax of a query with an ORDER BY clause:

      • SELECT columns_to_return
      • FROM table_to_query
      • ORDER BY column_name;

      To illustrate how this works, say you wanted to know which of your volunteers has the highest max_bags value. You could run the following query which returns the name and max_bags values from the volunteers table:

      • SELECT name, max_bags
      • FROM volunteers;

      However, this query sorts the result set in the order in which each row was added:

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Gladys | 5 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | | Ann | 7 | | Juanita | 4 | | Georgia | 3 | +------------+----------+ 7 rows in set (0.00 sec)

      For a relatively small data set like this, the order of a result set isn’t that important and you could just scan this result set’s max_bags values to find the highest one. However, this can quickly become tedious when working with larger amounts of data.

      Instead, you could run the same query but add an ORDER BY clause that sorts the result set based each row’s max_bags value:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Georgeanna | 1 | | Wanda | 1 | | Catherine | 2 | | Georgia | 3 | | Juanita | 4 | | Gladys | 5 | | Ann | 7 | +------------+----------+ 7 rows in set (0.00 sec)

      As this output indicates, the default behavior of SQL queries that include an ORDER BY clause is to sort the specified column’s values in ascending (increasing) order. You can change this behavior and sort them in descending order by appending the DESC keyword to the ORDER BY clause:

      • SELECT name, max_bags
      • FROM volunteers
      • ORDER BY max_bags DESC;

      Output

      +------------+----------+ | name | max_bags | +------------+----------+ | Ann | 7 | | Gladys | 5 | | Juanita | 4 | | Georgia | 3 | | Catherine | 2 | | Georgeanna | 1 | | Wanda | 1 | +------------+----------+ 7 rows in set (0.00 sec)

      Conclusion

      By reading this guide, you learned how to write basic queries, as well as filter and sort query result sets. 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

      How To Use WHERE Clauses in SQL


      Introduction

      In Structured Query Language (SQL) statements, WHERE clauses limit what rows the given operation will affect. They do this by defining specific criteria, referred to as search conditions, that each row must meet in order for it to be impacted by the operation.

      This guide will go over the general syntax used in WHERE clauses. It will also outline how to combine multiple search condition predicates in a single WHERE clause to filter data with more granularity, as well as how to use the NOT operator to exclude, rather than include, rows that meet a given search condition.

      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 fact, WHERE clauses 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 writing queries that include WHERE clauses. 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, 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 where_db:

      • CREATE DATABASE where_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 where_db database, run the following USE statement:

      Output

      Database changed

      After selecting where_db, create a table within it.

      To follow along with the examples used in this guide, imagine that you run a golf league at a local golf course. You decide to track information about the individual performances of the league’s players at the outings they attend. To do so, you decide to store the information in an SQL database.

      You decide that this table will need six columns:

      • name: each of the golfers’ names, expressed using the varchar data type with a maximum of 20 characters
      • rounds_played: the total number of complete rounds each golfer has played, expressed with the int data type
      • best: each golfer’s best, or lowest, score for an individual outing, also expressed as an int
      • worst: each golfer’s worst, or highest, score for an individual outing, again expressed as an int
      • average: an approximate average of each golfers’ scores over the rounds they’ve played. This column will hold values of the decimal type, limited to a maximum of 4 digits with one of those digits to the right of the decimal point
      • wins: the number of rounds in which each golfer had the lowest score of everyone in the playing group, expressed using the int type

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

      • CREATE TABLE golfers (
      • name varchar(20),
      • rounds_played int,
      • best int,
      • worst int,
      • average decimal (4,1),
      • wins int
      • );

      Then load the golfers table with some sample data. Run the following INSERT INTO operation to add seven rows of data representing seven of the league’s golfers:

      • INSERT INTO golfers
      • VALUES
      • ('George', 22, 68, 103, 84.6, 3),
      • ('Pat', 25, 65, 74, 68.7, 9),
      • ('Grady', 11, 78, 118, 97.6, 0),
      • ('Diane', 23, 70, 92, 78.8, 1),
      • ('Calvin', NULL, 63, 76, 68.5, 7),
      • ('Rose', NULL, 69, 84, 76.7, 4),
      • ('Raymond', 18, 67, 92, 81.3, 1);

      Notice that two of these rows’ rounds_played values are NULL. For the purposes of this tutorial, assume that these golfers haven’t reported how many rounds they’ve played and thus those values are recorded as NULL.

      You may also notice that each golfer’s best value is less than their worst. This is because, in common golf rules, a golfer’s score is determined by the number of strokes it takes for them to get their ball into each hole in the course, with the winner being the person with the fewest total strokes. Thus, unlike most other sports, a golfer’s best score will be lower than their worst.

      With that, you’re ready to follow the rest of the guide and begin learning how to use WHERE clauses in SQL.

      Filtering Data with WHERE clauses

      In SQL, a statement is any operation sent to the database system that will perform some sort of task, like creating a table, inserting or deleting data, or changing the structure of a column or table. SQL statements are made up of various clauses, which consist of certain keywords and the information that they require.

      As mentioned in the introduction, WHERE clauses allow you to filter out certain rows of data from being impacted by an SQL operation. In queries, the WHERE clause comes after the FROM clause, as in the following example:

      • SELECT columns_to_query
      • FROM table_to_query
      • WHERE search_condition;

      Following the WHERE keyword is a search condition. A search condition is a set 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.” Note that in cases where a search condition only contains a single predicate, the terms “search condition” and “predicate” are synonymous.

      Predicates in a WHERE clause search condition can take many forms, but they typically follow this syntax:

      . . .
      WHERE column_name OPERATOR value_expression
      . . .
      

      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. Most often, though, at least one of the value expressions in a WHERE clause’s search condition will be a column name.

      When running SQL queries that contain a WHERE clause, the database management system 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.”

      To illustrate this idea, run the following query. This will return every value from the golfers table’s name column:

      • SELECT name
      • FROM golfers
      • WHERE (2 + 2) = 4;

      This query includes a WHERE clause, but instead of specifying a column name it uses (2 + 2) as the first value expression and tests whether it’s equal to the second value expression, 4. Because (2 + 2) is always equal to 4, this search condition evaluates to “true” for every row. Consequently, every row gets returned in the result set:

      Output

      +---------+ | name | +---------+ | George | | Pat | | Grady | | Diane | | Calvin | | Rose | | Raymond | +---------+ 7 rows in set (0.01 sec)

      This WHERE clause isn’t very useful, since it always evaluates to “true” and always returns every row in the table. As mentioned previously, you will typically use at least one column name as a value expression in WHERE clause search conditions. When running queries, the database system will apply the search condition to each row individually in sequence. By providing a column name as a value expression in a search condition, you’re telling the DBMS to use each row’s value from that column as the value expression for that row’s iteration of the search condition.

      The following query’s WHERE clause applies a more exclusive search condition to each row than the previous example. It will return the name and wins values from any row whose wins column value is equal to 1:

      • SELECT name, wins
      • FROM golfers
      • WHERE wins = 1;

      Only two golfers have won exactly one round, so the query only returns those two rows:

      Output

      +---------+------+ | name | wins | +---------+------+ | Diane | 1 | | Raymond | 1 | +---------+------+ 2 rows in set (0.01 sec)

      The previous examples use the equals sign (=) to test whether two value expressions are equivalent, but the operator you use depends on what type of predicate you want to use to filter your result sets.

      The SQL standard defines 18 types of predicates, although not all of them are included in every SQL implementation. 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 use a comparison operator to compare one value (in queries, typically values in a specified column) with another. The six comparison operators are:

      • =: tests whether the two values are equal
      • SELECT name
      • FROM golfers
      • WHERE name="George";

      Output

      +--------+ | name | +--------+ | George | +--------+ 1 row in set (0.00 sec)
      • <>: tests whether two values are not equal
      • SELECT name, wins
      • FROM golfers
      • WHERE wins <> 1;

      Output

      +--------+------+ | name | wins | +--------+------+ | George | 3 | | Pat | 9 | | Grady | 0 | | Calvin | 7 | | Rose | 4 | +--------+------+ 5 rows in set (0.00 sec)
      • <: tests whether the first value is less than the second
      • SELECT name, wins
      • FROM golfers
      • WHERE wins < 1;

      Output

      +-------+------+ | name | wins | +-------+------+ | Grady | 0 | +-------+------+ 1 row in set (0.00 sec)
      • >: tests whether the first value is greater than the second
      • SELECT name, wins
      • FROM golfers
      • WHERE wins > 1;

      Output

      +--------+------+ | name | wins | +--------+------+ | George | 3 | | Pat | 9 | | Calvin | 7 | | Rose | 4 | +--------+------+ 4 rows in set (0.00 sec)
      • <=: tests whether the first value is less than or equal to the second
      • SELECT name, wins
      • FROM golfers
      • WHERE wins <= 1;

      Output

      +---------+------+ | name | wins | +---------+------+ | Grady | 0 | | Diane | 1 | | Raymond | 1 | +---------+------+ 3 rows in set (0.00 sec)
      • >=: tests whether the first value is greater than or equal to the second
      • SELECT name, wins
      • FROM golfers
      • WHERE wins >= 1;

      Output

      +---------+------+ | name | wins | +---------+------+ | George | 3 | | Pat | 9 | | Diane | 1 | | Calvin | 7 | | Rose | 4 | | Raymond | 1 | +---------+------+ 6 rows in set (0.00 sec)

      Null

      Predicates that use the IS NULL operator test whether values in a given column are Null. If so, the predicate evaluates to “true” and the row is included in the result set:

      • SELECT name, rounds_played
      • FROM golfers
      • WHERE rounds_played IS NULL;

      Output

      +--------+---------------+ | name | rounds_played | +--------+---------------+ | Calvin | NULL | | Rose | NULL | +--------+---------------+ 2 rows in set (0.00 sec)

      Range

      Range predicates use the BETWEEN operator to test whether the specified column values fall between two value expressions:

      • SELECT name, best
      • FROM golfers
      • WHERE best BETWEEN 67 AND 73;

      Output

      +---------+------+ | name | best | +---------+------+ | George | 68 | | Diane | 70 | | Rose | 69 | | Raymond | 67 | +---------+------+ 4 rows in set (0.00 sec)

      Membership

      Membership predicates use the IN operator to test whether a value is a member of a given set:

      • SELECT name, best
      • FROM golfers
      • WHERE best IN (65, 67, 69, 71);

      Output

      +---------+------+ | name | best | +---------+------+ | Pat | 65 | | Rose | 69 | | Raymond | 67 | +---------+------+ 3 rows in set (0.00 sec)

      Pattern Match

      Pattern matching predicates use the LIKE operator to test whether a value matches a string pattern that contains one or more wildcard characters, also known as wildcards. SQL defines two wildcards, % and _:

      • _: an underscore represents a single unknown character
      • SELECT name, rounds_played
      • FROM golfers
      • WHERE rounds_played LIKE '2_';

      Output

      +--------+---------------+ | name | rounds_played | +--------+---------------+ | George | 22 | | Pat | 25 | | Diane | 23 | +--------+---------------+ 3 rows in set (0.00 sec)
      • %: a percentage sign represents zero or more unknown characters
      • SELECT name, rounds_played
      • FROM golfers
      • WHERE name LIKE 'G%';

      Output

      +--------+---------------+ | name | rounds_played | +--------+---------------+ | George | 22 | | Grady | 11 | +--------+---------------+ 2 rows in set (0.00 sec)

      It’s beyond the scope of this tutorial to go into each of these predicate types in greater detail. If you’d like to learn more about them, though, we encourage you to check out the following guides:

      Combining Multiple Predicates with AND and OR

      There may be times when you need more granularly filtered results than a WHERE clause with a single search condition predicate can provide. On the other hand, there could also be times when rows that meet one of several search conditions would be acceptable in a result set. In cases like these, you can write WHERE clauses that include multiple predicates with the AND or OR operators, respectively.

      To get started with these operators, run the following query which returns values from the golfers table’s name, best, worst, and average columns. Its WHERE clause includes two predicates, separated by AND:

      • SELECT name, best, worst, average
      • FROM golfers
      • WHERE best < 70 AND worst < 96;

      The first predicate tests whether each row’s best value is less than 70, while the second tests whether each row’s worst value is less than 96. If either test evaluates to “false” for a row, that row will not get returned in the result set:

      Output

      +---------+------+-------+---------+ | name | best | worst | average | +---------+------+-------+---------+ | Pat | 65 | 74 | 68.7 | | Calvin | 63 | 76 | 68.5 | | Rose | 69 | 84 | 76.7 | | Raymond | 67 | 92 | 81.3 | +---------+------+-------+---------+ 4 rows in set (0.00 sec)

      Next, run the following query. This is identical to the previous example, except that it separates the two predicates with the OR operator instead of AND:

      • SELECT name, best, worst, average
      • FROM golfers
      • WHERE best < 70 OR worst < 96;

      Because only one of the predicates must evaluate to “true” for a row to be returned, this result set includes two more rows than the previous example:

      Output

      +---------+------+-------+---------+ | name | best | worst | average | +---------+------+-------+---------+ | George | 68 | 103 | 84.6 | | Pat | 65 | 74 | 68.7 | | Diane | 70 | 92 | 78.8 | | Calvin | 63 | 76 | 68.5 | | Rose | 69 | 84 | 76.7 | | Raymond | 67 | 92 | 81.3 | +---------+------+-------+---------+ 6 rows in set (0.00 sec)

      You can include as many predicates as you’d like in a single WHERE clause as long as you combine them with the correct syntax. As your search conditions get more complex, though, it can become difficult to predict what data they will filter.

      It’s important to note that database systems generally give precedence to AND operators. This means that any predicates separated by an AND operator (or operators in the case of more than two predicates) are treated as a single, isolated search condition that gets tested before any other predicates in a WHERE clause.

      To illustrate, run the following query, which returns values from the name, average, worst, and rounds_played columns for any row that meets the search conditions defined in the WHERE clause:

      • SELECT name, average, worst, rounds_played
      • FROM golfers
      • WHERE average < 85 OR worst < 95 AND rounds_played BETWEEN 19 AND 23;

      This query first tests whether the predicates separated by the AND operator — worst < 95 and rounds_played BETWEEN 19 AND 23 — both evaluate to “true” for the row in the current iteration. If so, then that row will appear in the result set. But if either evaluates to “false”, the query will then check whether the current row’s average value is less than 85. If so, the row will be returned:

      Output

      +---------+---------+-------+---------------+ | name | average | worst | rounds_played | +---------+---------+-------+---------------+ | George | 84.6 | 103 | 22 | | Pat | 68.7 | 74 | 25 | | Diane | 78.8 | 92 | 23 | | Calvin | 68.5 | 76 | NULL | | Rose | 76.7 | 84 | NULL | | Raymond | 81.3 | 92 | 18 | +---------+---------+-------+---------------+ 6 rows in set (0.00 sec)

      You can prioritize a set two or more predicates by wrapping them in parentheses. The following example is identical to the previous one, but it wraps the average < 85 and worst < 95 predicates, separated by an OR operator, in parentheses:

      • SELECT name, average, worst, rounds_played
      • FROM golfers
      • WHERE (average < 85 OR worst < 95) AND rounds_played BETWEEN 19 AND 23;

      Because the first two predicates are surrounded by parentheses, the subsequent AND operator treats them as a discrete search condition that must evaluate to “true”. If both of these predicates — average < 85 and worst < 95 — evaluate to “false”, then the entire search condition evaluates to “false” and the query immediately drops the row from the result set before moving on to evaluate the next one.

      However, if either of these first two predicates evaluate to “true”, the query then tests whether the given golfer’s rounds_played value is between 19 and 23. If so, that row gets returned in the result set:

      Output

      +--------+---------+-------+---------------+ | name | average | worst | rounds_played | +--------+---------+-------+---------------+ | George | 84.6 | 103 | 22 | | Diane | 78.8 | 92 | 23 | +--------+---------+-------+---------------+ 2 rows in set (0.00 sec)

      As this output indicates, by prioritizing sets of predicates and wrapping them in parentheses, otherwise identical queries can return significantly different result sets.

      Although it isn’t always necessary to do so, it’s recommended that you always include parentheses when combining more than two predicates in a single search condition. Doing so can help make queries more readable and easier to understand.

      Excluding Results with NOT

      All of this guide’s examples so far have focused on how to write queries with WHERE clauses that only include rows that meet the specified search conditions in their result set. However, you can write queries that exclude specific rows by including the NOT operator in your WHERE clauses.

      Range, membership, and pattern matching predicates clauses that include the NOT operator generally follow this syntax:

      • . . .
      • WHERE column_name NOT OPERATOR value_expression
      • . . .

      To illustrate, run the following query. This will return values from the golfers table’s name column, but the NOT operator in its WHERE clause will cause the DBMS to exclude any rows that match the wildcard pattern:

      • SELECT name
      • FROM golfers
      • WHERE name NOT LIKE 'R%';

      Output

      +--------+ | name | +--------+ | George | | Pat | | Grady | | Diane | | Calvin | +--------+ 5 rows in set (0.00 sec)

      Things get a little different when adding the NOT operator to IS NULL predicates. In such cases you place the NOT between IS and NULL, as in the following example. This query returns the name and rounds_played values of every golfer whose rounds_played value isn’t Null:

      • SELECT name, rounds_played
      • FROM golfers
      • WHERE rounds_played IS NOT NULL;

      Output

      +---------+---------------+ | name | rounds_played | +---------+---------------+ | George | 22 | | Pat | 25 | | Grady | 11 | | Diane | 23 | | Raymond | 18 | +---------+---------------+ 5 rows in set (0.00 sec)

      You can also place the NOT operator immediately after the WHERE keyword. This is useful if you’re excluding rows based on whether they meet multiple search conditions, as in this example query which returns the golfers’ name, average, best, and wins values:

      • SELECT name, average, best, wins
      • FROM golfers
      • WHERE NOT (average < 80 AND best < 70) OR wins = 9;

      Output

      +---------+---------+------+------+ | name | average | best | wins | +---------+---------+------+------+ | George | 84.6 | 68 | 3 | | Pat | 68.7 | 65 | 9 | | Grady | 97.6 | 78 | 0 | | Diane | 78.8 | 70 | 1 | | Raymond | 81.3 | 67 | 1 | +---------+---------+------+------+ 5 rows in set (0.00 sec)

      Take note of this result set’s second row. Pat’s average score is less than 80 and her best score is less than 70. However, her row is still included in the result set, as the NOT operator only negates the search condition wrapped in parentheses.

      Recall that when you wrap multiple predicates separated by AND or OR in parentheses, SQL will prioritize those predicates and treat them as a single isolated search condition. Because of this, the NOT operator only excludes rows based on the first two predicates, average < 80 and best < 70. But it includes rows based on the third predicate, wins = 9.

      You can rewrite the query to exclude rows based on the third predicate along with the first two by wrapping all three in parentheses, like this:

      • SELECT name, average, best, wins
      • FROM golfers
      • WHERE NOT ((average < 80 AND best < 70) OR wins = 9);

      Output

      +---------+---------+------+------+ | name | average | best | wins | +---------+---------+------+------+ | George | 84.6 | 68 | 3 | | Grady | 97.6 | 78 | 0 | | Diane | 78.8 | 70 | 1 | | Raymond | 81.3 | 67 | 1 | +---------+---------+------+------+ 4 rows in set (0.00 sec)

      Depending on its SQL implementation, your database system may consider a query’s syntax invalid if you include NOT before a comparison operator. As an example, try running this query:

      • SELECT name
      • FROM golfers
      • WHERE name NOT = 'Grady';

      On MySQL and its derivatives, this will cause an error:

      Output

      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= 'Grady'' at line 1

      The reason for this error is that the NOT operator generally isn’t used with comparison operators (=, <>, <, <=, >, and >=), since you can achieve the opposite effect of one comparison operator by replacing it with another that would return the rows that the first would exclude. For example, you can replace the equivalence operator (=) with the inequivalence operator (<>).

      Conclusion

      By reading this guide, you learned how to write WHERE clauses so a query only returns rows that meet a specified condition. You also learned how to combine multiple predicates and search conditions in a single query, as well as how to use the NOT keyword to exclude information from result sets.

      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

      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