Operators in SQL – Alias, IN and Between

In this tutorial, we will learn about the in and between clause of SQL and further we will discuss the need and use of aliases in SQL.

Let us now dive deep into the In and Between clauses and the alias, we use in SQL with the examples.

In and Between Clause in SQL

In SQL the clauses play an important role to simplify the queries. Similarly, the In clause is a shortcut tool for multiple Or conditions in a statement.

In clause helps us to check multiple conditions in the where clause. It is compatible with almost all data types in SQL be it an int, string, date or numerics.

The Between clause is put to select values in a given range (a,b) where a and b, both are inclusive.

Like the In clause, Between clause is also compatible with almost all data types i.e. string, int, date etc.

Below are the Syntax of both In and Between clause.

Syntax of IN clause in SQL:

SELECT col1,col2,col3,.....
FROM tableName
WHERE col1 IN (val1, val2, ...);

OR

SELECT col1,col2,col3,.....
FROM tableName
WHERE col1 IN (SELECT STATEMENT);

Syntax of BETWEEN clause in SQL:

SELECT  col1,col2,col3,.....
FROM tableName
WHERE col1 BETWEEN val1 AND val2;

What is Alias in SQL and Why do we need it?

An alias is a temporary reference name given to a table, column, or query result. The alias we create is temporary and exists only while the query is executing.

Once the query is done executing, the alias also disappears.

Some of the reasons why we need alias in SQL are as follows:

• To make the query result more readable.
• To enhance the understanding of the user by using proper names.

Now to use alias we need to know where to use them. Some of the major use cases of alias are as follows:

• When we are querying more than one table, the use of an alias makes the result easy to understand.
• When we have complex functions involved in our query, the use of alias makes the result more readable.
• Helps to make the column names more readable if they are not already user-friendly.
• Helps to rename the columns in the query result when columns are combined.

Syntax:

SELECT col1, 
col2 AS alis1,
col3 AS alias2
FROM tableName;

Demo Database

Let us now have a look at our demo database DataFlair.
Query: 

SELECT * FROM DataFlair;

Output:

Example 1: Let us now find the employees in DataFlair who have experience of Odd years.
Query:

SELECT emp_id AS Id,
name as Employee_Name,
experience AS Experience 
FROM DataFlair
WHERE experience IN (1,3,5,7,9,11);

Output:

Here, we can see the combined use of In clause and the alias.

Example 2: Let us now find the employees who are located in the Indore and Pune office of Dataflair.
Query:

SELECT emp_id AS Id,
name as Employee_Name,
location as Location
FROM DataFlair
WHERE location IN ('Indore','Pune');

Output:

Here again, we have seen the combination of IN clause and the alias in SQL.

Example 3: Let us now view the details of employees who have work_ex in between 5 to 10 years (both inclusive) in DataFlair.
Query:

SELECT emp_id AS Id,
name as Employee_Name,
location as Location,
experience as Work_ex
FROM DataFlair
WHERE experience BETWEEN 5 AND 10;

Output:

Here we can see the combination of the Between clause and the alias in SQL.

Example 4: Let us now find the details of employees with work_ex between 2 and 10 (both inclusive), but leaving out people with experience of 3 and 7 years.
Query:

SELECT emp_id AS Id,
name as Employee_Name,
location as Location,
experience as Work_ex
FROM DataFlair
WHERE experience BETWEEN 2 AND 10 AND 
experience NOT IN (3,7);

Output:

Here we can see the combination of the In and Between clause along with alias in SQL.

Summary

In this tutorial, we have seen what are In and between a clause in SQL. We have seen in detail what an alias is and where should we use SQL in a query.

We have understood all the use cases of both In and Between clauses and all the possible combinations using an alias in SQL.

Further, we have understood the underlying concept of both the clause i.e. In and Between clause using various example queries on our demo database.