SQL - Logical Operators

---

---

In SQL, logical operators are used to create conditional expressions that evaluates to either true or false. They are used in the WHERE clause of SELECT, UPDATE, DELETE, and other SQL statements to filter data based on specified conditions. The logical operators available in SQL are as follows:

In this chapter we will learn about each operator one-by-one with examples showing its usage. Before proceeding further, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Once the table is created, we can insert some values into the table using the following queries −

INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'khilan', 25, 'Delhi', 1500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'Kaushik', 23, 'Kota', 2000.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardhik', 27, 'Bhopal', 8500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'komal', 22, 'MP', 4500.00);
INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', 24, 'Indore', 10000.00 );

Let us verify whether the table is created or not using the following query −

SELECT * FROM CUSTOMERS;

As we can see in the below output, the table has been created in the database.

Now, let us perform all the SQL logical operations using the above table.

SQL ALL Operator

The SQL ALL operator is used to compare a value to a set of values returned by a subquery. It checks if the specified condition holds true for all values in the result set of the subquery. The ALL operator is generally used with comparison operators such as =, >, <, >=, <=, <>, etc.

Example

The following query returns the details of all the customers whose salary is not equal to the salary of any customer whose AGE is 25.

select * from CUSTOMERS where SALARY <> ALL (select SALARY from CUSTOMERS where AGE = 25);

Output

When we execute the above query, the output is obtained as follows −

SQL AND Operator

The SQL AND operator is used to combine multiple conditions in a WHERE clause or a HAVING clause. It allows you to retrieve rows that meet all specified conditions. If all the conditions joined by AND are true for a row, that row will be included in the result set.

Example

In here, we are fetching the ID, Name and Salary of the customers whose salary is greater than 2000 and age is less than 25 years.

SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

Output

When we execute the above query, the output is obtained as follows −

SQL ANY Operator

The SQL ANY operator is used to compare a single value with a set of values returned by a subquery. It checks if the specified condition holds true for at least one value in the result set of the subquery. The ANY operator is generally used with comparison operators such as "=", ">", "<", ">=", "<=", "<>", etc.

Example

Now, let us try to list out the details of all the customers whose salary is greater than the salary of any customer whose AGE is 32 i.e. Chaitali, Hardik, Komal and Muffy in this case.

select * from customers WHERE SALARY > ANY (select SALARY from CUSTOMERS where AGE = 32);

Output

On executing the above query, the output is displayed as follows −

SQL BETWEEN Operator

The SQL BETWEEN operator is used to filter data within a specified range of values. It checks if a value is within the specified lower and upper bounds (inclusive). The BETWEEN operator is used in the WHERE clause of a SQL query to retrieve rows that fall within a particular range.

Example

Here, we are trying to retrieve customers whose age is between 18 and 22.

SELECT * FROM EMPLOYEE WHERE AGE BETWEEN 18 AND 22;

Output

The table for the above query produced as given below −

SQL EXISTS Operator

The SQL EXISTS operator is used to verify whether a particular record exists in a SQL table. While using this operator we need to specify the record (for which you have to check the existence) using a subquery. It is used in the WHERE clause to filter results based on the existence of rows in the subquery. The EXISTS operator returns true if the subquery returns at least one row; otherwise, it returns false.

To understand better, let us create another table CARS, containing the details such as id of the customer, name and price of the car, using the following query −

create table CARS(
ID INT NOT NULL,
NAME VARCHAR(20) NOT NULL,
PRICE INT NOT NULL,
PRIMARY KEY(ID)
);

Using the INSERT statement, let us insert values into this table −

insert INTO CARS VALUES(2, 'Maruti Swift', 450000);
insert INTO CARS VALUES(4, 'VOLVO', 2250000);
insert INTO CARS VALUES(7, 'Toyota', 2400000);

If you try to retrieve the contents of the table using the SELECT query the table is displayed as follows −

Example

Now, let us try to return the lists of the customers with the price of the car greater than 2,000,000.

SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);

Output

Following is the output of the above query −

SQL IN Operator

The SQL IN operator is used to specify a list of values to match against a specific column or expression. It allows us to retrieve rows that have a column value matching any of the values in the specified list. The IN operator is used in the WHERE clause of a SQL query to filter data based on multiple possible values.

Example

In the following query, we are trying to display records with NAME equal to 'Khilan', 'Hardik' and 'Muffy' (string values).

select * from CUSTOMERS WHERE NAME IN ('Khilan', 'Hardhik', 'Muffy');

Output

The output produced is as shown below −

SQL LIKE Operator

The SQL LIKE operator is used to perform pattern matching against character data. It is used in the WHERE clause of a SQL query to filter rows based on specific patterns within a column. The LIKE operator is particularly useful when you want to perform wildcard searches i.e. '%', '_', '[]', '[^]'.

Example

Let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';

Output

Output of the above code is as shown below −

SQL NOT Operator

The SQL NOT operator used to negate a condition. It returns true if the specified condition is false and false if the condition is true. The NOT operator is commonly used to perform negative or inverse filtering in SQL queries.

Example

In the following query, we are retrieving the customers whose salary is not greater than 2000.

SELECT * FROM CUSTOMERS WHERE NOT (SALARY > 2000.00);

Output

Output of the above code is as shown below −

SQL OR Operator

The SQL OR operator is used to combine multiple conditions in a WHERE clause or a HAVING clause. It allows us to retrieve rows that meet at least one of the specified conditions. If any of the conditions joined by the OR operator are true for a row, that row will be included in the result set.

Example

The following query fetches the ID, Name and Salary of the customers whose salary is greater than 2000 or age is less than 25 years.

SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 OR age < 25;

Output

Following is the output of the above query −

SQL IS NULL Operator

The SQL IS NULL operator is used to check whether a column has a null value (having no value). It returns true if the column value is NULL and false if it is not.

Example

Let's consider a table named "Fruit" that we are going to create in our database and which contains some null values in the fields. Execute the below query to create a table.

CREATE TABLE Fruit
(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   ADDRESS  CHAR (25),
   PRICE   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now we are going to populate the above-created table by using the below query.

INSERT INTO Fruit (ID,NAME,ADDRESS,PRICE)
VALUES (1, 'Apple', 'Shimla', 2000.00 );

INSERT INTO Fruit (ID,NAME,ADDRESS,PRICE)
VALUES (2, 'Mango',NULL, 3000.00 );

INSERT INTO Fruit (ID,NAME,ADDRESS,PRICE)
VALUES (3, 'Orange',NULL, 4000.00 );

INSERT INTO Fruit (ID,NAME,ADDRESS,PRICE)
VALUES (4, 'Banana', 'AP',NULL);

INSERT INTO Fruit (ID,NAME,ADDRESS,PRICE)
VALUES (5, 'JackFruit', 'Ooty',NULL);

Verification

To check whether the table is created or not, let's execute the below query.

SELECT * FROM Fruit;

On executing it, it will display a table as shown below −

Now, we are trying to retrieve the list of fruits where address is not provided.

SELECT * FROM Fruit WHERE ADDRESS IS NULL;

Output

Output of the above query is as shown below −