SQL And & OR Clause - Fullstackadda

SQL AND & OR clauses are used to filter records in a SELECT, UPDATE, or DELETE statement. The AND clause returns only those records that meet multiple conditions, while the OR clause returns records that meet at least one of the conditions.

For example, the following SQL query uses the AND clause to select only employees who have a salary greater than 50000 and work in the department with ID 3:

SELECT * FROM employees
WHERE salary > 50000 AND department_id = 3;

Similarly, the following SQL query uses the OR clause to select employees who have a salary greater than 50000 or work in the department with ID 3:

SELECT * FROM employees
WHERE salary > 50000 OR department_id = 3;

It’s important to note that the order of the conditions in the WHERE clause matters when using the AND and OR operators. In most cases, it’s a good practice to use parentheses to clarify the order of precedence of the conditions.

When to use AND & OR clauses in SQL :

The AND and OR clauses in SQL are used to filter records based on multiple conditions. The AND clause is used when you want to return only those records that meet multiple conditions, while the OR clause is used when you want to return records that meet at least one of the conditions.

Here are some examples of when you might use the AND and OR clauses in SQL:

When you want to select employees who have a salary greater than 50000 and work in the department with ID 3:

SELECT * FROM employees
WHERE salary > 50000 AND department_id = 3;

When you want to select employees who have a salary greater than 50000 or work in the department with ID 3:

SELECT * FROM employees
WHERE salary > 50000 OR department_id = 3;

When you want to update the salary of all employees who work in the department with ID 3 or 4:

UPDATE employees
SET salary = salary + 5000
WHERE department_id = 3 OR department_id = 4;

When you want to delete all employees who do not have a manager and who have been with the company for less than 2 years:

DELETE FROM employees
WHERE manager_id IS NULL AND hire_date > '2019-01-01';

SQL And & OR Clause SQL And & OR Clause SQL And & OR Clause