SQL AND and OR
Operator
The
SQL AND and OR operators are used to combine multiple conditions to narrow data
in an SQL statement. These two operators are called conjunctive operators.
These
operators provide a means to make multiple comparisons with different operators
in the same SQL statement.
The AND Operator:
The
AND operator allows the existence of multiple conditions in an SQL statement's
WHERE clause.
Syntax:
SELECT
column1, column2, columnN
FROM
table_name
WHERE
[condition1] AND [condition2]...AND [conditionN];
You
can combine N number of conditions using AND operator. For an action to be
taken by the SQL statement, whether it be a transaction or query, all
conditions separated by the AND must be TRUE.
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Khilan | 25
| Delhi |
1500.00 |
|
3 | kaushik | 23
| Kota |
2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Hardik | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP |
4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-----------+------+-----------------+----------------+
Following is an
example, which
would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is
greater than 2000 AND age is less than 25 years:
SQL>
SELECT ID, NAME, SALARY
FROM
CUSTOMERS
WHERE
SALARY > 2000 AND age < 25;
This
would produce the following result:
+----+----------+--------------+
|
ID | NAME | SALARY
|
+----+----------+--------------+
|
6 | Komal | 4500.00 |
|
7 | Muffy | 10000.00
|
+----+-------+----------------+
The OR Operator:
The OR
operator is used to combine multiple conditions in an SQL statement's WHERE
clause.
Syntax:
The
basic syntax of OR operator with WHERE clause is as follows:
SELECT
column1, column2, columnN
FROM
table_name
WHERE
[condition1] OR [condition2]...OR [conditionN]
You
can combine N number of conditions using OR operator. For an action to be taken
by the SQL statement, whether it be a transaction or query, only any ONE of the
conditions separated by the OR must be TRUE.
Example:
Consider
the CUSTOMERS table having the following records:
+----+-------------+------+-----------------+---------------+
|
ID | NAME | AGE
| ADDRESS | SALARY |
+----+-------------+------+-----------------+---------------+
|
1 | Ramesh | 32
| Ahmedabad | 2000.00 |
|
2 | Khilan | 25
| Delhi |
1500.00 |
|
3 | kaushik
| 23 | Kota | 2000.00 |
|
4 | Chaitali | 25
| Mumbai | 6500.00 |
|
5 | Hardik | 27
| Bhopal | 8500.00 |
|
6 | Komal | 22
| MP | 4500.00 |
|
7 | Muffy | 24
| Indore |
10000.00 |
+----+-------------+------+-----------------+---------------+
Following is an
example, which
would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is
greater than 2000 OR age is less than 25 years:
SQL>
SELECT ID, NAME, SALARY
FROM
CUSTOMERS
WHERE
SALARY > 2000 OR age < 25;
This
would produce the following result:
+----+------------+-------------+
| ID |
NAME | SALARY |
+----+------------+-------------+
|
3 | kaushik | 2000.00 |
|
4 | Chaitali | 6500.00 |
|
5 | Hardik | 8500.00 |
|
6 | Komal | 4500.00 |
|
7 | Muffy | 10000.00 |
+----+----------+-------------+
0 Comments