Scalar Subqueries :

In my previous articles I have given the idea about the subqueries with its real world examples. In this article I would like to give you basic idea about the scalar subqueries with its real industry examples. I would like to give you the brief idea of what is Scalar Subqueries and how it is used in industry. I will explain the different usages of Scalar Subqueries as well. The Scalar Subqueries are introduced in Oracle 9i in which the output of select query is nothing but one column or expression with select statement.

What is Scalar Sub-query ?

Scalar Sub-query is nothing but the query which only select one column or one expression and it will return only one row. There are two scenarios while working with the scalar subqueries :

1.If the Scalar sub-query returns zero rows then oracle will use the Null value as output of subquery.

2.If Scalar sub-query returns more than one row the oracle will throw the error.

Error Description for the same is :

Error: Scalar sub-query produced more than one element.

There are following Usages of Scalar Subqueries. I will explain the different examples with these usages in next section.

1.The scalar sub-queries are most used for removing the outer joins.

2.If user want to aggregate multiple tables then scalar sub-queries are useful.

3.Table insertion based on other table values.

Hope you get the actual idea about the scalar sub-query and all the different condition of scalar subqueries as well. In following section i would like to give you some important examples of Scalar subqueries. Before that Let me give you where the developer can use the Subqueries.

User can use the SQL Scalar Subqueries with using the following keywords of SQL,

• CASE expressions
• SELECT statements
• VALUES clauses of INSERT statements
• WHERE clauses
• ORDER BY clauses
• Parameters of a function

Some Real World Examples of Scalar Subqueries :

In this section i will give you some most important examples of scalar subqueries.

Example 1 : Simple Scalar Sub-query example 

If user want to find out the Department_name and Number_of_Departments using scalar query you can use following expression :

Query :

Select D.Department_name(Select Count(E.Department_name) From Employee E Where E.Department_no=D.Department_no;

Example 2 :How to Remove Outer join using scalar Sub-query

The Scalar Subqueries are mainly used to remove the outer join situations. So you should have question in mind that why there is necessity to remove the outer join conditions. There are following reasons for which the outer join conditions needs to be removed :

1. Easiness :

The user can easily read the scalar subqueries rather than complex outer join queries. There are so many situations where the queries are too huge to read. In that cases if user converts that query to scalar subqueries. Its easy to read for user to read the query.

2.Performance Tuning :

Sometimes the performance of Query will improve by using scalar sub-query.

Let us check the following query to check the department_name,Count of Sub-departments and Average salary of Department.

Query which uses the Left Outer Join to fetch the data :

select a.Department_name, count(*) Count_Of_SubDepartment, avg(b.Salary) Average_Department_Salary
from Department a, Department_Salary_Info b
where a.Dept_no= b.Dept_no(+)
group by a.Department_name
order by a.Department_name;

I would like to reconstruct the Query :

select a.Department_name , (select count(*) from Department_Salary_Info b where b.Dept_no= a.Dept_no) Count_of_Subdepartment, (select avg(salary) from Department_Salary_Info b where b.Dept_no= a.Dept_no) Average_Department_Salary from Department a order by a.Department_name;

The above query will spit in to two scalar queries which returns the Count of SubDepartments and Average salary of Departments.

Example 3 : Inserting in to table based on other table

The scalar subqueries are used to insert the values from other table. If user want to insert values from another table to newly created table scalar subqueries are used. These kind of situations are actually useful in reporting. There should be very complex requirements of reports where user needs to fetch count and average of some columns from multiple tables. In those situations user need to create a new table and insert the appropriate values in it.

Lets take following example ,

Create table for the new report :

create table Salary_Details(
sum_of_sal number,
max_of_sal number,
min_of_sal number,
avg_of_sal number
);

After new table has been created .Now insert the values in to the table using scalar subqueries,

insert into Salary_Details(
sum_of_sal ,
max_of_sal ,
min_of_sal ,
avg_of_sal )
values (
(select sum(salary) from Employees),
(select max(salary) from Employees),
(select min(salary) from Employees),
(select avg(salary) from Employees)
);

Commit;

User can use the Salary details table to create the report based on salary of the employee.

Example 4 : Aggregating value collection from multiple table

As you all know that the user can use one aggregate function for one table at a time. If there are situations where the aggregating values needs to be collected from multiple tables. There are so many ways to collect the aggregate values from the multiple tables. User can use the outer join as well as inline views to fetch the aggregate values from multiple tables. But because of full scanning of one of the table the performance of the query will be not good.

In that situation user needs to use the scalar sub-query. The researchers proves that the scalar subqueries are faster than inline views.

Lets take example ,

select a.Department_name ,

(select count(*) from Department_Salary_Info b where b.Dept_no= a.Dept_no) Count_of_Subdepartment,

(select avg(salary) from Department_Salary_Info b where b.Dept_no= a.Dept_no) Average_Department_Salary

from Department a order by a.Department_name;

In above example we are fetching Count of Sub department as well as Average salary department wise.

These are some examples of Scalar Subqueries. There are some restrictions on using scalar subqueries.

1.Scalar Subqueries can not be used for Default Values

2.No scalar queries uses RETURNING clauses.

3.Scalar queries can not be used by Hash expressions or Functional index expressions

4.Scalar subqueries can not be used for CHECK constraints as well as WHEN condition of triggers.

5.It can not be used with Hierarchical Queries in SQL

6.It Can not be used with Group by and Having clauses.

These are above some restrictions of using the scalar sub-query. These are different examples of Scalar subqueries with real life examples. Hope you like this article on Scalar Subquery. If you like this article or if you have any suggestions with the same kindly comment in comment section.