NOT

MySQL

Example

SELECT * FROM Employees
WHERE NOT Gender='Male';

Output

| Employee_Id | Name     | Gender |
|-------------|----------|--------|
| 2           | Emma     | Female |
| 4           | Bella    | Female |
| 6           | Olivia   | Female |

Explanation

In this example, the “NOT” statement is used in conjunction with the “WHERE” clause to display all records from the ‘Employees’ table where the gender is not ‘Male’. Thus, only ‘Female’ records are returned.

PostgreSQL

Example

SELECT *
FROM orders
WHERE NOT customer_id = 1;

Output

| order_id | customer_id | product |
|----------|-------------|---------|
|   2      |     3       |   book  |
|   3      |     5       |  laptop |

Explanation

The above query selects all records from the ‘orders’ table where ‘customer_id’ does not equal 1. The NOT statement inverts the predicate (condition), the result is true if the predicate isn’t satisfied.

SQL Server

Example

SELECT *
FROM Employees
WHERE NOT Department = 'Sales';

Output

| EmployeeID | EmployeeName | Department |
|------------|--------------|------------|
| 1          | John Doe     | Marketing  |
| 2          | Jane Smith   | IT         |
| 3          | Mark Johnson | Finance    |

Explanation

In this SQL Server example, the query is searching for all employees whose department is not ‘Sales’. The NOT operator is used to exclude the records that do not meet the specified conditions.

Oracle

Example

SELECT * FROM Customers
WHERE NOT Country='Germany';

Output

CustomerID  CustomerName   ContactName   Country
     1      Alfreds        Maria         Mexico
     2      Ana Trujillo   Ana           Mexico
     3      Antonio Moreno Antonio       Mexico
     4      Around the Horn Thomas        UK
                                        ...

Explanation

The SQL NOT Operator is used to negate a condition in the WHERE clause. In this example, it filters out records where Customers are not from Germany. The output displays all customers whose country is not ‘Germany’.

SQLite

Example

CREATE TABLE Employees (
    ID INT,
    Name TEXT,
    Position TEXT
);

INSERT INTO Employees (ID, Name, Position)
VALUES (1, 'John', 'Engineer'),
       (2, 'Doe', 'Manager'),
       (3, 'Jane', 'Sales'),
       (4, 'Smith', 'Engineer');

SELECT * FROM Employees WHERE NOT Position = 'Engineer';

Output

ID  Name  Position
---------------
2  Doe  Manager
3  Jane  Sales

Explanation

The NOT operator in SQL is used to filter the results of a query based on whether a certain condition is not met. In the example provided, the SELECT * FROM Employees WHERE NOT Position = 'Engineer' query returns all rows where the position is not ‘Engineer’.