SQL WHERE Condition | Filter Results from Table

The SQL WHERE Condition filters record from the table by using a specified condition. The query using where condition returns only those records which fulfill some conditions. Also, you can not only use it with SQL SELECT but also in UPDATE and DELETE statements. In this article, we will discuss the SQL WHERE Condition. Also, we will discuss a few examples of using it.

Syntax

SELECT column1, column2 FROM table WHERE condition;

You can specify the WHERE Condition in the query. The above SELECT query will return only those results which match the condition.

Demo Table

For instance, consider the demo table to refer for the all the queries in this article.

+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Supported Conditions

You can specify the following conditions with a WHERE Condition to filter records.

OperatorDescription
=Equal
>Greater Than
< Less Than
>=Greater Than or Equal To
<= Less Than or Equal To
<> or !=Not Equal To
BETWEENLying in the certain range
LIKESearch for a pattern
INSpecify multiple Values for a column

Also, you can provide multiple conditions by using AND/OR Operators. By using AND, the query will return the records fulfilling both the conditions. However, by using OR, the query will return records that fulfill atleast one of the condition.

Examples

Example 1: Using Equal To

We can fetch only those records from the table whose column matches exactly the one in the where condition. For instance, consider the query using SQL WHERE below:

mysql> SELECT * FROM employees WHERE salary = 20000;
+----+-------+--------+------------+-------+
| id | name  | salary | experience | team  |
+----+-------+--------+------------+-------+
|  1 | David |  20000 |          5 | Alpha |
+----+-------+--------+------------+-------+

In the above example, the query fetches the rows with salary exactly equal to 2000.

Also, we can set a condition on string in SQL WHERE Condition.

mysql> SELECT * FROM employees WHERE team = "Beta";
+----+--------+--------+------------+------+
| id | name   | salary | experience | team |
+----+--------+--------+------------+------+
|  2 | Monica |  25000 |          6 | Beta |
|  3 | John   |  15000 |          3 | Beta |
+----+--------+--------+------------+------+

In the above example, the query fetches the rows with column team exactly equal to ‘Beta’.

Example 2: Using Greater Than or Less Than

Similarly, we can use a Greater Than or Less Than Condition in an SQL WHERE.

mysql> SELECT * FROM employees WHERE salary > 20000;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  2 | Monica |  25000 |          6 | Beta  |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

The above query fetches the rows whose salary is greater than 20000. Similarly, we can use the less than condition to fetch a column value less than the condition value.

mysql> SELECT * FROM employees WHERE salary < 16000;
+----+------+--------+------------+------+
| id | name | salary | experience | team |
+----+------+--------+------------+------+
|  3 | John |  15000 |          3 | Beta |
+----+------+--------+------------+------+

Example 3: Using Greater Than or Equal To OR Less Than or Equal To

Also, the only difference between greater than or equal to and a simple greater than condition is that the former also checks for equality.

mysql> SELECT * FROM employees WHERE salary >= 20000;
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

The Less Than or Equal To condition also works the similar way.

Example 4: Using Not Equal To

On the other hand, the Not Equal To condition in an SQL WHERE Condition works the opposite to Equality condition. It selects those rows whose condition does not match with the where condition. For instance, consider the example as follows:

mysql> SELECT * FROM employees WHERE team != 'Alpha';
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
+----+--------+--------+------------+-------+

Example 5: Using BETWEEN Condition

Similarly, we can use the BETWEEN condition in an SQL WHERE Condition to select the rows whose values lie in a certain range.

mysql> SELECT * FROM employees WHERE salary BETWEEN 15000 AND 20000;
+----+-------+--------+------------+-------+
| id | name  | salary | experience | team  |
+----+-------+--------+------------+-------+
|  1 | David |  20000 |          5 | Alpha |
|  3 | John  |  15000 |          3 | Beta  |
|  4 | Mary  |  18000 |          4 | Gamma |
+----+-------+--------+------------+-------+

In the above example, the query fetches the rows with salary between 15000 and 20000.

Example 6: Using LIKE Operator

Also, we can use the LIKE Operator in an SQL WHERE condition to select a string column with a pattern match.

mysql> SELECT * FROM employees WHERE name LIKE 'M%';
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  2 | Monica |  25000 |          6 | Beta  |
|  4 | Mary   |  18000 |          4 | Gamma |
+----+--------+--------+------------+-------+

In the above example, the query fetches the rows whose name start with ‘M’. Notice the percentage in the above query. The percentage is after the letter M which states that the first letter should be M, and anything can appear after that. However, if you want to select the middle letter, you can put % on both the sides as follows:

mysql> SELECT * FROM employees WHERE name LIKE '%n%';
+----+--------+--------+------------+------+
| id | name   | salary | experience | team |
+----+--------+--------+------------+------+
|  2 | Monica |  25000 |          6 | Beta |
|  3 | John   |  15000 |          3 | Beta |
+----+--------+--------+------------+------+

The above query fetches the rows which have the letter ‘n’ anywhere in their name.

We will discuss the LIKE Operator in detail in a later article.

Example 7: Using IN

The IN operator in an SQL WHERE condition fetches the rows whose value is equal to either of the ones in the list. It is useful when you need to search for multiple values in the rows.

mysql> SELECT * FROM employees WHERE experience IN (3, 6);
+----+--------+--------+------------+------+
| id | name   | salary | experience | team |
+----+--------+--------+------------+------+
|  2 | Monica |  25000 |          6 | Beta |
|  3 | John   |  15000 |          3 | Beta |
+----+--------+--------+------------+------+

In the above example, the query selects the rows with experience as either 3 or 6.

Similarly, you can also use IN with String Columns. The below query fetches the rows with team as either Alpha or Beta.

mysql> SELECT * FROM employees WHERE team IN ("Alpha", "Beta");
+----+--------+--------+------------+-------+
| id | name   | salary | experience | team  |
+----+--------+--------+------------+-------+
|  1 | David  |  20000 |          5 | Alpha |
|  2 | Monica |  25000 |          6 | Beta  |
|  3 | John   |  15000 |          3 | Beta  |
|  5 | Ross   |  30000 |          8 | Alpha |
+----+--------+--------+------------+-------+

Example 8: Using Multiple Conditions Together in SQL WHERE

Also, you can use multiple conditions together in an SQL WHERE Condition by using AND/OR Operators.

mysql> SELECT * FROM employees WHERE salary = 15000 OR team = "Gamma";
+----+------+--------+------------+-------+
| id | name | salary | experience | team  |
+----+------+--------+------------+-------+
|  3 | John |  15000 |          3 | Beta  |
|  4 | Mary |  18000 |          4 | Gamma |
+----+------+--------+------------+-------+

The above query fetches the rows with salary equal to 15000 or team equal to Gamma.

Conclusion

In conclusion, we discussed the SQL WHERE Condition in this article. You can use it with SQL SELECT, UPDATE or DELETE Queries. You can read more about it on Wikipedia. Additionally, you can learn more about SQL on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *