Practical 4: Simple Queries

+------------------+--------+
| title            | price  |
+------------------+--------+
| Database Systems | 720.50 |
| MySQL Reference  | 499.00 |
| Learn SQL        | 280.00 |
+------------------+--------+

The comparison operators:

= is the first surprise for anybody arriving from C, where = assigns and == compares. In SQL there is no assignment inside a WHERE, so one sign is enough.

SELECT title, price FROM book WHERE price BETWEEN 400 AND 600;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| MySQL Reference   | 499.00 |
| Computer Networks | 540.75 |
| Discrete Maths    | 430.00 |
+-------------------+--------+

BETWEEN includes both ends, so 400 and 600 would both be in. It is the same as price >= 400 AND price <= 600, and it is easier to read.

SELECT title, subject FROM book WHERE subject IN ('Networks', 'Systems');

+-------------------+----------+
| title             | subject  |
+-------------------+----------+
| Computer Networks | Networks |
| Operating Systems | Systems  |
+-------------------+----------+

IN is the same as a chain of ORs and is much shorter. It comes back in [Practical 7: Subqueries with IN], where the list is produced by a query instead of typed.

LIKE: matching a pattern

Two wildcards, and only two.

SELECT title FROM book WHERE title LIKE 'C%';

+-------------------+
| title             |
+-------------------+
| Computer Networks |
+-------------------+

SELECT title FROM book WHERE title LIKE '%Systems';

+-------------------+
| title             |
+-------------------+
| Database Systems  |
| Operating Systems |
+-------------------+

SELECT member_name FROM member WHERE member_name LIKE '_a%';

+---------------+
| member_name   |
+---------------+
| Ravi Deshmukh |
+---------------+

The last one asks for names whose second letter is a: one character, then an a, then anything.

Whether LIKE 'c%' in small letters also finds Computer Networks depends on the column's collation, and in this database it does, because the collation ends ci for case insensitive, as [Practical 2: Viewing Databases, Creating One and Listing Its Tables] showed. On a server whose collation is case sensitive it would not. If a LIKE must ignore case whatever the collation, write WHERE LOWER(title) LIKE 'c%'.

AND, OR, NOT

SELECT title, subject, price FROM book
WHERE subject = 'Databases' AND price > 400;

+------------------+-----------+--------+
| title            | subject   | price  |
+------------------+-----------+--------+
| Database Systems | Databases | 720.50 |
| MySQL Reference  | Databases | 499.00 |
+------------------+-----------+--------+

AND binds tighter than OR, exactly as multiplication binds tighter than addition. So this:

WHERE subject = 'Databases' OR subject = 'Networks' AND price > 600

means "Databases, or else Networks costing more than 600", which is almost certainly not what was wanted. Bracket every mixture of AND and OR, even where you are sure:

WHERE (subject = 'Databases' OR subject = 'Networks') AND price > 600

NULL, and why it needs its own operators

One member has no city recorded. Ask for the members whose city is not Mumbai:

SELECT member_id, member_name, city FROM member WHERE city <> 'Mumbai';

+-----------+---------------+--------+
| member_id | member_name   | city   |
+-----------+---------------+--------+
|         2 | Ravi Deshmukh | Thane  |
|         4 | Imran Shaikh  | Kalyan |
|         6 | Vikram Rao    | Thane  |
+-----------+---------------+--------+

Neha Patil is missing from that answer, and her city is certainly not Mumbai. Here is why:

SELECT NULL = NULL     AS null_equals_null,
       NULL <> 'Mumbai' AS null_not_mumbai,
       NULL IS NULL    AS null_is_null;

+------------------+-----------------+--------------+
| null_equals_null | null_not_mumbai | null_is_null |
+------------------+-----------------+--------------+
|             NULL |            NULL |            1 |
+------------------+-----------------+--------------+

NULL is not a value; it is the absence of one. Any comparison with it gives NULL, which is neither true nor false, and a WHERE keeps only the rows for which the condition is true. So a row with a NULL city is excluded by city = 'Mumbai' and excluded by city <> 'Mumbai' alike.

That is what IS NULL and IS NOT NULL are for, and they are the only operators that work on it:

SELECT member_id, member_name FROM member WHERE city IS NULL;

+-----------+-------------+
| member_id | member_name |
+-----------+-------------+
|         5 | Neha Patil  |
+-----------+-------------+

To ask the question that was really meant, say so:

SELECT member_id, member_name, city FROM member
WHERE city <> 'Mumbai' OR city IS NULL;

+-----------+---------------+--------+
| member_id | member_name   | city   |
+-----------+---------------+--------+
|         2 | Ravi Deshmukh | Thane  |
|         4 | Imran Shaikh  | Kalyan |
|         5 | Neha Patil    | NULL   |
|         6 | Vikram Rao    | Thane  |
+-----------+---------------+--------+

COALESCE is the tidy way to put a stand-in value in the result:

SELECT member_name, COALESCE(city, 'not recorded') AS city FROM member LIMIT 3;

+---------------+--------+
| member_name   | city   |
+---------------+--------+
| Asha Kulkarni | Mumbai |
| Ravi Deshmukh | Thane  |
| Meena Iyer    | Mumbai |
+---------------+--------+

ORDER BY

SELECT title, price FROM book ORDER BY price DESC LIMIT 4;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| Database Systems  | 720.50 |
| Operating Systems | 655.25 |
| Data Structures   | 610.00 |
| Computer Networks | 540.75 |
+-------------------+--------+

ASC is ascending and is the default; DESC is descending. Sort by more than one column by listing them, and the second decides only where the first is equal:

SELECT subject, title FROM book ORDER BY subject ASC, title DESC;

+-------------+-------------------+
| subject     | title             |
+-------------+-------------------+
| Databases   | MySQL Reference   |
| Databases   | Learn SQL         |
| Databases   | Database Systems  |
| Mathematics | Discrete Maths    |
| Networks    | Computer Networks |
| Programming | The C Language    |
| Programming | Data Structures   |
| Systems     | Operating Systems |
+-------------+-------------------+

Without an ORDER BY, the order of a result is not promised. It usually comes out in a convenient order and that order can change when an index is added or the table grows. If the order matters, say so.

LIMIT

SELECT title, price FROM book ORDER BY price DESC LIMIT 3;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| Database Systems  | 720.50 |
| Operating Systems | 655.25 |
| Data Structures   | 610.00 |
+-------------------+--------+

The three most expensive books. LIMIT with two numbers skips some first:

SELECT title, price FROM book ORDER BY price DESC LIMIT 3, 2;

+-------------------+--------+
| title             | price  |
+-------------------+--------+
| Computer Networks | 540.75 |
| MySQL Reference   | 499.00 |
+-------------------+--------+

LIMIT 3, 2 means skip three and then take two, so those are the fourth and fifth most expensive. The clearer spelling is LIMIT 2 OFFSET 3, which means the same thing with the numbers the right way round.

LIMIT without ORDER BY is the commonest way of getting a wrong answer that looks right: "the top three" is meaningless until you have said top by what.

The order the server does it in

This is the part that explains several errors at once.

SELECT is nearly last. Two consequences follow, and both are asked:

A column alias cannot be used in WHERE. SELECT price * 1.1 AS new_price FROM book WHERE new_price > 500 fails, because the WHERE runs before the alias exists. Repeat the expression, or use a subquery.

A column alias can be used in ORDER BY, because ORDER BY runs after SELECT.

What beginners get wrong

WHERE city = NULL. Never matches. Use IS NULL.

Forgetting that <> 'Mumbai' excludes the NULLs too.

Double quotes round a string. MySQL allows them; the standard does not. Use single quotes.

SELECT * in a query that will be kept. Name the columns.

Mixing AND and OR without brackets. AND binds tighter.

LIMIT with no ORDER BY, and calling the result the top three.

A column alias in the WHERE. The alias does not exist yet.

LIKE with no wildcard. LIKE 'Learn SQL' is just a slower =.

Quick revision

• SELECT cols FROM t WHERE cond ORDER BY cols LIMIT n; and the clauses must be written in that order.
• * is every column; name them instead in anything you keep.
• AS renames a column in the result only.
• DISTINCT removes duplicate rows of the result, not duplicate values of one column.
• =, <>, <, >, BETWEEN a AND b inclusive, IN (list), LIKE, IS NULL.
• % is any number of characters, _ is exactly one.
• NULL compares equal to nothing, not even NULL. Use IS NULL and IS NOT NULL.
• COALESCE(col, 'stand-in') supplies a value where there is none.
• ORDER BY col DESC, and a second column decides ties.
• LIMIT n and LIMIT skip, n, which is LIMIT n OFFSET skip.
• Evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

What goes in your journal

Aim, and then one query per feature with its result underneath: every column, chosen columns, an alias, DISTINCT, each comparison operator, both LIKE wildcards, IS NULL, ORDER BY and LIMIT. Ten or twelve short queries.

Include the pair that shows the NULL trap: WHERE city <> 'Mumbai' and then the same query with OR city IS NULL. Two results, one row different, and the difference is the practical's whole lesson.

Test yourself

1. Why does WHERE city = NULL return nothing? Because a comparison with NULL gives NULL rather than true, and WHERE keeps only rows where the condition is true. The test is IS NULL.

2. What is the difference between % and _ in a LIKE pattern? % matches any number of characters, including none. _ matches exactly one.

3. Is BETWEEN 400 AND 600 inclusive? Yes, both ends are included.

4. Why can a column alias be used in ORDER BY but not in WHERE? Because WHERE is evaluated before SELECT, where the alias is created, and ORDER BY is evaluated after it.

5. What does LIMIT 3, 2 mean? Skip the first three rows and return the next two. The same thing is written LIMIT 2 OFFSET 3.

6. What order does the server evaluate the clauses in? FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

7. Why bracket a mixture of AND and OR? Because AND binds more tightly than OR, so an unbracketed mixture rarely means what it looks like.