AND OR Operators in SQL

AND & OR Operators

The AND and OR operators are widely used by both DBA’s and Developers to join different conditions to limited information within a SQL query. Both of these operators are called “Conjunctive Operators” because of their usage.

These operators give a way to make numerous comparisons with various operators in the same SQL query.

AND Operator

AND Operators is of multiplicative nature as it will only return True if all the given conditions are true otherwise it will return False. This operator allows the application of multiple conditions within an SQL statement’s WHERE clause.

Syntax

SELECT col_name_1, col_name_2, … ,col_name_n

FROM tbl_name

WHERE [condition_1] AND [condition_2] AND … AND [condition_N];

In the above syntax, N number of conditions can be applied using AND operator. However, it will only return TRUE if all the conditions given, are true. If any of the condition returns false, the whole process will be FALSE.

Example

Currently the Customers table has following records:

SELECT * FROM Customers

WHERE City = ‘Birmingham’ AND ID = 2;

Now this command will return the following record as there is only one record in table that has City as Birmingham and ID = 2.

Likewise, if we apply the following command:

SELECT * FROM Customers

WHERE City = ‘Manchester’ AND ID = 3 AND Status = ‘Inactive’;

This command returns one record that meets the ID criteria as equal to 3, City criteria as equal to Manchester and Status criteria as equal to Inactive.

Now, an example for a FALSE return

SELECT * FROM Customers

WHERE City = ‘Birmingham’ AND Status = ‘Inactive’;

The above query will return 0 rows as there is no record in the table that meets both the given conditions.

OR Operator

OR Operator is opposite to AND operator. In AND operator, all conditions have to be true in order to return TRUE whereas in OR operator, any one condition has to be true to return TRUE.

Syntax

SELECT col_name_1, col_name_2, … ,col_name_n

FROM tbl_name

WHERE [condition_1] OR [condition_2] OR … OR [condition_N];

In the above syntax, N number of conditions can be applied using OR operator. However, it will only require any one condition to be true to return a TRUE.  It will only return FALSE if none of the conditions return true.

Example

Currently the Customers table has following records:

SELECT * FROM Customers

WHERE City = ‘Birmingham’ OR ID = 5;

Now this command will return the following records:

In above table, all the records that met the condition of ID = 5 and all the records that met the condition of City = Birmingham.

Likewise, if we apply the following command:

SELECT * FROM Customers

WHERE City = ‘Manchester’ OR ID = 5 OR Status = ‘Inactive’;

This command returns two records that meets the ID criteria as equal to 5, City criteria as equal to Manchester and Status criteria as equal to Inactive.

Now, an example for a FALSE return

SELECT * FROM Customers

WHERE City = ‘Bristol’ AND ID = 7;

The above query will return 0 rows as there is no record in the table that meets both the given conditions.

More Related Articles For You

• Architecture of DBMS
• CODDS RULE DBMS
• Database Models in DBMS
• Relational DBMS Concepts
• Keys and Types of keys in Database
• Database Normalization
• Generalization, Specialization and Aggregation Concepts in DBMS
• ERD Diagram Tutorial with Examples in DBMS
• Introduction to SQL
• How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
• Alter Query Command in SQL, Add, Modify and Drop Column in Table
• TCL Commands in SQL
• Truncate Query, Drop and Rename Query in SQL
• All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
• Data Control Language DCL Revoke and Grant Command in SQL
• Select Statement or Select Query in SQL Explained with Examples
• Distinct Keyword Explained in SQL
• WHERE Statement or WHERE Clause in SQL Statement Explained
• LIKE Operator in SQL
• ORDER BY Clause Sorting Explained in SQL
• Group By Clause in SQL
• Having Clause Explained in SQL
• SQL Constraints
• SQL Aggregate Functions
• SQL Aliases Use and Purpose in SQL
• SQL Joins Types, Use and Purpose
• SQL Sequence Syntax and Use with Examples
• SQL View
• SET Operation in SQL, UNION, UNION ALL, Intersect and Minus

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes