Bytes

AND and OR operators in SQL

Module - 7 Filtering in SQL
AND and OR operators in SQL

Overview

The AND and OR operators are used to combine two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement in SQL. The AND operator displays a record if all the conditions separated by AND are TRUE. The OR operator displays a record if any of the conditions separated by OR is TRUE. These operators filter out records that do not satisfy the given criteria.

AND Operator:

The AND operator is a logical operator that combines two or more conditions in a SQL statement.

Syntax:

SELECT * FROM table_name WHERE condition1 AND condition2 and ...conditionN;

table_name: name of the table
condition1,2,..N : first condition, second condition and so on

This statement is used to retrieve specific records from a table. The table_name specifies which table to access, and the conditions are used to specify which records to retrieve from the table. The statement will return all records that match all of the conditions provided.

Example:

Employees

employeeIDfirstNamesalarydepartment
1John60000sales
2Jane40000finance
3Bob80000sales
4Sarah45000marketing
SELECT * FROM Employees WHERE salary > 50000 AND department = 'sales'

Explanation: The above example returns all employees with a salary greater than 50000 and are in the sales department.

OR Operator:

The OR operator is a logical operator in SQL that combines two or more expressions to return true if any of the conditions are true.

Syntax:

SELECT * FROM table_name WHERE condition1 OR condition2 OR... conditionN;

table_name: name of the table
condition1,2,..N : first condition, second condition and so on

This code retrieves records from a table with a certain set of conditions. It will select all data from the table specified by table_name and then filter the results based on the specified conditions (condition1, condition2, etc). The query will return all records that match any of the specified conditions.

Example:

EmployeeIDFirst NameLast Name
1JohnSmith
2JaneSmith
3JohnDoe
4JackSmith
SELECT * FROM employees
WHERE last_name = 'Smith' OR first_name = 'John';

Explanation: This example uses the OR operator to select all employees whose last name is Smith OR whose first name is John. The database will return any records that match the WHERE clause's conditions.

Combining AND and OR:

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition1 AND/OR condition2;

This syntax is utilized to choose certain columns from a table in a database based on numerous conditions. The conditions can be combined utilizing either the AND or the OR operator. The AND operator is utilized to indicate that all conditions must be met, whereas the OR operator is utilized to indicate that at slightest one of the conditions must be met

Example:

CustomerIDNameCityCountry
1JohnBerlinGermany
2JaneBerlinGermany
3JackBerlinGermany
SELECT *
FROM Customers
WHERE City = 'Berlin' AND Country = 'Germany';

This example uses both the AND and OR operators to select records from the "Customers" table where the "City" is equal to "Berlin" and the "Country" is equal to "Germany".

Conclusion

The AND and OR operators can be utilized in SQL to combine numerous Boolean expressions to create a single expression. The AND operator returns genuine in the event that all of its operands are genuine, whereas the OR operator returns genuine on the off chance that at least one of its operands is genuine. Both operators can be utilized to form complex queries to contract down comes about and discover the precise information that's required.

Key takeaways

  1. AND operator combines two or more conditions to narrow down the comes about of a query.
  2. OR operator is utilized to combine two or more conditions to broaden the comes about of a query.
  3. AND operator takes precedence over the OR operator in SQL.
  4. When combining numerous AND and OR operators in a query, the query should be composed clearly and consistently, utilizing parentheses to separate conditions.
  5. AND operator returns records that fulfill all indicated conditions, whereas the OR operator returns records that satisfy at least one specified condition.

Quiz

  1. Which operator is used to combine two or more conditions in SQL? 
    1. AND 
    2. OR  
    3. NOT 
    4. XOR

Answer: A. AND

  1. Which operator is used to retrieve records that meet any of the conditions specified? 
    1. AND 
    2. OR 
    3. NOT  
    4. XOR

Answer: B. OR

  1. Which operator is used to combine two conditions and retrieve records that satisfy either of the conditions?
    1. AND 
    2. OR  
    3. NOT 
    4. XOR

Answer: B. OR

  1. Which operator is used to retrieve records that meet both of the conditions specified?  
    1. AND 
    2. OR  
    3. NOT 
    4. XOR

Answer: A. AND

Online SQL Compiler (SQLite 3 Editor)
Open Compiler
Recommended Courses
Certification in Full Stack Data Science and AI
Course
20,000 people are doing this course
Become a job-ready Data Science professional in 30 weeks. Join the largest tech community in India. Pay only after you get a job above 5 LPA.
Masters Program in Data Science and Artificial Intelligence
Course
20,000 people are doing this course
Join India's best Masters program in Data Science and Artificial Intelligence. Get the best jobs in top tech companies. Accredited by ECTS and globally recognised in EU, US, Canada and 60+ countries.

AlmaBetter’s curriculum is the best curriculum available online. AlmaBetter’s program is engaging, comprehensive, and student-centered. If you are honestly interested in Data Science, you cannot ask for a better platform than AlmaBetter.

avatar
Kamya Malhotra
Statistical Analyst
Fast forward your career in tech with AlmaBetter
Explore Courses

Vikash SrivastavaCo-founder & CPTO AlmaBetter

Vikas CTO

Related Tutorials to watch

view Allview-all

Top Articles toRead

view Allview-all
AlmaBetter
Made with heartin Bengaluru, India
  • Official Address
  • 4th floor, 133/2, Janardhan Towers, Residency Road, Bengaluru, Karnataka, 560025
  • Communication Address
  • 4th floor, 315 Work Avenue, Siddhivinayak Tower, 152, 1st Cross Rd., 1st Block, Koramangala, Bengaluru, Karnataka, 560034
  • Follow Us
  • facebookinstagramlinkedintwitteryoutubetelegram

© 2024 AlmaBetter