Updated March 23, 2023
Introduction to AND in SQL
While working on data in SQL, many times, an analyst comes across a situation where data needs to be filtered. Filtering is a very common part of data preparation and in SQL it is possible through certain logical operators. One amongst such operators is the AND operator. As the name suggests it takes into consideration all the conditions that are associated with it. For obtaining requisite data specify the conditions in the WHERE clause separated by the AND operator. If conditions and the operator are employed properly then results will be returned only for those records which meet the conditions.
Syntax:
The syntax for AND operator in SQL is as follows:
SELECT column_1, column_2, ..., column_n
FROM table_name
WHERE condition_1 AND condition_2 AND …. condition_nHow AND Works in SQL?
The AND operator in SQL checks if all conditions by the operator are TRUE and return the records which satisfy these criteria. Technically the AND operator means “TRUE if both Boolean expressions are TRUE”. For those records which are satisfying the conditions, requisite fields are returned. In a way, the AND operator acts as a filter, by allowing us to get only that portion of the data which we find meaning in the context. Here, another important thing is the order of execution of the SQL query. The WHERE clause stands higher in the order and so, the AND operator comes into the picture earlier in the query processing that actually facilitates the task of filtering.
Examples to Implement AND in SQL
The examples of AND in SQL are given below:
Example #1
For this demonstration, we are considering the “customers” dataset. The dataset contains various details pertaining to the customers, such as their names, contact details, and credit limits. Our task is to obtain only records for those customers who belong to France with a credit limit of more than or equal to 75000. Here, we have two conditions which are, first the customer should belong to a particular country i.e. France in this case. Once this condition is satisfied, the next task is to identify those customers amongst all the customers from France for whom the credit limit is either 75000 or exceeds 75000. In order to combine these two conditions, and obtain the requisite data, we must employ the AND operator.
Code:
SELECT * FROM customers
WHERE creditLimit > 75000 AND country = 'France';Output:
What if we are just interested in knowing the first name and the last name of the customers who satisfy these two criteria? We will rewrite the above query by mentioning explicitly only those column names which we are interested in as below.
Code:
SELECT contactFirstName 'First Name', contactLastName 'Last Name’
FROM customers
WHERE creditLimit > 75000 AND country = 'France';As we can see above, contact first name means the first name of the customer. Similarly, contact the last name means the last name of the customer. Note, now, the AND operator will check if both the conditions are true and return the first and last names from the corresponding records.
Output:
We can bring as many fields in the output as desired. Like, in the above query we can also, bring the credit limit along with names.
Example #2
In this demonstration, we would like to obtain the name of those bikes which have a price of greater than or equal to 50 units. The product dataset consists of various fields including product code, product name, product line, product vendor, product description, quantityInStock, and buy price. Here, the most important field is the product line. The dataset contains details for various vehicle product lines such as Classic Cars, Motor Cycles, Planes, Ships, Trains, Trucks and Buses, and Vintage Cars. As we are interested in knowing the price of bikes, we will choose “Motorcycles” from the product line field. By doing this we filter the data on the first criteria. Now, the second task is to identify the bikes which have a price greater than or equal to 50 units. So, we’ll add these two conditions together using the AND operator in the WHERE clause, and the query for obtaining such bikes is as follows.
Code:
SELECT productName 'Product Name', buyPrice 'Price'
FROM products
WHERE productLine = 'Motorcycles' AND buyPrice >= 50</code?Output:
In the above query, we have given an alias to productName and buy price fields. The output will have these two fields. We will update the query little by adding the product vendor field to it. The updated query is as shown below.
Code:
SELECT productName 'Product Name', productVendor 'Product Vendor', buyPrice 'Price'
FROM products
WHERE productLine = 'Motorcycles' AND buyPrice >= 50;Note the above query will give us details about those bikes which follow certain price criteria. Most important, now, we get to see which vendors provide which bike along with the price. This is a very important aspect of filtering the data using AND. It is very important that a SQL query should give some insights into the data, and filtering is one of those tools that allow us to dig deeper into data.
Output:
Example #3
In this demonstration, our goal is to obtain the names of those female students who belong to class X. So, essentially, the two conditions are that the student should belong to class X and the gender of the student should be female. The query to obtain requisite data is as written below:
Code:
SELECT student_name 'Name', student_gender 'Gender'
FROM students
WHERE student_class = 'x' AND student_gender = 'Female';Output:
So, we have only two female students in class X.
Conclusion
The AND operator in SQL is a very important operator without which it is not possible to obtain a requisite portion of data. Along with working with the WHERE clause, the operator also works effectively with UPDATE and DELETE. So, while working multiple conditions over data, the AND operator is useful.
Recommended Articles
We hope that this EDUCBA information on “AND in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
