Filtering Data Using Conditions Joined by AND Operator

In the world of database management, precision is important. Whether we are dealing with customer records, financial transactions, or inventory data, the ability to retrieve specific information quickly is essential. SQL or Structured Query Language provides powerful tools for filtering data from databases. One such tool is the AND operator which allows us to combine multiple conditions to refine our data queries.

In this article, We will learn about How to Filter Data Using Conditions Joined by AND Operator by understanding various examples with the explanation in detail and so on.

AND Operator

• The AND operator in the SQL is a logical operator used to apply multiple conditions in the WHERE clause.
  
• The AND operator ensures that all conditions evaluated to true in a particular row for it to be included in the query results.
  
• Therefore, using the operator AND to add more conditions will return a smaller number of results.
  
• This operator displays only those records where both conditions condition1 and condition2 evaluates to True.

Syntax:

The syntax for using the AND operator in SQL is straightforward:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Here, condition1, condition2, etc., represent the individual conditions that must all evaluate to true for a row to be selected.

Example Queries of Filtering Data Using Conditions Joined by AND Operator

Let's create a table named as Employees and then insert some data into it.

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

-- Insert some sample data
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department, Salary, YearsOfExperience)
VALUES 
    (1, 'Minal', 'Pandey', 'IT', 60000, 7),
    (2, 'Mahi', 'Pandey', 'HR', 55000, 6),
    (3, 'Soni', 'Pandey', 'IT', 65000, 8),
    (4, 'Abhilekh', 'Pandey', 'Marketing', 50000, 4),
    (5, 'Sudarshan', 'Pandey', 'IT', 70000, 9);

Output:

Example 1: Write a query to select employees from the IT department with a salary greater than $60,000.

Query:

SELECT *
FROM Employees
WHERE Department = 'IT' AND Salary > 60000;

Output:

Explanation:

• SELECT * FROM Employees: It refers to selecting all the columns from the employees table.
  
• WHERE Department = 'IT' AND Salary > 60000: This statement determines the criteria which are required to be fulfilled by each row in the resultant output. The query indicates the department column should read 'IT' and the salary should be above $60,000.

Example 2: Write a query to select employees with more than 5 years of experience and a salary between $50,000 and $70,000.

Query:

SELECT *
FROM Employees
WHERE YearsOfExperience > 5 AND Salary BETWEEN 50000 AND 70000;

Output:

Explanation:

• SELECT * FROM Employees: It refers to selecting all the columns from the employees table.
  
• WHERE YearsOfExperience > 5 AND Salary BETWEEN 50000 AND 70000: This part sets the conditions that each row must meet to be included in the result set. It specifies that the Years of experience is greater than 5, and the Salary is in between 50000 and 70000.

Example 3: Write a query to select employees from the IT department with a salary greater than $60,000 and more than 7 years of experience.

Query:

SELECT *
FROM Employees
WHERE Department = 'IT' AND Salary > 60000 AND YearsOfExperience > 7;

Output:

Explanation:

• SELECT * FROM Employees: It refers to selecting all the columns from the employees table.
  
• WHERE Department = 'IT' AND Salary > 60000 AND YearsOfExperience > 7: This part sets the conditions that each row must meet to be included in the result set. It specifies that the Department column must have the value 'IT', and the Salary column must be greater than 60,000 and Years of experience is greater than 7.

Example 4: Write a query to select employees from the HR department with a salary greater than $50,000 and less than 7 years of experience.

Query:

SELECT *
FROM Employees
WHERE Department = 'HR' AND Salary > 50000 AND YearsOfExperience < 7;

Output:

Explanation:

• SELECT * FROM Employees: It refers to selecting all the columns from the employees table.
  
• WHERE Department = 'HR' AND Salary > 50000 AND YearsOfExperience < 7: This part sets the conditions that each row must meet to be included in the result set. It specifies that the Department column must have the value 'HR', and the Salary column must be greater than 50,000 and Years of experience is less than 7.

Example 5: Write a query to list all employees from the Marketing department who are earning between $45,000 and $55,000, and have over three years of experience.

Query:

SELECT *
FROM Employees
WHERE Department = 'Marketing' AND Salary BETWEEN 45000 AND 55000 AND YearsOfExperience >= 3;

Output:

Explanation:

• SELECT * FROM Employees: It means selecting all the columns from the employees table.
  
• WHERE Department = 'Marketing' AND Salary BETWEEN 45000 AND 55000 AND YearsOfExperience >= 3: In this section, we specify which criteria each row must satisfy in order to be included in the result set. It declares the Department must be Marketing, and the Salary is between 45000 and 55000 and Years of experience is less than or equal to 3.

Conclusion

Overall, The AND operator in SQL must be mastered before writing queries to get accurate and precise results. You can filter data in a precise way by logically combining multiple conditions applied with AND thus only the appropriate records are received. Whether you are looking for customer data, product inventory, or employee records, knowing how to effectively use the AND operator will enable you to extract usable information from the database quickly.