Choosing rows and columns: SELECT and WHERE

Introduction

SELECT columns FROM table WHERE condition;

• SELECT chooses the columns of the result: * means all of them.
• WHERE chooses the rows: a row is kept only if its condition is true.

The DBMS reads it in a different order from the one you write: first FROM (which table), then WHERE (which rows), and only then SELECT (which columns).

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  city TEXT,
  marks INTEGER
);
INSERT INTO students (name, dept, city, marks) VALUES
  ('Rahim', 'CSE', 'Dhaka', 85),
  ('Karim', 'EEE', 'Sylhet', 72),
  ('Nadia', 'CSE', 'Dhaka', 91),
  ('Tania', 'BBA', 'Khulna', 64),
  ('Sumon', 'CSE', 'Rajshahi', 58),
  ('Rina', 'EEE', 'Dhaka', 79);

SELECT name, marks FROM students WHERE dept = 'CSE' AND marks >= 80;

SELECT name, city FROM students WHERE city IN ('Sylhet', 'Khulna');

SELECT name, marks FROM students WHERE marks BETWEEN 60 AND 80;

SELECT name FROM students WHERE name LIKE 'R%';

SELECT name, marks, marks + 5 AS with_bonus FROM students WHERE NOT dept = 'CSE';

Most questions asked of a database are some version of "which rows match this?" WHERE is how you say it, and NULL is the one value that needs special care.

How it works

Conditions

AND is done before OR, just as * is done before +. dept = 'CSE' OR dept = 'EEE' AND marks > 80 means dept = 'CSE' OR (dept = 'EEE' AND marks > 80). When in doubt, add brackets.

In SQLite, LIKE ignores the difference between capitals and small letters for English letters; in PostgreSQL it does not (use ILIKE there).

Calculated columns

SELECT name, marks + 5 AS new_marks works out a value for each row. AS gives the column a name in the result; without it, the column is called marks + 5.

Syntax

SELECT name, marks FROM students
WHERE (city = 'Dhaka' OR city = 'Sylhet') AND marks BETWEEN 60 AND 80;

SELECT name FROM contacts WHERE phone IS NULL;
SELECT name, COALESCE(phone, 'none') AS phone FROM contacts;

Example

CREATE TABLE contacts (
  name TEXT,
  phone TEXT
);
INSERT INTO contacts VALUES ('Rahim', '01711-000001'), ('Karim', NULL), ('Nadia', '01811-000002');

-- = NULL is never true, so this finds nobody
SELECT name FROM contacts WHERE phone = NULL;

-- IS NULL is the way to ask
SELECT name FROM contacts WHERE phone IS NULL;

SELECT name, COALESCE(phone, 'no phone') AS phone FROM contacts;

phone = NULL is never true, not even when phone is NULL: comparing with an unknown value gives unknown, and WHERE keeps only rows where the condition is true. To test for NULL, write IS NULL or IS NOT NULL.

NULL spreads through arithmetic: NULL + 5 is NULL. COALESCE(phone, 'none') gives the first value in its list that is not NULL, which is handy for showing a default.

Line by line

1. contacts has three people, and Karim's phone is NULL.
2. WHERE phone = NULL is never true, because comparing with NULL gives unknown. The first result has no rows.
3. WHERE phone IS NULL is the right test, so the second result finds Karim.
4. COALESCE(phone, 'no phone') gives the phone when there is one, and the text no phone when it is NULL.
5. AS phone names the calculated column, so the third result shows all three people with something in the phone column.

Expected result

name
----
(0 rows)

name
-----
Karim
(1 row)

name   phone
-----  ------------
Rahim  01711-000001
Karim  no phone
Nadia  01811-000002
(3 rows)

In the real world

Search pages, report filters and admin screens all turn the boxes you fill in into a WHERE clause. Missing values are everywhere in real data, which is why reports so often use COALESCE to show a default.

Common mistakes

• Writing = NULL instead of IS NULL, which never matches anything.
• Mixing AND and OR without brackets, so the conditions group differently from what you meant.
• Expecting BETWEEN 60 AND 80 to leave out 60 and 80: both ends are included.

Best practices

• Put brackets round every OR that is combined with AND.
• Use IN (...) instead of a long chain of ORs.
• Decide what a missing value should mean before you filter on it.

Summary

SELECT chooses columns and WHERE chooses rows, with comparisons, AND, OR, NOT, BETWEEN, IN and LIKE. NULL is never equal to anything, so test it with IS NULL and replace it with COALESCE.

Mark lesson complete