SQL And, Or & Not

The SQL AND, OR & NOT Operators can be combined with the WHERE clause and used. These operators are usually used if you have multiple conditions to be checked for filtering out the data.

The SQL AND, OR and NOT Operators

The AND and OR operators are used to filter records based on more than one condition:

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition (s) is not TRUE.

    SELECT column_1, column_2
    FROM table_name
    WHERE condition_1 AND condition_2 AND condition_3;
  

    SELECT column_1, column_2
    FROM table_name
    WHERE condition_1 OR condition_2 OR condition_3;
  

    SELECT column_1, column_2
    FROM table_name
    WHERE NOT condition;
  

Query

Consider a customer table having multiples records

AND Example:

The following SQL statement selects all fields from “customers” where country is “Germany” AND city is “Berlin”:

SELECT * FROM customers
WHERE country = 'Germany' AND city = 'Berlin';

-- returns 1 records
-- +--------------------------------+
-- | 2 - John  - Berlin   - Germany |
-- +--------------------------------+

OR Example:

The following SQL statement selects all fields from “customers” where city is “Berlin” OR “Mannheim”:

SELECT * FROM customers
WHERE city = 'Berlin' OR city = 'Mannheim';

-- returns 2 records
-- +--------------------------------+
-- | 1 - Steve - Mannheim - Germany |
-- | 2 - John  - Berlin   - Germany |
-- +--------------------------------+

NOT Example:

The following SQL statement selects all fields from “customers” where country is NOT “Germany”:

SELECT * FROM customers
WHERE NOT country = 'Germany';

-- returns 2 records
-- +------------------------------------+
-- | 3 - Yaya Vanakova - Madrid - Spain |
-- | 4 - Howard Snyder - Eugene - USA   |
-- +------------------------------------+

Combined AND, OR and NOT Example:

The following SQL statement selects all fields from “customers” where country is “Germany” AND city must be “Berlin” OR “Leipzig”:

SELECT * FROM customers
WHERE country='Germany' AND (City='Berlin' OR City='Leipzig');

-- returns 2 records
-- +------------------------------------------+
-- | 1 - Steve           - Mannheim - Germany |
-- | 5 - Alexander Feuer - Leipzig  - Germany |
-- +------------------------------------------+

The following SQL statement selects all fields from “customers” where country is NOT “Germany” and NOT “USA”:

SELECT * FROM customers
WHERE NOT country='Germany' AND NOT country='USA';

-- returns 1 records
-- +-------------------------------------+
-- | 3 - Yaya Vanakova - Madrid - Spain  |
-- +-------------------------------------+

.
SQL Complete Guide