All Filters in Oracle Select-Where | SQL PLSQL
In the `SELECT` clause, the `WHERE` filter is used to restrict the rows returned by a query based on specified conditions. This allows you to retrieve only the rows that meet certain criteria. Here's an explanation of the `WHERE` filter and its operators:
Basic Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition
SELECT: Specifies the columns you want to retrieve.
FROM: Specifies the table from which to retrieve data.
WHERE: Filters the rows based on the specified condition(s).
Operators Used in the WHERE Clause:
1. Comparison Operators:
Equal to
Not equal to
Less than
Greater than
Less than or equal to
Greater than or equal to
Example:
```sql
SELECT * FROM employees WHERE salary 50000;
```
2. *Logical Operators:*
`AND`: Returns true if both conditions are true.
`OR`: Returns true if either condition is true.
`NOT`: Negates the condition.
Example:
```sql
SELECT * FROM orders WHERE order_status = 'Shipped' AND order_date '2023-01-01';
```
3. *IN Operator:*
Used to specify multiple values in a condition.
Example:
```sql
SELECT * FROM employees WHERE department_id IN (101, 102, 103);
```
4. *BETWEEN Operator:*
Specifies a range of values.
Example:
```sql
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
```
5. *LIKE Operator:*
Used for pattern matching using wildcards `%` and `_`.
Example:
```sql
SELECT * FROM customers WHERE email LIKE '%@example.com';
```
6. *IS NULL / IS NOT NULL:*
Checks for NULL values or non-NULL values.
Example:
```sql
SELECT * FROM orders WHERE shipping_address IS NULL;
```
These operators can be combined and nested to create complex conditions in the `WHERE` clause, allowing for precise filtering of data. They provide flexibility in constructing queries to retrieve the desired results based on specific criteria.