Using Comparison Operators
Comparison operators are used to compare one expression with another. The result of the comparison is a boolean value, either true or false. The following table lists the comparison operators available in SQL.
| Operator | Description |
|---|---|
| = | Equal to |
| <> or != | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
Let's Take an example...
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 10000 |
| 2 | Jane | Doe | 12000 |
| 3 | Mike | Smith | 15000 |
| 4 | David | Johnson | 8000 |
The following query retrieves all employees whose salary is greater than 10000.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000;
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 2 | Jane | Doe | 12000 |
| 3 | Mike | Smith | 15000 |
Using Logical Operators
Logical operators are used to combine two or more conditions. The following table lists the logical operators available in SQL.
| Operator | Description |
|---|---|
| AND | True if all conditions separated by AND are true |
| OR | True if any condition separated by OR is true |
| NOT | True if the condition is false |
Let's Take an example...
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 10000 |
| 2 | Jane | Doe | 12000 |
| 3 | Mike | Smith | 15000 |
| 4 | David | Johnson | 8000 |
The following query retrieves all employees whose salary is greater than 10000 and whose first name is Jane.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 10000 AND first_name = 'Jane';
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 2 | Jane | Doe | 12000 |
Using BETWEEN and IN Operators
The BETWEEN operator is used to select values within a range. The following query retrieves all employees whose salary is between 10000 and 15000.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 10000 AND 15000;
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | John | Doe | 10000 |
| 2 | Jane | Doe | 12000 |
| 3 | Mike | Smith | 15000 |
The IN operator is used to specify multiple values in a WHERE clause. The following query retrieves all employees whose first name is either Jane or Mike.
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE first_name IN ('Jane', 'Mike');
| employee_id | first_name | last_name | salary |
|---|---|---|---|
| 2 | Jane | Doe | 12000 |
| 3 | Mike | Smith | 15000 |