SQL AND and OR Conjunctive Operators

    In SQL we have two logical operators AND & OR. These two operators operate between two condition operation and narrow down the data selection for the SQL statements. These two operators are also known as conjunctive operators.

    AND Operator

    Like the and Operators in high-level programming languages, AND operator of SQL performs a similar function, it allows the existence of multiple condition expressions in a single statement. The AND operator always operate between two conditional expressions and filter those data sets which are True for both the conditional expressions. AND syntax 

    SELECT column_name_1, column_name_2, column_name_N 
FROM table_name
WHERE [condition_expression_1] AND [condition_expression_2];

    Example For this example considered the table students: 

    +------+--------+------+--------+-------+
| id   |  name  | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 |  Luffy | 16   | A      |  970  |
|    2 | Naruto | 18   | A      |  960  |
|    3 | Zoro   | 20   | A      |  940  |
|    4 | Sanji  | 21   | B      |  899  |
|    5 | Nami   | 17   | B      |  896  |
|    6 | Robin  | NULL | NULL   |  860  |
+------+--------+------+--------+-------+

    Query: Display those students details whose marks are greater than 880 and age is greater than 16. 

    SELECT *
FROM students
WHERE marks>880 AND age > 16;

    Output 

    +----+--------+------+--------+-------+
| id | name   | age  | grades | marks |
+----+--------+------+--------+-------+
| 2  | Naruto | 18   | A      | 960   |
| 3  | Zoro   | 20   | A      | 940   |
| 4  | Sanji  | 21   | B      | 899   |
| 5  | Nami   | 17   | B      | 896   |
+----+--------+------+--------+-------+

    Behind the Query Here the AND operator operate between two conditional expressions marks>880 & age > 16 , and filter out those data sets which are satisfying both the conditions.

    OR Operator

    The OR operator is similar to the AND operator that operates between two conditional expressions, the different is, OR operator filters that data set if that data satisfy any of the conditional expression. In short, we can say that if anyone conditional expression is True then the OR operator will filter that data. OR operator Syntax 

    SELECT column_name_1, column_name_2, column_name_N 
FROM table_name 
WHERE [condition_expression_1] OR[condition_expression_2];

    Example For this example considered the table student: 

    +------+--------+------+--------+-------+
| id   |  name  | age  | grades | marks |
+------+--------+------+--------+-------+
|    1 |  Luffy | 16   | A      |  970  |
|    2 | Naruto | 18   | A      |  960  |
|    3 | Zoro   | 20   | A      |  940  |
|    4 | Sanji  | 21   | B      |  899  |
|    5 | Nami   | 17   | B      |  896  |
|    6 | Robin  | NULL | NULL   |  860  |
+------+--------+------+--------+-------+

    Query: Show the students details whose has marks greater than 880 or their grade is A 

    SELECT *
FROM students
WHERE marks>880 OR grades = "A";

    Output 

    +------+--------+------+--------+-------+
| id   |   name |  age | grades | marks |
+------+--------+------+--------+-------+
| 1    |  Luffy |   16 |      A |   970 |
| 2    | Naruto |   18 |      A |   960 |
| 3    |   Zoro |   20 |      A |   940 |
| 4    |  Sanji |   21 |      B |   899 |
| 5    |   Nami |   17 |      B |   896 |
+------+--------+------+--------+-------+

    Behind the Query Here using the OR operator we select all those data sets which satisfy any one of these two conditions marks>880 & grades = "A" .

    Summary

    • AND and OR are the two logical operators of SQL.
    • These are also known as conjunctive operators.
    • AND operator filters the data for those data sets which satisfy both the conditional expression.
    • OR operator filters the data for those data sets which satisfy either one of the conditional expression.

    People are also reading:

    Previous Tutorial Next Tutorial