totn MariaDB

MariaDB: AND Condition

This MariaDB tutorial explains how to use the MariaDB AND condition with syntax and examples.

Description

The MariaDB AND condition (also called the AND Operator) is used to test two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the AND condition in MariaDB is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

condition1, condition2, ... condition_n
All of the conditions that must be met for the records to be selected.

Note

  • The MariaDB AND condition allows you to test 2 or more conditions.
  • The MariaDB AND condition requires that all of the conditions (ie: condition1, condition2, condition_n) must be met for the record to be included in the result set.

Example - With SELECT Statement

Let's look at how to use the AND condition within a SELECT statement in MariaDB.

For example:

SELECT *
FROM sites
WHERE site_name = 'TechOnTheNet.com';
AND site_id <= 500;

This MariaDB AND example would return all sites that have a site_name of 'TechOnTheNet.com' and a site_id that is less than or equal to 500. Because the * is used in the SELECT statement, all fields from the sites table would appear in the result set.

Example - JOINING Tables

Let's look at how to use the AND condition in MariaDB to join multiple tables in a SELECT statement.

For example:

SELECT pages.page_id, sites.site_name
FROM sites, pages
WHERE sites.site_id = pages.site_id
AND sites.site_name = 'TechOnTheNet.com'
ORDER BY pages.page_id;

This MariaDB AND condition example would return all rows where the site_name is 'TechOnTheNet.com'. And the sites and pages tables are joined on site_id. You will notice that all of the fields are prefixed with the table names (ie: pages.page_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both the sites and the pages tables.

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT pages.page_id, sites.site_name
FROM sites
INNER JOIN pages
ON sites.site_id = pages.site_id
WHERE sites.site_name = 'TechOnTheNet.com'
ORDER BY pages.page_id;

Example - With INSERT Statement

Let's look at how to use the AND 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'
AND site_id > 43;

This MariaDB AND condition 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 the site_id is greater than 43.

Example - With UPDATE Statement

Let's look at how to use the AND condition in the UPDATE statement in MariaDB.

For example:

UPDATE sites
SET site_name = 'TechOnTheNet.com'
WHERE site_name = 'CheckYourMath.com'
AND site_id <= 10;

This MariaDB AND condition example would update all site_name values in the sites table to 'TechOnTheNet.com' where the site_name is 'CheckYourMath.com' and the site_id is less than or equal to 10.

Example - With DELETE Statement

Finally, let's look at how to use the AND condition in the DELETE statement in MariaDB.

For example:

DELETE FROM sites
WHERE site_name = 'TechOnTheNet.com'
AND site_id = 65;

This MariaDB AND condition example would delete all records from the sites table where the site_name is 'TechOnTheNet.com' and the site_id is equal to 65

Share on: