☄ EXPOLORE MIND ⚜ Tutorial ? JIQA Biography News
PRV MENU NEXT

Combining the AND and OR Conditions

Course- SQL >

This SQL tutorial explains how to use the AND condition and the OR condition together in a single query with syntax and examples.

Description

The SQL AND condition and OR condition can be combined to test for multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use parentheses so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

Syntax

The syntax for the SQL AND condition and OR condition together is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n
The conditions that are evaluated to determine if the records will be selected.

Note

  • The SQL AND & OR conditions allow you to test multiple conditions.
  • Don't forget the order of operation parentheses!

Example - With SELECT Statement

Let's look at an example that combines the AND condition and OR condition in a SELECT query.

For example:

SELECT *
FROM suppliers
WHERE (city = 'New York' AND name = 'IBM')
OR (ranking >= 10);

This SQL SELECT example would return all suppliers that reside in New York whose name is IBM and all suppliers whose ranking is greater than or equal to 10. The parentheses determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
OR (name = 'Hewlett Packard' AND city = 'Atlantic City')
OR (name = 'Gateway' AND status = 'Active' AND city = 'Burma');

This SQL SELECT statement would return all supplier_id values where the supplier's name is IBM or the name is Hewlett Packard and the city is Atlantic City or the name is Gateway, the status is Active, and the city is Burma.

Example - With INSERT Statement

This next example demonstrates how the SQL AND condition and SQL OR condition can be combined in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE (customer_name = 'IBM' OR customer_name = 'Apple')
AND employees > 15;

This SQL AND and OR condition example would insert into the suppliers table, all account_no and customer_name records from the customers table whose customer_name is either IBM or Apple and where the employees is greater than 15.

Example - With UPDATE Statement

This example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'HP'
WHERE supplier_name = 'IBM'
OR (state = 'California' AND supplier_id = 50);

This SQL AND & OR condition example would update all supplier_name values in the suppliers table to HP where the supplier_name was IBM or the state is California and the supplier_id is equal to 50.

Example - With DELETE Statement

Finally, this last AND & OR condition example demonstrates how the AND and OR condition can be used in the DELETE statement.

For example:

DELETE FROM suppliers
WHERE city = 'New York'
AND (product = 'PC computers' OR supplier_name = 'Dell');

This SQL AND and OR condition example would delete all suppliers from the suppliers table whose city was New York and either the product was PC computers or the supplier name was Dell.

PRV MENU NEXT