Learn SQL: Where, operators, Like, Between

Using Where clause with logical operators

Previous part:

learn SQL: introduction

Here, we’ll see how we can use Where clause to filter data from our tables and also use the various operators to create complex filter conditions.

Where Clause

It is used to filter data from your table. Some basic arithmetic comparison operators that we use are:

https://minigranth.com/wp-content/uploads/2018/05/SQL-Comparison-Operators.jpg

select ename
from emp
where sal > 2000;

gives all ename whose salary is greater than 2000.

We can use the equal operator to compare values for equality:

select empno, ename, job, sal
from emp
where sal = 5000;

String Comparison
To compare a string we can use = operator or we can use like operator which we’ll discuss later. In case of = operator the string that is compared should match the case in table data and it should be full string and not a part. Ex: to search for an ename King we must use full name ‘KING’ in UPPERCASE

select *
from emp
where ename = ‘KING’;
// If we use ename = 'king' which is lowercase, it won't return any data.

For comparing part of a string we use like operator. which is a logical operator. So suppose if we only know pattern of the name then we use like along with some wild card characters. Two of the most common used in string comparison are _ and % wildcard characters.

Make a note that when we use like we don’t use the = but directly write the pattern between single quotes.

select *
from emp
where ename like '%LA%'

This query will return all emp whose ename contains the string ‘LA’ anywhere in the name.

the % here represent string of size 0 or more. i.e. before and after ‘LA’ there can be 0 or more characters.

If we know only some starting characters of string then we can use % in the last.

select *
from emp
where ename like 'BL%'

If we know only some last characters of string then we can use % in the beginning.

select *
from emp
where ename like '%E'

If we don’t know the middle of a string then we can use % in the middle

select *
from emp
where ename like 'B%E'

The % is to be used when we don’t know 0 or more characters but If we just want to replace 1 letter only then the next wildcard that comes in handy is _ underscore.

For A letter in the start we use it at the beginning of a string.

select *
from emp
where ename like '_LARK'

Similarly we can use it in the mid or end.

Another use it for when we want to get string with at least 1 character then we can combine _ with % and get the strings whose length is 1 or more.

select *
from emp
where ename like '_%'

Remember that if we use only % then it’ll give all the rows where ename is NULL as well.

https://www.w3schools.com/sql/sql_operators.asp

To apply multiple conditions to filter data we can use logical operators such as AND as well as OR

select *
from emp
where job = 'MANAGER'
and sal > 2000;

It’ll return 3 rows

using AND operator only those rows are returned where both the conditions are true. We aren’t restricted to using only one AND operator as we can use any no of conditions.

Instead of AND if we use OR operator and it’ll return all the rows where any one of the condition evaluates to be true.

select *
from emp
where job = 'MANAGER'
OR sal > 2000;

It’ll return 6 rows if we use OR instead of AND

Not Equals:

We can add the exclamation mark in front of = operator like != to get all the values that are not equal to the given operand.

select *
from emp
where job != 'MANAGER'
and sal > 2000;

IN Operator:

To check the condition against multiple values of same datatype among which only 1 can be true then instead of using multiple OR operators we can use in operator and provide the filter values to check against inside parenthesis.

IN (val1, val2, val3...)

Ex. the query using OR operator

select ename, hiredate, deptno
from emp
where deptno = 10 
OR deptno = 20

Can be efficiently written using IN operator

select ename, hiredate, deptno
from emp
where deptno IN (10, 20)

We can also use IN for getting Enames

select ename, hiredate
from emp
where ename in ('KING', 'CLARK', 'JONES','WARD')

⚠️Remember that Names inside quotes are case sensitive

NOT operator

The NOT operator is used to negate a condition has similar functionality as that of ! but they both have separate places where they can be used. While ! is used with arithmetic operators, NOT can be used with rest operators. We’ll see most of them but for now let’s use NOT with IN operator.

Let’s use the same query as above with NOT operator on our table of 14 rows and see what happens

select ename, hiredate
from emp
where ename not in ('KING', 'CLARK', 'JONES','WARD')

We got 10 out of 14 rows and they don’t contain the names given in the IN operator. Or in other words they contain all the names other than specified in the IN operator.

BETWEEN Operator

The Between operator filters data based on a range given in Ascending order.

The order in which range is given must be ascending. BETWEEN operator includes the boundary condition in the output.

select *
from emp
where sal between 800 and 2000

Will give all the emp details with salary in range 800–2000 including 800 and 2000 which is boundary condition.

But if we give range in descending order, then

select *
from emp
where sal between 2000 and 800

Will output no rows because the range given is in descending order.

BETWEEN can be used to filter for dates and also for text values as well.

We can also use NOT operator to filter for all values other than given to the between operator.

select *
from emp
where sal not between 800 and 2000

Will give details of emp whose salary is less than 800 and greater than 2000.

Using Parenthesis () to club together conditions:

We will be using parenthesis for nested queries but before all that the simplest use for parenthesis is to simulate priority among same priority operators. It works same as in the PEMDAS/BODMAS rule.

SELECT * from emp where job = 'SALESMAN' and (comm = 300 or comm > 1000)

This query will first evaluate (comm = 300 or comm > 1000) condition and then the boolean outcome of that will be compared against the preceding AND operators.

It’ll output details of emp who are salesman and whose commission is either 300 or 1000.

Alias in SQL

We can give alias to columns and table names in our query so that they make much more sense and they also come in handy when we derive a new column and want to give the new column a name.

Let’s take an easy example of calculating double salary of emp

select ename, sal*2  from emp

Here we see that the column has name sal*2 which doesn’t look very polished. We can give the column an alias by writing the alias just after the column name. We can make use of the word AS to give the alias which is a good practice but it is completely optional. The query will work both ways.

select ename Employee, sal*2 "Double salary" from emp
or 
select ename AS Employee, sal*2 AS "Double salary" from emp

Here we gave ename an alias of EMPLOYEE and sal*2 the alias Double salary. Notice that for a single word alias we don’t write it in double quotes but for a name which contains more than one word we use double quotes because the name has a space in it. The name inside double quotes is case sensitive.

Similarly we can give alias to table as well which is useful when we have more than one table in action.