SQL Operators - How to Use AND OR NOT Operators in SQL?

This recipe covers the different types of SQL operators to become a proficient data handler in no time.

Recipe Objective - SQL Operators - How to Use AND OR NOT Operators in SQL? 

SQL operators play a crucial role in database management by enabling users to perform various operations on data. Among the essential operators are the logical operators AND, OR, and NOT. Check out this recipe to explore how to use these operators in SQL to enhance query capabilities and retrieve specific data sets.

What are the Types of Operators in SQL? 

SQL operators can be broadly categorized into several types, including arithmetic operators (e.g., +, -, *, /), comparison operators (e.g., =, <>, <, >), logical operators (e.g., AND, OR, NOT), and bitwise operators (e.g., &, |, ^). Let’s explore each of them below: 

Logical Operators in SQL 

Logical operators in SQL are used to combine conditions in WHERE clauses. The three primary logical operators are AND, OR, and NOT.

AND Operator in SQL

The AND operator is employed to retrieve records that meet multiple conditions simultaneously. For example:

Syntax:
SELECT column_name1, column_name2, ... FROM table_name
WHERE condition1 AND condition2 AND condition3 AND...;

Example Code:

SELECT * FROM customers WHERE city = "Sheffield" AND country="England"; 

AND Operator in SQL

Output:

+-------------+---------------+------+-----------+---------+----------+

| customer_id | customer_name | age  | city      | country | income   |

+-------------+---------------+------+-----------+---------+----------+

|         105 | May Carleton  |   29 | Sheffield | England | 78000000 |

+-------------+---------------+------+-----------+---------+----------+

1 row in set (0.00 sec)

Ready to apply your SQL skills in real-world scenarios? Explore our hands-on SQL projects and elevate your expertise.

OR Operator in SQL

The OR operator is utilized to fetch records that satisfy at least one of the specified conditions. OR operator retrieves data that satisfy any one of the mentioned conditions.

Syntax:
SELECT column_name1, column_name2, ... FROM table_name
WHERE condition1 OR condition2 OR condition3 OR...;

Example Code:

SELECT * FROM customers WHERE customer_id = 101 OR age < 28; 

OR Operator in SQL

 Output:

+-------------+---------------+------+------------+---------+----------+

| customer_id | customer_name | age  | city       | country | income   |

+-------------+---------------+------+------------+---------+----------+

|         101 | Thomas Shelby |   30 | Birmingham | England | 90000000 |

|         104 | Michael Gray  |   22 | New York   | USA     | 56000000 |

+-------------+---------------+------+------------+---------+----------+

2 rows in set (0.00 sec)

NOT Operator in SQL

NOT operator retrieves data that does not satisfy the mentioned conditions.

Syntax:

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

Code:

SELECT customer_name FROM customers WHERE NOT country="England";

OR Operator in SQL

Output:

+---------------+

| customer_name |

+---------------+

| Grace Burgess |

| Michael Gray  |

+---------------+

2 rows in set (0.00 sec)

Take your SQL skills to the next level by working on practical projects! 

SQL Comparison Operators

Comparison operators in SQL are essential for filtering and retrieving data based on specified conditions. These operators allow you to compare values in different ways, making it possible to create dynamic and precise queries.

Examples of Comparison Operators in SQL 

SELECT * FROM employees WHERE department = 'IT';

This query retrieves all records from the employees table where the department is equal to 'IT'.

SELECT * FROM employees WHERE department <> 'HR';

This query fetches records where the department is not equal to 'HR'.

SELECT * FROM employees WHERE salary > 50000;

This query returns records where the salary is greater than 50000.

These operators provide the foundation for constructing conditions in the WHERE clause, enabling you to filter data based on various criteria.

SQL Set Operators

Set operators in SQL are used to combine or subtract the results of two queries. These operations are particularly useful when you need to analyze relationships between datasets or create composite result sets.

Examples of Set Operators

UNION

SELECT employee_id FROM department1

UNION

SELECT employee_id FROM department2;

This query combines the employee IDs from department1 and department2 and removes any duplicates.

INTERSECT

SELECT employee_id FROM department1

INTERSECT

SELECT employee_id FROM department2;

This query returns the common employee IDs between department1 and department2.

EXCEPT (or MINUS in some databases)

SELECT employee_id FROM department1

EXCEPT

SELECT employee_id FROM department2;

This query returns the employee IDs from department1 that are not present in department2.

Set operators offer a powerful way to manipulate and analyze data from multiple sources, facilitating complex queries.

SQL Arithmetic Operators

Arithmetic operators in SQL enable you to perform mathematical calculations on numeric data within your queries. These operators support basic arithmetic operations such as addition, subtraction, multiplication, division, and modulo.

Examples of Arithmetic Operators

Addition (+)

SELECT salary + bonus AS total_income FROM employees;

This query calculates the total income by adding the salary and bonus for each employee.

Subtraction (-)

SELECT expenses - income AS profit_loss FROM financial_data;

This query calculates the profit or loss by subtracting expenses from income.

Multiplication (*), Division (/), Modulo (%) 

SELECT quantity * price AS total_cost FROM products;

This query calculates the total cost by multiplying quantity and price.

Arithmetic operators are crucial for performing calculations directly within SQL queries, providing flexibility in data analysis.

SQL Bitwise Operators

Bitwise operators in SQL operate at the bit level and are used for binary calculations. While not as commonly used as other types of operators, they can be powerful tools for specific scenarios, especially in system-level programming.

Examples of Bitwise Operators:

AND (&)

SELECT num1 & num2 AS result FROM bitwise_data;

This query performs a bitwise AND operation on num1 and num2.

OR (|), XOR (^)

SELECT num1 | num2 AS result FROM bitwise_data;

This query performs a bitwise OR operation on num1 and num2.

NOT (~)

SELECT ~num1 AS result FROM bitwise_data;

This query performs a bitwise NOT operation on num1.

Bitwise operators are typically used in scenarios where data is stored or manipulated at the binary level, such as in certain encryption algorithms or low-level system programming. They may not be commonly encountered in everyday SQL queries but provide powerful functionality when needed.  

Learn more about SQL Operators Through Hands-on Experience by ProjectPro!  

SQL Operators such as AND, OR, and NOT operators, are crucial for effective database querying. However, true proficiency comes from hands-on experience, and ProjectPro offers a unique opportunity to solidify your skills through real-world projects. With a diverse repository of over 270+ projects centered around data science and big data, ProjectPro serves as the ultimate platform to bridge the gap between theoretical knowledge and practical application. Elevate your SQL expertise by immersing yourself in these projects and unlock the full potential of SQL operators with ProjectPro's comprehensive learning experience. 

What Users are saying..

profile image

Ray han

Tech Leader | Stanford / Yale University
linkedin profile url

I think that they are fantastic. I attended Yale and Stanford and have worked at Honeywell,Oracle, and Arthur Andersen(Accenture) in the US. I have taken Big Data and Hadoop,NoSQL, Spark, Hadoop... Read More

Relevant Projects

Deep Learning Project for Beginners with Source Code Part 1
Learn to implement deep neural networks in Python .

MLOps Project to Build Search Relevancy Algorithm with SBERT
In this MLOps SBERT project you will learn to build and deploy an accurate and scalable search algorithm on AWS using SBERT and ANNOY to enhance search relevancy in news articles.

Build a Logistic Regression Model in Python from Scratch
Regression project to implement logistic regression in python from scratch on streaming app data.

NLP and Deep Learning For Fake News Classification in Python
In this project you will use Python to implement various machine learning methods( RNN, LSTM, GRU) for fake news classification.

LLM Project to Build and Fine Tune a Large Language Model
In this LLM project for beginners, you will learn to build a knowledge-grounded chatbot using LLM's and learn how to fine tune it.

Hands-On Approach to Regression Discontinuity Design Python
In this machine learning project, you will learn to implement Regression Discontinuity Design Example in Python to determine the effect of age on Mortality Rate in Python.

Build an Image Segmentation Model using Amazon SageMaker
In this Machine Learning Project, you will learn to implement the UNet Architecture and build an Image Segmentation Model using Amazon SageMaker

Deploy Transformer-BART Model on Paperspace Cloud
In this MLOps Project you will learn how to deploy a Tranaformer BART Model for Abstractive Text Summarization on Paperspace Private Cloud

Llama2 Project for MetaData Generation using FAISS and RAGs
In this LLM Llama2 Project, you will automate metadata generation using Llama2, RAGs, and AWS to reduce manual efforts.

House Price Prediction Project using Machine Learning in Python
Use the Zillow Zestimate Dataset to build a machine learning model for house price prediction.