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

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