beginnersbook.com

  • Home
  • All Tutorials
    • Learn Servlet
    • Learn JSP
    • Learn JSTL
    • Learn C
    • Learn C++
    • Learn MongoDB
    • Learn XML
    • Learn Python
    • Learn Perl
    • Learn Kotlin
  • Core Java
  • OOPs
  • Collections
  • Java I/O
  • JSON
  • DBMS

SQL AND, OR and NOT Operators with examples

By Chaitanya Singh | Filed Under: SQL

In the last tutorial, we learned how to use where clause in an SQL statement. In this guide, we will learn about logical operators AND and OR which we can use in where clause to join multiple conditions and NOT operator which is used to negate a condition.

SQL AND Operator

When multiple conditions are joined using AND operator, only those rows will be fetched from the database which meets all the conditions.

AND Operator Syntax

SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition_1 AND condition_2 ...;

SQL AND Example

Table: STUDENT

+----+--------------+-----+-----------+----------+
| ID | STU_NAME     | AGE | ADDRESS   | BRANCH   |
+----+--------------+-----+-----------+----------+
| 11 | Carl         |  22 | Agra      |  ECE     |
| 12 | Rick         |  23 | Delhi     |  CSE     |
| 13 | Maggie       |  23 | Gurgaon   |  CL      |
| 14 | Carol        |  24 | Noida     |  ME      |
| 15 | Negan        |  26 | Delhi     |  EE      |
+----+--------------+-----+-----------+----------+

Lets write an SQL statements to fetch the name of the students where student age is “greater than 23” and address is “Delhi”

Query:

SELECT STU_NAME
FROM STUDENT
WHERE AGE > 23 AND ADDRESS = 'Delhi';

Result:

+----------+
| STU_NAME |
+----------+
| Negan    |
+----------+

SQL OR Operator

When multiple conditions are joined using OR operator, all those rows will be fetched from the database which meet any of the given conditions.

OR Operator Syntax

SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition_1 OR condition_2 ...;

SQL OR Example

Table: EMPLOYEE

+----+--------------+-----+-----------+----------+
| ID | EMP_NAME     | AGE | ADDRESS   | SALARY   |
+----+--------------+-----+-----------+----------+
| 90 | David        |  30 | Agra      |  10000   |
| 91 | Steve        |  31 | Delhi     |   9000   |
| 92 | Ajeet        |  29 | Gurgaon   |  11000   |
| 93 | Rahul        |  33 | Noida     |  19000   |
| 94 | Pappu        |  35 | Agra     |   9900    |
+----+--------------+-----+-----------+----------+

Lets write an SQL statements to fetch the details of the employees, where either employee age is “greater than 30” or address is “Agra”.

Query:

SELECT *
FROM EMPLOYEE
WHERE AGE > 30 OR ADDRESS = 'Agra';

Result:

+----+--------------+-----+-----------+----------+
| ID | EMP_NAME     | AGE | ADDRESS   | SALARY   |
+----+--------------+-----+-----------+----------+
| 90 | David        |  30 | Agra      |  10000   |
| 91 | Steve        |  31 | Delhi     |   9000   |
| 93 | Rahul        |  33 | Noida     |  19000   |
| 94 | Pappu        |  35 | Delhi     |   9900   |
+----+--------------+-----+-----------+----------+

Explanation:
Row number 1 is in the output because it meets the second condition (ADDRESS = ‘Agra’).
Row number 2nd and 4th are in the output because they meet the first condition (AGE > 30).
Row number 5th is in the output because it meets both the conditions.

SQL NOT Operator

When a NOT operator is used with a condition, only those rows will fetched from database which do not meet the given condition.

NOT Operator Syntax

SELECT column_name1, column_name2, ...
FROM table_name
WHERE NOT condition;

SQL NOT Example

Table: ORDER

+----+--------------+-----+-----------+----------+
| ID | CUSTOMER_NAME| AGE | ADDRESS   | AMOUNT   |
+----+--------------+-----+-----------+----------+
| 70 | Ram          |  30 | Sector121 |  11000   |
| 71 | Tim          |  31 | Sector122 |    900   |
| 72 | Tom          |  29 | Sector62  |   1000   |
| 73 | Lisa         |  33 | Sector61  |   1000   |
| 74 | Dexter       |  35 | Sector121 |    500   |
+----+--------------+-----+-----------+----------+

The following SQL statement selects all the order id and customer name where address is NOT Sector121.

Query:

SELECT ID, CUSTOMER_NAME
FROM ORDER
WHERE NOT ADDRESS = 'Sector121';

Result:

+----+--------------+
| ID | CUSTOMER_NAME| 
+----+--------------+
| 71 | Tim          |
| 72 | Tom          |
| 73 | Lisa         |
+----+--------------+

SQL AND, OR and NOT Operators together

We can use AND, OR and NOT logical operators together in where clause. Refer this tutorial: SQL AND, OR and NOT operators together.

❮ PreviousNext ❯

Enjoyed this post? Try these related posts

  1. SQL – DROP Table Statement to delete the entire table
  2. UPDATE Query in SQL
  3. SQL SELECT TOP Statement
  4. SQL DELETE Statement
  5. SQL UPDATE Statement
  6. DEFAULT Constraint in SQL

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

SQL Tutorial

  • SQL Introduction
  • SQL Syntax
  • SQL Select
  • SQL Select Distinct
  • SQL Select Count
  • SQL Select Top
  • SQL Where
  • SQL AND, OR & NOT
  • SQL Order By
  • SQL Insert Into
  • SQL Insert Into SELECT
  • SQL Select Random
  • SQL Alias
  • SQL NULL Check
  • SQL Update
  • SQL Delete
  • SQL MIN, MAX
  • SQL SUM
  • SQL AVG

Recently Added..

  • JSON Tutorial
  • Java Regular Expressions Tutorial
  • Java Enum Tutorial
  • Java Annotations Tutorial

Copyright © 2012 – 2021 BeginnersBook . Privacy Policy . Sitemap