BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

SQL AND, OR and NOT Operators with examples

Last Updated: November 28, 2018 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 ❯

Top Related Articles:

  1. Introduction to SQL
  2. DELETE Query in SQL
  3. SQL INSERT INTO Statement
  4. SQL – Combining AND, OR and NOT together in where clause
  5. SQL Syntax

About the Author

I have 15 years of experience in the IT industry, working with renowned multinational corporations. Additionally, I have dedicated over a decade to teaching, allowing me to refine my skills in delivering information in a simple and easily understandable manner.

– Chaitanya

Leave a Reply Cancel reply

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

SQL Tutorial

  • SQL Tutorial
  • SQL Introduction
  • SQL Syntax
  • SQL Data Types

SQL Database

  • SQL CREATE DB
  • SQL DROP DB
  • SQL Rename DB
  • SQL USE DB

SQL Queries

  • 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

Copyright © 2012 – 2025 BeginnersBook . Privacy Policy . Sitemap