WHERE Clause

??

Operators Available

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> or !=	Not equal
AND	Logical operator AND
OR	Logical operator OR
IN	Return true if a value matches any value in a list
BETWEEN	Return true if a value is between a range of values
LIKE	Return true if a value matches a pattern
IS NULL	Return true if a value is NULL
NOT	Negate the result of other operators

Where

• Cannot use column alias with where clause

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

Where with OR

select first_name, last_name, date_of_birth
from actors
where date_of_birth < '1990-01-01' 
   or date_of_birth > '1980-01-01'
LIMIT 10;

 first_name | last_name |    date_of_birth    
------------+-----------+---------------------
 Malin      | Akerman   | 1978-05-12
 Tim        | Allen     | 1953-06-13
 Julie      | Andrews   | 1935-10-01
 Ivana      | Baquero   | 1994-06-11
 Lorraine   | Bracco    | 1954-10-02
 Alice      | Braga     | 1983-04-15
 Marlon     | Brando    | 1924-04-03
 Adrien     | Brody     | 1973-04-14
 Peter      | Carlberg  | 1950-12-08
 Gemma      | Chan      | 1982-11-29

select first_name, last_name, date_of_birth
from actors
where date_of_birth < '1990-01-01'
   or date_of_birth > '1980-01-01'
ORDER BY date_of_birth
LIMIT 10;

-- with order by clause
 first_name | last_name |    date_of_birth    
------------+-----------+---------------------
 Clark      | Gable     | 1901-02-01 00:00:00
 Scatman    | Crothers  | 1910-05-23 00:00:00
 Vivien     | Leigh     | 1913-11-05 00:00:00
 Alec       | Guiness   | 1914-04-02 00:00:00
 Judy       | Garland   | 1922-06-10 00:00:00
 Marlon     | Brando    | 1924-04-03 00:00:00
 Dick       | Van Dyke  | 1925-12-13 00:00:00
 Sihung     | Lung      | 1930-01-01 00:00:00
 Ian        | Holm      | 1931-09-12 00:00:00
 Rebecca    | Pan       | 1931-12-29 00:00:00

Where with AND

select *
from directors
where date_of_birth > '1970-01-01'
  AND nationality = 'British';
  
 director_id | first_name | last_name | date_of_birth | nationality 
-------------+------------+-----------+---------------+-------------
           4 | Richard    | Ayoade    | 1977-06-12    | British
          18 | Martin     | McDonagh  | 1970-03-26    | British

Where with BETWEEN (NOT BETWEEN)

select *
from directors
where date_of_birth between '1970-01-01' and '1990-01-01';

 director_id | first_name |        last_name         | date_of_birth | nationality 
-------------+------------+--------------------------+---------------+-------------
           2 | Paul       | Anderson                 | 1970-06-26    | American
           4 | Richard    | Ayoade                   | 1977-06-12    | British
          11 | Florian    | Henckel von Donnersmarck | 1973-05-02    | German
          18 | Martin     | McDonagh                 | 1970-03-26    | British

Where with LIKE

SELECT first_name,
       last_name
FROM actors
WHERE actors.last_name LIKE '%son'
ORDER BY first_name;

 first_name |  last_name  
------------+-------------
 Jack       | Nicholson
 Lina       | Leandersson
 Luke       | Wilson
 Mykelti    | Williamson
 Owen       | Wilson
 Patrick    | Wilson
 Samuel     | Jackson
 Woody      | Harrelson

Where with IN and NOT IN

select *
from directors
where nationality in ('American', 'Japenese');

 director_id | first_name |        last_name         | date_of_birth | nationality  
-------------+------------+--------------------------+---------------+--------------
           1 | Tomas      | Alfredson                | 1965-04-01    | Swedish
           2 | Paul       | Anderson                 | 1970-06-26    | American
           3 | Wes        | Anderson                 | 1969-05-01    | American
           4 | Richard    | Ayoade                   | 1977-06-12    | British
           5 | Luc        | Besson                   | 1959-03-18    | French
           6 | James      | Cameron                  | 1954-08-16    | American
           7 | Guillermo  | del Toro                 | 1964-10-09    | Mexican
           
select *
from directors
where nationality not in ('American', 'Japenese');

 director_id | first_name |        last_name         | date_of_birth | nationality  
-------------+------------+--------------------------+---------------+--------------
           1 | Tomas      | Alfredson                | 1965-04-01    | Swedish
           4 | Richard    | Ayoade                   | 1977-06-12    | British
           5 | Luc        | Besson                   | 1959-03-18    | French
           7 | Guillermo  | del Toro                 | 1964-10-09    | Mexican
          10 | Kinji      | Fukasaku                 | 1930-07-03    | Japanese
          11 | Florian    | Henckel von Donnersmarck | 1973-05-02    | German
          12 | Terry      | Jones                    | 1942-02-01    | British