You are here

Subqueries

Subqueries

Subqueries

In this post we will discuss Subqueries in SQL Server. Subqueries are nested queries means query within Query.it returns result of inner query to outer query. In SQL Server Subquery can be of one of the following:
– Self-contained Subquery
– Correlated Subquery
In terms of the result returned by subquery it can be
Scalar
Multi-valued (table with a single column)
– Multi-column Table-value (table with multiple column)

Self-contained Subquery:

  • Self-contained subqueries are independent of outer query. It not plays any important role other than passing result. We can run inner query independently.
  • It returns a single value, multiple value or entire table. And inner query is executed only once.
  • We used self-contained subquery in WHERE and FROM clause.
  • T-SQL support some operator that we can used in subqueries, like ALL, ANY, SOME. We used this operator rarely because this are simple and intuitive

Syntax:
Select <select-list>
From <table_name>
Where <expression> <operator> {ALL|ANY|SOME} (<subquery>);

  • The ALL operator returns true only when applying it to the expression and all values returned by the subquery.
  • SOME and ANY operator compare a value to each value in a list or results from a query and evaluate to true if the result of an inner query contains at least one row.

Example:
Table: product (pro_id is primary key and supp_id is foreign key)

pro_id

pro_name

price

supp_id

1

Pens 250.0000 2
2 Books 500.0000

1

3

Pencils 100.0000 4

4

Lunchbox

200.0000

3

5

Calculator 800.0000 5

Table: supplier (su_id is primary key)

su_id

su_name City

1

Riya

Vvnagar

2

Ankit

Ode

3

Nikunj

Vvnagar

4

Jiya

Nadiad

5

Tiya

Nadiad

  • Retrieve the minimum price of product from product table

select pro_name, price from dbo. product where price= (select MIN(price) from dbo. product);
Output:

pro_name

Price

Pencils

100.0000

Correlated Contained Subquery:

  • Correlated subqueries are subqueries where the inner query has a reference to a column from the table in the outer query.
  • Inner query is dependent on the outer query, and it is executed once per row. We cannot execute it separately.
  • It may return scalar and multiple values.
  • In order for the subquery to be able to distinguish between the two, must assign different aliases to the different instances.

Example:
Table: Employee (e_id is primary key)

e_id

Name Salary department

1

A

2K

CSE

2

B

3K

EC

3

C 3K CSE
4 D 4K

EC

  • Retrieve the name of the employee whose salary is greater than average of their department.

select e_id, name from Employee as e1 where salary> (select AVG(salary) from Employee as e2 where e2. department=e1. department);
Output:

e_id

Name

3

C

4

D

Exists and Not Exists Predicate: –

  • The Exists predicate accepts a subquery as input and returns true when the subquery returns at least one row and false otherwise.
  • Exists doesn’t need to return the result set of the subquery; it returns only true or false, it depends on subquery returns any rows.

Example:
Table: Customers  (Custid is primary key)

Custid

Fname

Lname

4000

Sita

Patel

5000

Gita

Shah

7000

Rita

Joshi

9000

Mita

Patel

Table: Orders (Orddid is primary key, Custid is foreign key)

Ordid

Custid

Ordate

1

7000 2016-04-18
2 5000

2016-04-18

3

8000 2016-04-19
4 4000

2016-04-20

  • Retrieve the customer details who buy something

Select * from Customers where EXISTS (select * from orders where Customers. Custid= Orders. custid);
Output:

Custid

Fname

lname

4000

Sita

Patel

5000

Gita

Shah

7000

Rita

Joshi

  • Retrieve the customer details who don’t buy anything

Select * from Customers where NOT EXISTS (select * from Orders where Customers. Custid=Orders. Custid);
Output:

Custid

Fname

lname

9000

Mita

Patel

Leave a Reply

Top