CASE, views and set operations

Introduction

These three tools shape results: CASE turns values into labels, views give complicated queries a simple name, and set operations combine the results of separate queries.

How it works

CASE: choose a value

CASE is SQL's if/else. It works out a value for each row:

CREATE TABLE results (
  name TEXT,
  marks INTEGER
);
INSERT INTO results VALUES ('Rahim', 85), ('Karim', 72), ('Nadia', 91), ('Tania', 64), ('Sumon', 48);

SELECT name, marks,
  CASE
    WHEN marks >= 80 THEN 'A+'
    WHEN marks >= 70 THEN 'A'
    WHEN marks >= 60 THEN 'B'
    ELSE 'F'
  END AS grade
FROM results
ORDER BY marks DESC;

SELECT COUNT(*) AS students,
  SUM(CASE WHEN marks >= 60 THEN 1 ELSE 0 END) AS passed
FROM results;

The conditions are tried from the top, and the first true one wins, so marks >= 80 must come before marks >= 60. Without an ELSE, a row that matches nothing gets NULL. CASE can go anywhere a value can: in SELECT, ORDER BY, GROUP BY, even inside SUM (SUM(CASE WHEN marks >= 60 THEN 1 ELSE 0 END) counts the passes).

Views: a saved query

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  marks INTEGER
);
INSERT INTO students (name, marks) VALUES ('Rahim', 85), ('Karim', 72), ('Nadia', 91);

CREATE VIEW toppers AS
  SELECT name, marks FROM students WHERE marks >= 80;

SELECT * FROM toppers;

-- The view is not touched, but it shows the new data
UPDATE students SET marks = 82 WHERE name = 'Karim';
SELECT * FROM toppers ORDER BY marks DESC;

DROP VIEW toppers;

CREATE VIEW name AS SELECT ... saves a query under a name. Afterwards you use the view like a table: SELECT * FROM toppers.

• A view stores no rows. Its query runs each time the view is used, so it always shows the current data: after the UPDATE, toppers includes Karim without anyone touching the view.
• Views hide complicated joins behind a simple name, and they can show a user only the columns they may see (a staff_public view without the salary column, for example).
• DROP VIEW name deletes the view, never the rows of its tables.

Some DBMSs can also store a view's result (a materialized view), which is faster to read but must be refreshed.

Syntax

SELECT name, CASE WHEN marks >= 60 THEN 'pass' ELSE 'fail' END AS result FROM results;

CREATE VIEW toppers AS SELECT name FROM students WHERE marks >= 80;

SELECT name FROM cricket UNION SELECT name FROM football;

Example

UNION, INTERSECT and EXCEPT combine the results of two queries that have the same number of columns:

CREATE TABLE cricket (name TEXT);
CREATE TABLE football (name TEXT);
INSERT INTO cricket VALUES ('Rahim'), ('Karim'), ('Nadia');
INSERT INTO football VALUES ('Karim'), ('Tania'), ('Nadia'), ('Sumon');

SELECT name FROM cricket UNION SELECT name FROM football;

SELECT name FROM cricket UNION ALL SELECT name FROM football;

SELECT name FROM cricket INTERSECT SELECT name FROM football;

SELECT name FROM cricket EXCEPT SELECT name FROM football;

Line by line

1. cricket has three players and football has four, and Karim and Nadia play both.
2. UNION lists everyone who plays either game, each name once: five names.
3. UNION ALL keeps the repeats, so Karim and Nadia appear twice: seven rows.
4. INTERSECT keeps the names in both lists: Karim and Nadia.
5. EXCEPT keeps the cricket players who do not play football: only Rahim.

Expected result

name
-----
Karim
Nadia
Rahim
Sumon
Tania
(5 rows)

name
-----
Rahim
Karim
Nadia
Karim
Tania
Nadia
Sumon
(7 rows)

name
-----
Karim
Nadia
(2 rows)

name
-----
Rahim
(1 row)

In the real world

Views are used to give each team or application exactly the columns it needs, and to keep reports consistent. Set operations combine data from similar tables, such as this year's and last year's orders, or customers from two systems being merged.

Common mistakes

• Using UNION when repeats are impossible or wanted, which wastes time removing them; UNION ALL is faster.
• Putting a broad CASE condition before a narrow one, so the narrow one never wins.
• Expecting a view to keep a copy of old data: it always shows the current rows.

Best practices

• Always give CASE an ELSE, so no row gets an unexpected NULL.
• Add ORDER BY once, at the end of a set operation, when the order matters.
• Use views to hide joins that many queries repeat.

Summary

CASE chooses a value per row with the first true condition, a view saves a query under a name and always shows current data, and UNION, UNION ALL, INTERSECT and EXCEPT combine results with the same columns.

Mark lesson complete