Scalar Sub-Queries in SQL Server

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

id

dbtype

sql server ?

1

sql
server

ms sql
server

2

oracle

non ms sql
server

3

sybase

non ms sql
server

4

db2

non ms sql
server

5

ingress

non ms sql
server

6

Gupta sql

non ms sql
server

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

name

deptid

salary

Department_MAX_Salary

Chang Jin

1

55000

95000

Karen Mok

1

75000

95000

Claire
shi

1

95000

95000

Carol Mok

2

65000

95780

William
Hung

2

95010

95780

Lucy Ge

2

95780

95780

Hsu Chi

3

51000

65000

Honglet
Hsu

3

65000

65000

Chow Fat

4

53000

98909

Vivian
shi

4

98909

98909

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

name

department

salary

Claire
shi

Information
Technology

95000

Carol Mok

Finance

65000

Lucy Ge

Finance

95780

William
Hung

Finance

95010

Chang Jin

Information
Technology

55000

Honglet
Hsu

Treasury

65000

Karen Mok

Information
Technology

75000

Hsu Chi

Treasury

51000

Chow Fat

Marketing

53000

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

deptid

name

2

Carol Mok

2

Lucy Ge

2

William
Hung

1

Chang Jin

1

Karen Mok

1

Claire
shi

4

Chow Fat

4

Vivian shi

3

Hsu Chi

3

Honglet
Hsu

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

Latest Articles