Some Useful Operators in The SQL WHERE Clause

There are many operators in WHERE clause in SQL but we are going to take a look the 4 difficult ones only. Let's take a look at the syntax of WHERE first.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

1. Not equal

Now we always use operators like CustomerID=1 or country=USA etc. etc. But what if you want to get all the records except CustomerID=1 or country=USA? That's when the Not equal operator comes into picture. Let's take a look at the example. 

Let's apply the following queries and see what results we get.

SELECT * FROM city LIMIT 10;
SELECT * FROM city WHERE Population <> 1780000 LIMIT 10;

The first one gives the whole database table without any condition but the second one gives us data without the Population=1780000. Take a look at the results in the demo link provided at the end of the article. [I am using the database from MySQL classic DB. Click here to read more]

Note: In some versions of SQL this operator may be written as !=

2. IN

This operator gives us results within the given value set. Take a look at the following queries.

SELECT * FROM city WHERE CountryCode IN ('AFG','ANT');

As you can see in the demo, it will give us the results with CountryCode AFG and ANT only and will omit other results. 

3. LIKE

This operator, as per the name, gives results which are alike to the query, sort of. Let's take a look at the example,

SELECT * FROM city WHERE CountryCode LIKE 'r%' LIMIT 10;

As you can see the results in the demo, the query returns the rows with CountryCode starting with "r" only. 

4. BETWEEN

As per the name suggests, BETWEEN operator gives us results between certain values for example price or population. Take a look at the following query.

SELECT * FROM city WHERE Population BETWEEN 10000 AND 20000 LIMIT 10;

As you can see the query and the results in the demo link, the BETWEEN operator helps us to find out the results for a specific range, in this example population between 10000 to 20000.

Share this post