SQL Logical Operators

Introduction

In this blog, we are going to cover the logical operators in SQL. Firstly we need to know what exactly the logical operators are used for?

Logical operators are used to create complex query conditions.

If at all we are not satisfied with the conditions that we create using Relational operators or Arithmetic operators in real world then here comes the logical operators to enhance the capabilities of querying the data.

Types of logical operators in SQL

And
Or
Is Null
Not
Like
In
Between

SQL Table with Sample Data

CREATE TABLE  "AGENTS" 
(	
    "AGENT_CODE" NVARCHAR(10) NOT NULL PRIMARY KEY, 
	"AGENT_NAME" NVARCHAR(30), 
	"WORKING_AREA" NVARCHAR(30), 
	"COMMISSION" bigint, 
	"PHONE_NO" NVARCHAR(20), 
	"COUNTRY" NVARCHAR(20) 
);

Add some Data to the newly created table

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', 1, '077-25814763', null);
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', 2, '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', 3, '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', 4, '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', 5, '007-22388644', null);
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', 6, '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', 7, '045-21447739', null);
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', 8, '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', 9, '029-12358964', null);
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', 10, '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', 11, '008-22544166', null);
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', 12, '008-22536178', '');

1. AND

In the SQL Query if all the conditions are passed true then only will be able to get the result as true.

Select * from Agents
 Where working_area = 'Bangalore' AND AGENT_NAME = 'Ramasundar'

Result

2. OR

In the SQL Query irrespective of my conditions as we have atleast one of the conditions should be true.

Select * from Agents
 Where working_area = 'Bangalore' OR AGENT_NAME = 'Ramasundar'

Result

3. Is Null

The operator Is Null allows us to detect all rows that have null values in some of the columns.

Select * from Agents
 Where country  IS NULL

Result

4. Not

The Not Logical operator in the SQL checks if the condition is not true.

Select * from Agents
 Where working_area not in('Bangalore')

Result

5. Like

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

Let's fetch the data where agent name starts with letter A from the table

Select * from Agents
 Where agent_name like 'A%'

Result

6. In

The In operator allows us to perform the check based on the list of values added between the parenthesis - () and returns the result if the condition matches true.

Select * from Agents
 Where agent_name in ('Alex','Mukesh','Ravi Kumar')

Result

7. Between

The Between operator works as the name it says. It will fetch the in between values according to the condition and for that we must have to use the AND operator as well.

select * from agents
where commission between 5 and 10

Result

Conclusion

Thank you for reading, I hope this article gives you a brief idea about the SQL Logical operators and types of operators with clear code samples.

Please let me know your questions, thoughts, or feedback in the comments section. I appreciate your feedback and encouragement.

Keep learning ...!