SQL – Logical Operators
SQL logical operators are used to test for the truth of the condition. A logical operator like the Comparison operator returns a boolean value of TRUE, FALSE, or UNKNOWN. In this article, we will discuss different types of Logical Operators.
Logical operators are used to combine or manipulate the conditions given in a query to retrieve or manipulate data .there are some logical operators in SQL like OR, AND etc.
Types of Logical Operators in SQL
Given below is the list of logical operators available in SQL.
Operator | Meaning |
---|---|
AND | TRUE if both Boolean expressions are TRUE. |
IN | TRUE if the operand is equal to one of a list of expressions. |
NOT | Reverses the value of any other Boolean operator. |
OR | TRUE if either Boolean expression is TRUE. |
LIKE | TRUE if the operand matches a pattern. |
BETWEEN | TRUE if the operand is within a range. |
ALL | TRUE if all of a set of comparisons are TRUE. |
ANY | TRUE if any one of a set of comparisons is TRUE. |
EXISTS | TRUE if a subquery contains any rows. |
SOME | TRUE if some of a set of comparisons are TRUE. |
Example:
In the below example, we will see how this logical operator works with the help of creating a database.
Step 1: Creating a Database
In order to create a database, we need to use the CREATE operator.
Query
CREATE DATABASE xstream_db;
Step 2: Create table employee
In this step, we will create the table employee inside the xstream_db database.
Query
CREATE TABLE employee (emp_id INT, emp_name VARCHAR(255),
emp_city VARCHAR(255),
emp_country VARCHAR(255),
PRIMARY KEY (emp_id));
In order to insert the data inside the database, we need to use the INSERT operator.
Query
INSERT INTO employee VALUES (101, 'Utkarsh Tripathi', 'Varanasi', 'India'),
(102, 'Abhinav Singh', 'Varanasi', 'India'),
(103, 'Utkarsh Raghuvanshi', 'Varanasi', 'India'),
(104, 'Utkarsh Singh', 'Allahabad', 'India'),
(105, 'Sudhanshu Yadav', 'Allahabad', 'India'),
(106, 'Ashutosh Kumar', 'Patna', 'India');
Output
Now the given below is the list of different logical operators.
AND Operator
The AND operator is used to combines two or more conditions but if it is true when all the conditions are satisfied.
Query
SELECT * FROM employee WHERE emp_city = 'Allahabad' AND emp_country = 'India';
Output
IN Operator
It is used to remove the multiple OR conditions in SELECT, INSERT, UPDATE, or DELETE. and We can also use NOT IN to minimize the rows in your list and any kind of duplicate entry will be retained.
Query
SELECT * FROM employee WHERE emp_city IN ('Allahabad', 'Patna');
Output
NOT Operator
Query
SELECT * FROM employee WHERE emp_city NOT LIKE 'A%';
Output
OR Operator
The OR operator is used to combines two or more conditions but if it is true when one of the conditions are satisfied.
Query
SELECT * FROM employee WHERE emp_city = 'Varanasi' OR emp_country = 'India';
Output
LIKE Operator
In SQL, the LIKE operator is used in the WHERE clause to search for a specified pattern in a column.
- % – It is used for zero or more than one character.
- _ – It is used for only one character means fixed length.
Query
SELECT * FROM employee WHERE emp_city LIKE 'P%';
Output
BETWEEN Operator
The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive).
Query
SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;
Output
ALL Operator
The ALL operator returns TRUE if all of the subqueries values matches the condition.
All operator is used with SELECT, WHERE, HAVING statement.
Query
SELECT * FROM employee WHERE emp_id = ALL
(SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');
Output
ANY Operator
The ANY operator:
- It returns a boolean value as a result
- It returns TRUE if ANY of the subquery values match the condition
Query
SELECT * FROM employee WHERE emp_id = ANY
(SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');
Output
EXISTS Operator
In SQL,Exists operator is used to check whether the result of a correlated nested query is empty or not.
Exists operator is used with SELECT, UPDATE, INSERT or DELETE statement.
Query
SELECT emp_name FROM employee WHERE EXISTS
(SELECT emp_id FROM employee WHERE emp_city = 'Patna');
Output
SOME Operator
In SQL, SOME operators are issued with comparison operators (<,>,=,<=, etc) to compare the value with the result of a subquery.
Query
SELECT * FROM employee WHERE emp_id < SOME
(SELECT emp_id FROM employee WHERE emp_city = 'Patna');
Output
Now you should have a brief introduction to all SQL logical operators and how to use them to test the truth of a condition. We have discussed all the logical operators Like OR, AND, IN, etc with suitable examples of their output.
Contact Us