AND & OR operator:




The AND and OR operators are mostly used with the WHERE clause to build more flexible and precise conditions. This is because both the operators are binary operators and work with two variables therefore; these operators will use more than one condition to extract or to fetch out the result from the database.

The AND and OR operators are also called conjunctive operators.




AND operator

The AND operator is used to allow or set multiple conditions with the WHERE clause.

 

SYNTAX

The query with AND statement with WHERE clause has the following syntax:

SELECT column-name

FROM table-name

WHERE (condition) AND (condition)… AND (condition);

EXAMPLE

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Suppose you want to display those students whose monthly fee is greater than 1000 and their student id is greater than 100. As you are using the AND operator then both of the conditions must be true in order to get the result. The query for this problem will be the following:

SELECT * FROM Student WHERE MONFEE > 100 AND STUD_ID > 100

This query will produce the following result:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97

It can be seen here that we also had STUD_ID greater than 100 that is 150 but these entries are not returned because the MONFEE of these students were not greater than 1000. Therefore, this is the operation of the AND operator.




OR operator:

The OR operator is used to allow or set multiple conditions with the WHERE clause in a SQL statement. The difference between an OR operator and the AND operator is that in the AND operator all the conditions must be true in order to get the result whereas, at least one condition must be true in the OR statement to get the result.

 

SYNTAX

The query with OR statement with WHERE clause has the following syntax:

SELECT column-name

FROM table-name

WHERE condition OR condition … OR condition

EXAMPLE

Suppose you have a table that is named STUDENT as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Suppose you want to display those students whose monthly fee is greater than 1000 or their student id is greater than 100. As you are using the OR operator then at least one of the conditions must be true in order to get the result. The query for this problem will be the following:

SELECT * FROM Student WHERE MONFEE > 100 OR STUD_ID > 100

This query will produce the following result:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97




It can be seen in the above example that the MONFEE in some entries is not greater than 1000 but it is displayed because of the student id as it is greater than 100. This is because the OR operator is used.