totn MariaDB

MariaDB: Combining the AND and OR Conditions

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

Description

The MariaDB AND condition and OR condition can be combined 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 AND condition and OR condition together in MariaDB 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 MariaDB 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 shows how to combine the AND and OR conditions in a SELECT statement in MariaDB.

For example:

SELECT *
FROM sites
WHERE (site_id > 10 AND site_id < 50)
OR site_name = 'TechOnTheNet.com';

This AND & OR example would return all sites that have a site_id greater than 10 and less than 50 as well as all sites that have a site_name of 'TechOnTheNet.com. 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 site_id, site_name, server_name
FROM sites
WHERE (site_id > 10 AND site_id < 50)
OR site_name in ('TechOnTheNet.com', 'CheckYourMath.com')
OR site_id = 89;

This AND & OR example would return all site_id, site_name, and server_name values from the sites table where the site_id is greater than 10 and less than 50 OR whose site_name either is 'TechOnTheNet.com' or 'CheckYourMath.com' OR the site_id is 89.

Example - With INSERT Statement

Let's look at how to combine the AND condition and OR condition in the INSERT statement in MariaDB.

For example:

INSERT INTO contacts
(contact_id, contact_name)
SELECT site_id, site_name
FROM sites
WHERE site_name = 'TechOnTheNet.com'
OR (site_id = 5 AND server_name = 'MyServer');

This MariaDB AND and OR example would insert into the contacts table, all site_id and site_name records from the sites table where the site_name is 'TechOnTheNet.com' and where the site_id is equal to 5 and the server_name is 'MyServer'.

Example - With UPDATE Statement

Next, let's look at an example that shows how to combine the AND & OR conditions in the UPDATE statement in MariaDB.

For example:

UPDATE sites
SET site_name = 'TechOnTheNet.com'
WHERE site_id = 6
OR (site_id > 100 AND server_name = 'MyServer');

This MariaDB AND & OR condition example would update all site_name values in the sites table to 'TechOnTheNet.com' where either the site_id is equal to 6 OR the site_id is greater than 100 and the server_name is 'MyServer'.

Example - With DELETE Statement

Finally, let's look at how to combine the AND & OR conditions demonstrates how the AND and OR conditions in the DELETE statement in MariaDB.

For example:

DELETE FROM sites
WHERE site_id < 10
AND (site_name = 'TechOnTheNet.com' OR site_name = 'CheckYourMath.com');

This MariaDB AND and OR condition example would delete all records from the sites table where the site_id is less than 10 and the site_name is either 'TechOnTheNet.com' or 'CheckYourMath.com'.

Share on: