Types of Logical Operators (Between , IN, etc) in SQL DBMS

Logical operators in SQL allow users to combine multiple conditions to form more complex query criteria.

Types of Logical Operators in DBMS

To choose values from a range of values, use the BETWEEN operator.
It retrieves records where a column's value is between two specified values (inclusive).
For example: The BETWEEN operator is used to filter results within a specific range of values.
It is commonly used in WHERE clauses to retrieve records with values falling between two specified endpoints.
Syntax: The syntax is column_name BETWEEN value1 AND value2, where value1 and value2 define the range.

1SELECT column
2FROM table
3WHERE column BETWEEN value1 AND value2;

for example:

1SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 50000;

This query fetches employees whose salary is between $30,000 and $50,000.

The IN operator is used to match a value against a set of specified values.
It retrieves records where a column's value matches any of the specified values. For example:
The IN operator is used to match values against a list or set of specified values.
It is useful for filtering records where a column's value matches any value in a given list.
Syntax: The syntax is column_name IN (value1, value2, ...) to check if the column value is present in the specified list.

1SELECT column
2FROM table
3WHERE column IN (value1, value2, ...);

Example : This query fetches products belonging to the 'Electronics', 'Clothing', or 'Books' categories.

The AND operator is used to combine multiple conditions, and it retrieves records that satisfy all the specified conditions. For example:
The AND operator is employed to merge several conditions within a WHERE clause.
Usage: It requires all conditions to be true for a record to be included in the result set.
Syntax: The syntax is condition1 AND condition2 to check if both conditions are true.

1SELECT column
2FROM table
3WHERE condition1 AND condition2;

Example

1SELECT * FROM Products WHERE Category = 'Electronics' AND Price < 1000;

products from the "Products" table where the category is 'Electronics' AND the price is less than $1000.
The AND operator ensures that both conditions (category and price) must be true for a product to be included in the result set.

The OR operator is used to combine multiple conditions, and it retrieves records that satisfy at least one of the specified conditions. For example:
The OR operator is used to combine multiple conditions in a WHERE clause.
It requires at least one condition to be true for a record to be included in the result set.
syntax : condition1 OR condition2 to check if either condition1 or condition2 is true.

1SELECT column
2FROM table
3WHERE condition1 OR condition2;

Example:

1SELECT * FROM Products WHERE Category = 'Electronics' OR Price > 500;

This query fetches products that are either in the 'Electronics' category or have a price greater than $500.

A condition is negated using the NOT operator.It retrieves records that do not satisfy the specified condition. For example:
The NOT operator is used to negate a condition in a WHERE clause.
It reverses the logic of a condition, returning records where the condition is false.
Syntax: The syntax is NOT condition to check if the condition is not true.

1SELECT column
2FROM table
3WHERE NOT condition;

Example:

1SELECT * FROM Customers WHERE NOT Country = 'INDIA';

This query fetches customers who are not from the INDIA.

In SQL, null values stand for missing or ambiguous data.
Dealing with null values requires special consideration to ensure accurate and meaningful query results.
Here are two common approaches for handling null values:

One approach is to disallow null values in specific columns by defining them as "NOT NULL" during table creation.
This ensures that those columns always contain valid data and avoids potential issues when querying or performing calculations.

When comparing values that may include nulls, special care is needed.
SQL uses the "IS NULL" and "IS NOT NULL" operators to check for null values explicitly.

For example:

1SELECT column
2FROM table
3WHERE column IS NULL;

Now we have basic understanding of Types of Logical Operators in SQL