SQL AND

What is SQL AND?

The SQL AND operator is a logical condition used to filter data based on multiple criteria. When you use AND in a query, it requires that all specified conditions must be met for a row to be included in the result set. It's like finding the sweet spot where multiple conditions align perfectly.

How Does SQL AND Work?

To understand SQL AND better, let's look at its syntax:

SELECT columns
FROM tables
WHERE condition 1 AND condition 2;

When you use the AND operator, it's like adding an additional layer of scrutiny. Only rows that satisfy both "condition 1" and "condition 2" will be returned. This is incredibly useful when you need precise and specific data from your database.

SQL AND in SELECT Statements

Let's explore how SQL AND works in different SQL statements, starting with the SELECT statement. Consider the following example:

Example 1:

SELECT * FROM emp WHERE Department = "IT" AND Location = "USA";

In this query, we're retrieving records from the 'emp' table where the department is 'IT' and the location is 'USA'. As a result, only the rows that meet both criteria are returned.

Example 2:

SELECT * FROM emp WHERE Department = "IT" AND Location = "Mumbai";

This query fetches records where the department is 'IT' and the location is 'Mumbai'. Here, we have a single row that fulfills both conditions.

SQL AND in UPDATE Statements

The SQL AND operator is not limited to SELECT statements; you can also use it in UPDATE statements to modify specific records. Let's see this in action:

Example 1:

UPDATE emp SET Location = "USA" WHERE Department = "Marketing" AND First_Name = "ANNE";

In this query, we're updating the location for an employee in the 'Marketing' department with the first name 'ANNE' to 'USA'. Again, the AND condition ensures that both criteria are met for the update to occur.

Example 2:

UPDATE emp SET Department = "IT" WHERE Department = "Finance" AND ID = 4;

In this case, we're changing the department of an employee from 'Finance' to 'IT' but only if the department is 'Finance' and the employee's ID is 4.

SQL AND in DELETE Statements

SQL AND is a handy tool for DELETE statements, allowing you to remove specific records that meet multiple conditions. Let's take a look at examples:

Example 1:

DELETE FROM emp WHERE Last_Name = 'ANNE' AND Location = 'Colombo';

In this query, we are deleting a record from the 'emp' table if the last name is 'ANNE' and the location is 'Colombo.' The AND condition ensures that both criteria are satisfied for deletion.

Example 2:

DELETE FROM emp WHERE Department = 'IT' AND Location = 'Sydney';

Here, we are removing records from the 'emp' table with a department of 'IT' and a location of 'Sydney.' The AND operator guarantees that only records meeting both conditions will be deleted.