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:

ID C_Name Contact_No City Email Status
1 Tim Robbins +44 54 443-4434 London tim@myemail.uk Active
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active

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.

ID C_Name Contact_No City Email Status
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active

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.

ID C_Name Contact_No City Email Status
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk 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:

ID C_Name Contact_No City Email Status
1 Tim Robbins +44 54 443-4434 London tim@myemail.uk Active
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active

SELECT * FROM Customers

WHERE City = ‘Birmingham’ OR ID = 5;

Now this command will return the following records:

ID C_Name Contact_No City Email Status
2 James Chris +44 54 498-3476 Birmingham james@myemail.uk Active
4 Richard Butler +44 54 422-2345 Birmingham richard@myemail.uk Active
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active

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.

ID C_Name Contact_No City Email Status
3 Kevin Sputnik +44 54 487-6987 Manchester kevin@myemail.uk Inactive
5 David McGregor +44 54 413-0989 Cardiff david@myemail.uk Active

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

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes

Search

Shortcodes Ultimate

 
Follow Us
 
BGP