AND and OR Operators in SQL

Introduction

MySQL provides various operators to work with and perform certain operations. An operator is a special keyword that is used to join clauses within a WHERE clause. These operators are used to specify a condition in a statement in MySQL. In this article, you'll study the AND and OR operators in MySQL.

The AND and OR operator falls under the logical operators category. The WHERE clause can be combined with the AND and OR operators. And these operators are used with the WHERE clause to filter out the records based on the condition of the operator used.

AND Operator

AND operator is a logical operator and is used when you want to combine more than one condition. The AND operator allows you to test multiple conditions and based on the result of that conditions certain data is retrieved. Specifically, AND keyword in MySQL compares two conditions and returns TRUE if all the conditions separated by AND are TRUE and displays that record. Similarly, it returns FALSE when either is FALSE.

• Syntax

  The syntax of AND operator is given below:

  In some cases, there are chances that you have to use more than one condition to filter the data rows. In that case, you can use AND operator as shown above, in the syntax.

  It only displays the data rows if all the conditions are TRUE. If the result of any of the conditions is FALSE then the SQL statement will return an empty result. You can add multiple conditions in a single SQL statement.

Now, let's see some examples to understand the need of the AND operator in MySQL.

• Examples

  • Using single AND operator:

    In the example below, to filter more than one column, AND operator is used to append conditions to the WHERE clause.

    When you run the above statement, the system evaluates if the first condition, i.e., Product ID is less than or equal to 5 is TRUE. If this is TRUE then it evaluates the second condition. If both are true then the system returns the data row. If any of the statements is FALSE then the system won't return the data row. The above statement will result in the following.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    1	23020	499	Chennai
    2	11021	349	Mumbai
    3	40042	279	Ahmedabad
    4	33030	449	Delhi
    5	39901	399	Gurugram

  • Using multiple AND operator.

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    1	23020	499	Chennai
    2	11021	349	Mumbai
    4	33030	449	Delhi

    In the above example, multiple AND operators are used to filter out the required data from the available data. When you run the above statement the system evaluates if the first condition, i.e., Product ID is less than or equal to 5 is TRUE. If this is TRUE then it evaluates the second condition, i.e., product price is less than or equal to 500 is TRUE. The results we get after executing these statements are all the five product items.

    Now, the second AND operator is being checked and the product numbers less than 35000 are filtered out from the five products as that was the result of the previous AND statement. In this way, all the AND operators are executed and you can get the required results.

  • Using AND operator with other Logical operators:

    Example:

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    3	40042	279	Ahmedabad
    4	33030	449	Delhi

    In the above example, multiple AND operator is used with the IN keyword to filter out the required data from the available data. When you run the above statement the system evaluates if the first condition, i.e., Product ID is less than 5 is TRUE. If this is TRUE then it evaluates the second condition, i.e., product price is less than or equal to 500 is TRUE. The results we get after executing these statements are all the first four product items.

    Now, the second AND operator is being checked with the IN operator, and the products that are available only in Delhi and Ahmedabad are required. And the result after executing these multiple statements is shown above in the table that contains only 2 rows or 2 products i.e. product 3 and 4 which follows all the conditions that are given in the WHERE clause.

    Example:

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    2	11021	349	Mumbai
    3	40042	279	Ahmedabad
    5	39901	399	Gurugram

    In the above example, AND operator is used with the NOT keyword to filter out the required data from the available data. When you run the above statement the system evaluates if the first condition, i.e., Product Available is not in Delhi is TRUE ,i.e., other than this data all the data of the table are fetched. If this is TRUE then it evaluates the second condition, i.e., product ID is less than or equal to 5 is TRUE. The results we get after executing these statements are all the products except the 4th product that is available in Delhi.

    Now, the second AND operator is being checked and after executing all the statements, three products are fetched that are shown in the above table.

OR Operator

OR operator is also a logical operator and it displays a record if either the first condition or the second condition is TRUE. If all the conditions are FALSE then the SQL statement won't return any result. Similar to the AND operator, OR operator is useful to add multiple conditions in a single SQL statement.

• Syntax

  The syntax of the OR operator is given below:

  The OR operator can be written as OR or || i.e. two pipe symbols.

• Examples

  • Using single OR operator.

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    1	23020	499	Chennai
    2	11021	349	Mumbai
    5	39901	399	Gurugram

    When you run the above SQL statement, the system evaluates if the first condition is TRUE. The system will evaluate the second condition whether this condition is TRUE or FALSE. The system returns the data row if any of the conditions are TRUE. If all the conditions are FALSE the system will not return any data row.

  • Using multiple OR operator:

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    1	23020	499	Chennai
    2	11021	349	Mumbai
    3	40042	279	Ahmedabad

    As shown in the result above, when you run the above SQL statement the system evaluates if the first condition is TRUE. Whether this condition is TRUE or FALSE, the system will evaluate the second condition. The system returns the data row if any of the conditions are TRUE. If all the conditions are FALSE the system will not return any data row. In this case, product ID less than or equal to 3 or product number less than 25000, any of the data value falls under these two statements is obtained after executing the above MySQL statement.

  • Using OR operator with other logical operators:

    Example:

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    2	11021	349	Mumbai
    3	40042	279	Ahmedabad
    4	33030	449	Delhi

    As shown in the above result, the first two condition results in the 2,3 product. This condition is checked with the OR condition and this results in the 3,4 product. Hence the final result will display the product ranging from 2 to 4.

    Example:

    The above MySQL statement will result in the following data.

    prdct_id	prdct_no	prdct_price	prdct_avlble
    1	23020	499	Chennai
    2	11021	349	Mumbai
    3	40042	279	Ahmedabad

    In the above example, the NOT operator is used with the AND and OR keywords to filter out the required data from the available data. When you run the above statement the system evaluates if the first condition, i.e., Product Available is not in Chennai is TRUE, i.e., other than this data all the data of the table are fetched. If this is TRUE then it evaluates the second condition, i.e., product number is less than 30000 is TRUE. After executing these statements, the results we get are the 1st and 2nd products.

    Now, the OR operator is being checked and after executing all the statements, three products are fetched that are shown in the above table.