Finding the Data you want with Operators

If you don't already have a library database, then make sure to run createdb library from the terminal before starting this chapter. The library database is needed to work through this chapter's content. The data needed to work through this chapter is stored here. If you haven't been following along feel free to download it. Then use the following command to restore the library database: psql -d library < advanced_querying.sql

What is an operator?

Conditions in SQL queries are evaluated using operators. An operator is a character or a word that SQL recognizes and then performs the operations specified by the operator.

In previous queries, you have seen something like the following:

SELECT username FROM users WHERE enabled = true;

In the clause, WHERE enabled = true, the = is a comparison operator, checking for entries where the field content is equal to 'true'. SQL provides many different operators. For instance when we run the query:

SELECT * FROM users
WHERE username='John Smith' OR username LIKE '%Jane%';

Quotes in PostgreSQL

Notice that we use single quotes instead of double quotes in the example above. It's important to keep this difference in mind. In PostgreSQL, you will almost always want to use single quotes when comparing values or when trying to specify a string value. In the code example above, using single quotes allows us to specify a string 'John Smith', and then we can compare that string to the current value of username

If we use double quotes, this would instruct PostgreSQL to do something else entirely. Double quotes are used to specify an identifier. In that case, PostgreSQL would look for some column in the table users called "John Smith" and then compare it to username.

There are several different types of operators used, the first operator = is a comparison operator, the second operator OR is a logical operator, and LIKE is a powerful operator for wildcard matches.

What is a wildcard?

SQL Wildcard Characters are characters that can be used as a substitute in a string. There are two different wildcard characters:

_: This wildcard is used to match one additional character. For instance, you could have a pattern like 'ab_' associated with a LIKE clause in your SQL statement. This would match any value that starts with 'ab' and ends with any other character.

%: This wildcard is used to match zero or more characters. For instance, you could have a pattern like '%name%', which would match with: "My name is..." or "name that character."

Comparison Operators

The Comparison operators are straightforward. We have included queries that you can run on the database for each of these operators. Go ahead and try them out.

=

library=# SELECT * FROM books WHERE author = 'Rudyard Kipling';
 id | title |     author      |       published_date       | isbn
----+-------+-----------------+----------------------------+------
 10 | Kim   | Rudyard Kipling | 2016-04-10 19:54:38.997113 |
(1 row)

<

library=# SELECT * FROM books WHERE id < 4;
 id |       title        |   author    |       published_date       | isbn
----+--------------------+-------------+----------------------------+------
  1 | My First SQL book  | Mary Parker | 2016-04-06 13:00:16.555379 |
  2 | My Second SQL book | John Mayer  | 2016-04-06 13:00:16.560789 |
  3 | My Third SQL book  | Alice Munro | 2016-04-06 13:00:16.569222 |
(3 rows)

>

library=# SELECT * FROM books WHERE id > 19;
 id |          title          |      author      |       published_date       | isbn
----+-------------------------+------------------+----------------------------+------
 20 | The Forest Schoolmaster |   Peter Rosegger | 2016-04-10 19:54:38.997113 |
(1 row)

<=

library=# SELECT * FROM books WHERE id <= 3;
 id |       title        |   author    |       published_date       | isbn
----+--------------------+-------------+----------------------------+------
  1 | My First SQL book  | Mary Parker | 2016-04-06 13:00:16.555379 |
  2 | My Second SQL book | John Mayer  | 2016-04-06 13:00:16.560789 |
  3 | My Third SQL book  | Alice Munro | 2016-04-06 13:00:16.569222 |
(3 rows)

>=

library=# SELECT * FROM books WHERE id >= 19;
 id |          title          |      author      |       published_date       | isbn
----+-------------------------+------------------+----------------------------+------
 19 | The Imprudence of Prue  |   Sophie Fisher  | 2016-04-10 19:54:38.997113 |
 20 | The Forest Schoolmaster |   Peter Rosegger | 2016-04-10 19:54:38.997113 |
(2 rows)

Logical Operators

You'll be using logical operators almost exclusively in the WHERE clause of queries. These operators can help filter data that has been retrieved from a SELECT query.

AND/OR

We already saw the AND/OR operators. They are used when combining multiple conditions. To reiterate:

The AND operator checks if all conditions are true to select the record. The OR operator checks if any condition is true.

library=# SELECT * FROM users INNER JOIN addresses ON users.id = addresses.user_id WHERE City='San Francisco' AND State='CA';
 id |  username   | enabled |         last_login         | user_id |     street      |     city      | state
----+-------------+---------+----------------------------+---------+-----------------+---------------+-------
  1 | John Smith  | t       | 2016-04-06 13:00:16.551272 |       1 | 1 Market Street | San Francisco | CA
  2 | Jane Smiley | t       | 2016-04-06 13:00:16.562224 |       2 | 2 Elm Street    | San Francisco | CA
(2 rows)

IS NULL/IS NOT NULL

library=# SELECT * FROM users LEFT JOIN addresses ON users.id = addresses.user_id WHERE addresses.street IS NULL;
 id |  username   | enabled |         last_login         | user_id | street | city | state
----+-------------+---------+----------------------------+---------+--------+------+-------
  3 | Alice Munro | t       | 2016-04-06 13:00:16.566877 |         |        |      |
(1 row)
library=# SELECT * FROM users LEFT JOIN addresses ON users.id = addresses.user_id WHERE addresses.street IS NOT NULL;
 id |  username   | enabled |         last_login         | user_id |     street      |     city      | state
----+-------------+---------+----------------------------+---------+-----------------+---------------+-------
  1 | John Smith  | t       | 2016-04-06 13:00:16.551272 |       1 | 1 Market Street | San Francisco | CA
  2 | Jane Smiley | t       | 2016-04-06 13:00:16.562224 |       2 | 2 Elm Street    | San Francisco | CA
(2 rows)

IN/NOT IN

The IN operator lets you specify multiple values in a WHERE clause.

library=# SELECT * FROM users WHERE id IN (1,2);
 id |  username   | enabled |         last_login
----+-------------+---------+----------------------------
  1 | John Smith  | t       | 2016-04-06 13:00:16.551272
  2 | Jane Smiley | t       | 2016-04-06 13:00:16.562224
(2 rows)

The NOT IN works the same way, except it returns the disjoint values.

library=# SELECT * FROM users WHERE id NOT IN (1,2);
 id |  username   | enabled |         last_login
----+-------------+---------+----------------------------
  3 | Alice Munro | t       | 2016-04-06 13:00:16.566877
(1 row)

LIKE Operator

The LIKE operator lets you check whether a field matches a string you supply. LIKE is useful in a variety of situations. For instance, suppose you need to see a list of users whose name begins with John:

library=# SELECT * FROM users WHERE username LIKE 'John%';
 id |  username  | enabled |         last_login
----+------------+---------+----------------------------
  1 | John Smith | t       | 2016-04-06 13:00:16.551272
(1 row)
  • LIKE '%ing' will match any field ending in 'ing' (the % signifying 'anything before this')
  • LIKE 'user%' will match any field that starts with user (the % signifying 'anything after this')
  • LIKE '%and%' will match any field that has 'and' (the % signifying anything surrounding this)

The NOT LIKE operator works the reverse way. So the above examples will have the following results:

  • NOT LIKE '%ing' will exclude any field ending in 'ing'
  • NOT LIKE 'user%' will exclude any field that starts with user
  • NOT LIKE '%and%' will exclude any field that has 'and' with any other text surrounding it.