Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Sep 22, 2004

Scalar Sub-Queries in SQL Server

By Muthusamy Anantha Kumar aka The MAK

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date