How to use combine multiple conditions in SQL in 5 steps

This article discusses how to combine multiple conditions in a MySQL query. We’ll learn how to combine multiple conditions in the WHERE clause.

Example Syntax :
SELECT col1, col2, … coln
FROM table
WHERE col3 LIKE "%some-string%"
AND
col4 = 55;

-- The article queries are reproduced below for convenient copy/paste into the terminal.
-- Query 1
SELECT * FROM Actors WHERE FirstName > "B" AND NetWorthInMillions > 200;

-- Query 2
SELECT * FROM Actors WHERE FirstName > "B" OR NetWorthInMillions > 200;

-- Query 3
SELECT * FROM Actors WHERE (FirstName > 'B' AND FirstName < 'J') OR (SecondName >'I' AND SecondName < 'K');

-- Query 4
SELECT * FROM Actors WHERE NOT(FirstName > "B" OR NetWorthInMillions > 200);

-- Query 5
SELECT * FROM Actors WHERE NOT NetWorthInMillions = 200;

-- Query 6
SELECT * FROM Actors WHERE (NOT NetWorthInMillions) = 200;

-- Query 7
SELECT * FROM Actors WHERE FirstName > "B" XOR NetWorthInMillions > 200;

SELECT * FROM table WHERE column1 > condition1 AND column2 > condition2;

We can use the AND operator to query for actors whose first name starts with the letter ‘B’ or any character thereafter and whose net worth is greater than 200 million dollars. Any row that satisfies both these conditions is displayed.

SELECT * FROM Actors WHERE FirstName > "B" AND NetWorthInMillions > 200;

SELECT * FROM table WHERE column1 > condition1 OR column2 > condition2;

We can use the OR operator to match rows that satisfy at least one of several conditions specified in the WHERE clause. We can now query for actors whose first name starts with the letter B or any character thereafter or has a net worth of 200 million dollars or greater. The query now returns four more rows than the previous AND query.

SELECT * FROM Actors WHERE FirstName > "B" OR NetWorthInMillions > 200;

SELECT * FROM table WHERE (column1 > condition1 AND column1 < condition2) OR (column2 > condition3 AND column2 < condition4);

We can also combine the AND and the OR operators. Consider the above query, Each clause within the parentheses in the above query selects an actor meeting the criteria, and the OR-ing of the two clauses prints three actors.

SELECT * FROM Actors WHERE (FirstName > 'B' AND FirstName < 'J') OR (SecondName >'I' AND SecondName < 'K');

SELECT * FROM table WHERE NOT(column1 > condition1 OR column2 > condition2);

NOT is a unary operator that negates a boolean statement. For example, the above query gives us the complement of the result set that includes actors with a net worth greater than two hundred million dollars and whose first name starts with alphabet ‘B’ or higher. The complement includes only one row.

SELECT * FROM Actors WHERE NOT(FirstName > "B" OR NetWorthInMillions > 200);

The rows matching the two conditions in the parentheses are excluded by the NOT and everything else is included. The NOT operator’s precedence can be tricky. Consider the following query:

SELECT * FROM Actors WHERE NOT NetWorthInMillions = 200;

The above query returns all the actors with a net worth not equal to 200 million dollars. However, if we put parentheses as follows around the NOT operator, the result is an empty set:

SELECT * FROM Actors WHERE (NOT NetWorthInMillions) = 200;

The NOT operator is applied to the column NetWorthInMillions which has all non-zero values for all the rows in the table. Applying NOT on a non-zero column value makes it a zero, and since zero isn’t equal to 200, no rows are displayed. Also note that if the table had a row with a zero value for the column NetWorthInMillions, it will still not display anything because NOT of zero is non-zero, which isn’t equal to 200.

SELECT * FROM table WHERE column1 > condition1 XOR column2 > condition2;

MySQL supports exclusive OR through the XOR operator. Exclusive OR returns true when one of the two conditions is true. If both conditions are true, or both are false, the result of the XOR operations is false. If we XOR the conditions from the previous query, we are returned four rows. The rows satisfy either of the conditions but not both. All the other rows in the table either fail or satisfy both conditions and aren’t included in the result set.

SELECT * FROM Actors WHERE FirstName > "B" XOR NetWorthInMillions > 200;