How to Filter Data Using Conditions Joined by AND Operator

In the field of data analysis and database processing, efficient filtering is critical to obtain significant information. Filtering is based on the selection of data where the data criteria are applied. One commonly employed method is using the AND operator to join multiple conditions, allowing for more precise and granular filtering.

This article delves into the concept of filtering data using conditions joined by the AND operator, exploring its significance and example queries for filtering data.

AND Operator

AND operator is a logical conjunction that performs a combined condition operation involving two or more conditions in a filtering process. It provides the filtering of the data retrieved by establishing that all specified conditions should be met for the record to appear in the result set. Consequently, the AND operator reduces the results by asking each condition to be truthful at the same time.

Creating Table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

Insert data into it:

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES 
    ('Minal', 'Pandey', 'HR', 50000.00),
    ('Mahi', 'Pandey', 'IT', 60000.00),
    ('Soni', 'Pandey', 'HR', 55000.00),
    ('Abhilekh', 'Pandey', 'Finance', 70000.00),
    ('Sudarshan', 'Pandey', 'IT', 65000.00);

Output:

Employees Table:

Now, let's demonstrate some examples of filtering data using conditions joined by the AND operator:

Example 1: Filtering by Department and Salary Range

Query:

SELECT * FROM Employees
WHERE Department = 'HR'
AND Salary > 50000.00;

Explanation:

• SELECT * FROM Employees: This statement indicates that you want to retrieve all the columns from the employees table.
• WHERE Department = 'HR' AND Salary > 50000.00: This is the condition that specifies the department is HR and salary is greater than 50000.00.
  

Output:

This query checks those employees who are in HR department and their salary is higher than 50 K.

Example 2: Filtering by FirstName and LastName

Query:

SELECT * FROM Employees
WHERE FirstName = 'Minal'
AND LastName = 'Pandey';

Explanation:

• SELECT * FROM Employees: This statement indicates that you want to retrieve all the columns from the employees table.
• WHERE FirstName = 'Minal' AND LastName = 'Pandey': This is the condition that specifies the first name is minal and last name is Pandey.

Output:

This query checks those employees whose first name is 'Minal' and last name is 'Pandey'.

Example 3: Filtering by Department, Salary Range, and LastName

Query:

SELECT * FROM Employees
WHERE Department = 'Finance'
AND Salary BETWEEN 60000.00 AND 80000.00
AND LastName LIKE 'P%';

Explanation:

• SELECT * FROM Employees: This statement indicates that you want to retrieve all the columns from the employees table.
• WHERE Department = 'Finance' AND Salary BETWEEN 60000.00 AND 80000.00 AND LastName LIKE 'P%': This is the condition that specifies the department is Finance and salary is between 60000.00 and 80000.00 and last name starts with P.

Output:

This query retrieves employees who work in the Finance department, have a salary between 60,000 and 80,000, and whose last name starts with the letter 'P'.

Example 4: Filtering by Department and FirstName

Query:

SELECT * FROM Employees
WHERE Department = 'HR'
AND FirstName = 'Minal';

Explanation:

• SELECT * FROM Employees: This statement indicates that you want to retrieve all the columns from the employees table.
• WHERE Department = 'HR' AND FirstName = 'Minal': This is the condition that specifies the department is HR and first name is Minal.
  

Output:

This query retrieves the employee who works in the HR department and whose first name is 'Minal'.

Example 5: Filtering by Salary Range and Department

Query:

SELECT * FROM Employees
WHERE Salary >= 60000.00
AND Salary <= 70000.00
AND Department = 'Finance';

Explanation:

• SELECT * FROM Employees: It specifies that you want to select all the columns from the employees table.
• WHERE Salary >= 60000.00 AND Salary <= 70000.00 AND Department = 'Finance': This is the condition that specifies the salary is greater than equal to 60000.00 and less than equal to 70000.00 and department equal to Finance.

Output:

This query retrieves employees who work in the Finance department and have a salary between 60,000 and 70,000.

Conclusion

Filtering data using conditions joined by the AND operator is a fundamental aspect of data analysis and database querying. By specifying multiple criteria and requiring all conditions to be met simultaneously, the AND operator enables precise data retrieval tailored to specific requirements. Understanding its application and adhering to best practices ensure efficient and effective filtering, ultimately facilitating informed decision-making and actionable insights in diverse domains.