Fulfledge

Pull rows out, then filter them

INPUT · Slides

Bigger and smaller

01 / 04

Exact is not always what you want

= can only hit things that are exactly the same. But what you usually want to know is a range — "things at 200 or under", "things with fewer than 20 left".

That is what the comparison operators are for.

02 / 04

The four for bigger and smaller

They are the same symbols you have seen in maths.

  • price > 200 … over 200 (200 itself is out)
  • price >= 200 … 200 or more (200 is in)
  • price < 200 … under 200
  • price <= 200 … 200 or less

Remember it as: add the = and "exactly that" comes along too.

SELECT item, price FROM stocks  WHERE price >= 200;

Result

notebook | 200
colour pencils | 480
paper clips | 200

03 / 04

The boundary changes the answer

> and >= only differ right at the value. But if there is even one row sitting on it, the answer changes.

Below is the > version. The two rows at exactly 200 have gone, leaving only the 480 one.

SELECT item, price FROM stocks  WHERE price > 200;

Result

colour pencils | 480

04 / 04

"Not equal" is <>

"Is not" is written <>. It is the symbol that stands where JavaScript's !== did (!= works too).

It works on numbers and on text. You can now write conditions that leave things out, like "anything but Midori Stationery". Let us write some.

SELECT item, maker FROM stocks  WHERE maker <> 'Midori Stationery';

Result

notebook | Aoba Paper
ruler | Hoshiya Works
paper clips | Hoshiya Works