Scalar Sub-Queries in SQL Server

September 22, 2004

Scalar sub-queries return exactly one column value from one row. Scalar sub-queries can be used in CASE expressions, WHERE clauses, ORDER BY clauses and SELECT clauses. This article reviews a few examples of how to use scalar sub-queries in most situations.

Scalar Sub-Query in CASE Expression

First, create a table with sample data as shown below.

use tempdb
go
if exists 
  (select * from dbo.sysobjects 
    where id = object_id(n'[systems]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [systems]
go
create table systems (id int, dbtype varchar(100))
go
insert into systems select 1,'sql server'
insert into systems select 2,'oracle'
insert into systems select 3,'sybase'
insert into systems select 4,'db2'
insert into systems select 5,'ingress'
insert into systems select 6,'gupta sql'
go

Using Scalar Sub-Query in CASE expression, we can find which values are "MS SQL Server."

Query

In this example, the sub-query evaluates the DBType column for SQL Server and returns a single column value.

select id,DBType, (case when DBtype in 
(select DBType from systems 
  where DBtype ='SQL Server') 
then 'MS SQL Server'
else 'Non MS SQL server' end) 'SQL Server?'
from Systems

Results

Scalar sub-query in SELECT statement

Next, let's create a table with sample data as shown below.

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(n'[employee]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [employee]
go
create table employee (empid int, name varchar(100), salary money, deptid int)
go
insert into employee select 1,'claire shi',95000.00,1
insert into employee select 2,'carol mok',65000.00,2
insert into employee select 3,'lucy ge',95780.00,2
insert into employee select 4,'william hung',95010.00,2
insert into employee select 5,'chang jin',55000.00,1
insert into employee select 6,'honglet hsu',65000.00,3
insert into employee select 7,'karen mok',75000.00,1
insert into employee select 8,'vivian shi',98909.00,4
insert into employee select 9,'hsu chi',51000.00,3
insert into employee select 10,'chow fat',53000.00,4
go

Query

In this example, the subquery returns the maximum salary as a single column value.

Select name, deptid,Salary,
(select max(salary) from employee me where me.deptid = e.deptid) as Department_MAX_Salary
from employee e order by deptid, salary

Results

Scalar sub-query in WHERE Clause

Let's create a table with sample data as shown below.

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(n'[employee]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [employee]
go
create table employee (empid int, name varchar(100), salary money, deptid int)
go
insert into employee select 1,'claire shi',95000.00,1
insert into employee select 2,'carol mok',65000.00,2
insert into employee select 3,'lucy ge',95780.00,2
insert into employee select 4,'william hung',95010.00,2
insert into employee select 5,'chang jin',55000.00,1
insert into employee select 6,'honglet hsu',65000.00,3
insert into employee select 7,'karen mok',75000.00,1
insert into employee select 8,'vivian shi',98909.00,4
insert into employee select 9,'hsu chi',51000.00,3
insert into employee select 10,'chow fat',53000.00,4
go
if exists (select * from dbo.sysobjects where id = object_id(n'[department]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [department]
go
create table department (deptid int, name varchar(100))
go
insert into department select 1,'information technology'
insert into department select 2,'finance'
insert into department select 3,'treasury'
insert into department select 4,'marketing'
go

Query

In this example, the subquery is used to evaluate the salary values of the main query.

Select a.name,b.name as Department, a.salary from employee a, Department b
where a.deptid=b.deptid and 
a.salary < (select max(salary) from employee)

Results

Scalar sub-query in ORDER BY Clause

Let us create a table with sample data as shown below.

use tempdb
go
if exists (select * from dbo.sysobjects where id = object_id(n'[employee]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [employee]
go
create table employee (empid int, name varchar(100), salary money, deptid int)
go
insert into employee select 1,'claire shi',95000.00,1
insert into employee select 2,'carol mok',65000.00,2
insert into employee select 3,'lucy ge',95780.00,2
insert into employee select 4,'william hung',95010.00,2
insert into employee select 5,'chang jin',55000.00,1
insert into employee select 6,'honglet hsu',65000.00,3
insert into employee select 7,'karen mok',75000.00,1
insert into employee select 8,'vivian shi',98909.00,4
insert into employee select 9,'hsu chi',51000.00,3
insert into employee select 10,'chow fat',53000.00,4
go
if exists (select * from dbo.sysobjects where id = object_id(n'[department]') 
  and objectproperty(id, n'isusertable') = 1)
drop table [department]
go
create table department (deptid int, name varchar(100))
go
insert into department select 1,'information technology'
insert into department select 2,'finance'
insert into department select 3,'treasury'
insert into department select 4,'marketing'
go

Query

In this example, the sub query is used for sorting.

Select deptid, name from employee e
order by (
select name from department d where e.deptid=d.deptid)

Results

Conclusion

This article reviewed examples of how to use scalar sub-queries in different situations such as CASE, SELECT, ORDER BY and WHERE clauses.

» See All Articles by Columnist MAK