8.9. SQL Select Where

8.9.1. Rationale

  • Order clauses to filter out the most data first!

8.9.2. Selection

  • = - equals

  • != - not equal

  • <> - not equal

  • > - greater then

  • >= - greater or equal

  • < - less than

  • <= - less or equal

SELECT *
FROM astronauts
WHERE lastname = 'Watney';
SELECT *
FROM astronauts
WHERE agency != 'NASA';
SELECT *
FROM astronauts
WHERE age > 30;

8.9.3. Conjunction

  • AND - conjunction

SELECT *
FROM astronauts
WHERE lastname = 'Watney'
AND lastname = 'Mark';
SELECT *
FROM astronauts
WHERE age > 30
AND age < 55;

8.9.4. Alternative

  • OR - alternative

SELECT *
FROM astronauts
WHERE lastname = 'Watney'
OR lastname = 'Lewis';

8.9.5. Contains

  • IN - contains

  • NOT IN - not contains

SELECT *
FROM astronauts
WHERE career IN ('Pilot', 'Engineer', 'Scientist', 'Medical Doctor');
SELECT *
FROM astronauts
WHERE lastname NOT IN ('Watney', 'Lewis', 'Martinez');

8.9.6. Identity

  • IS - identity check

  • IS NOT - negation of an identity check

SELECT *
FROM astronauts
WHERE mission IS NULL;
SELECT *
FROM astronauts
WHERE mission IS NOT NULL;

8.9.7. Like

  • LIKE

  • % - Any character (many)

  • _ - Any character (one)

SELECT *
FROM astronauts
WHERE lastname LIKE 'Wat%';
SELECT *
FROM astronauts
WHERE lastname LIKE '%ney';
SELECT *
FROM astronauts
WHERE lastname LIKE '%tn%';
SELECT *
FROM astronauts
WHERE lastname LIKE 'Watne_';
SELECT *
FROM astronauts
WHERE lastname LIKE '_tn%';