Where AND, OR, NOT
WHERE conditions can be combined with AND, OR, and NOT Operators.
WHERE clause with AND requires that two conditions are true.
WHERE clause with OR requires that one of two conditions is true.
WHERE clause with NOT negates the specified condition(displays if NOT TRUE).
SQL Where AND Syntax:
SELECT Column1
FROM Table_Name
WHERE condition1 AND condition2;
SQL Where OR Syntax:
SELECT Column1
FROM Table_Name
WHERE condition1 OR condition2;
SQL Where NOT Syntax:
SELECT Column1
FROM Table_Name
WHERE NOT condition1;
Example:
Lets use to “Products” table to find all products from the Supplier “TechOne” and with a price under $500.
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
| 2 | Laptop 5 | 4000 Laptop 5th generation | 700 | TechTwo | 2 |
| 3 | 3D Printer | 3D Print Pro | 900 | TechOne | 1 |
| 4 | Labtop 7 | 100 Labtop 7 processor | 1200 | TechTwo | 2 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
SELECT
ProductID,
ProductName,
Description,
Price,
SupplierName,
SupplierID
FROM Products
WHERE SupplierName = ‘TechOne’ AND Price < 500
Results:
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
Now lets use the OR operator to find all products with the Supplier Name of “TechOne” or a price under $500.
SELECT
ProductID,
ProductName,
Description,
Price,
SupplierName,
SupplierID
FROM Products
WHERE SupplierName = ‘TechOne’ OR Price < 500
Results:
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 1 | Super Printer | 9000 series printer | 300 | TechOne | 1 |
| 3 | 3D Printer | 3D Print Pro | 900 | TechOne | 1 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
Now we will use the NOT operator to find all products that are not from the Supplier Name '“TechOne”.
SELECT
ProductID,
ProductName,
Description,
Price,
SupplierName,
SupplierID
FROM Products
WHERE NOT SupplierName = ‘TechOne’
Results:
| ProductID | ProductName | Description | Price | SupplierName | SupplierID |
|---|---|---|---|---|---|
| 2 | Laptop 5 | 4000 Laptop 5th generation | 700 | TechTwo | 2 |
| 4 | Labtop 7 | 100 Labtop 7 processor | 1200 | TechTwo | 2 |
| 5 | Camera 400 | Camera Ultra 400 | 400 | TechThree | 3 |
SQL Tutorials