This tutorial will explain how to use AND & OR operators clause in a SQL query and how to use them with the examples.
The AND operator requires the existence of all conditions within that AND group in an SQL statement's WHERE clause.
The OR operator requires the existence of at least one conditions within that OR group in an SQL statement's WHERE clause.
The basic syntax of the AND operator with a WHERE clause is as follows −
This is the general statement to use AND & OR operators within an SQL Statement
SELECT column1, column2, ...
FROM tablename
WHERE [condition1] AND/OR [condition2]...;
Lets create a sample table and add some rows
CREATE TABLE SampleContacts (
FullName VARCHAR(50) NOT NULL,
Gender CHAR(1) NOT NULL,
Age INT NOT NULL
);
INSERT INTO SampleContacts
(FullName, Gender, Age)
VALUES
('Contact1', 'M', 26),
('Contact2', 'M', 29),
('Contact3', 'F', 27),
('Contact4', 'F', 29),
('Contact5', 'F', 27),
('Contact6', 'M', 29)
The AND Operator
The following statement returns the records with Gender equals to 'M' and Age greater than 26 from SampleContacts:
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE Gender = 'M' AND Age >26
| FullName | Gender | Age |
---------------------------
Contact2 M 29
Contact6 M 29
The OR Operator
The following statement returns the records with Gender equals to 'M' or Age greater than 26 from SampleContacts:
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE Gender = 'M' OR Age >27
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact2 M 29
Contact4 F 29
Contact6 M 29
Complex AND/OR Operator Combinations
The following statement returns the records with 26 years Male and 29 years Female contacts from SampleContacts:
SELECT
FullName, Gender, Age
FROM
SampleContacts
WHERE (Gender = 'M' AND Age = 26) OR (Gender = 'F' AND Age = 29)
| FullName | Gender | Age |
---------------------------
Contact1 M 26
Contact4 F 29