10 - Querying Data by using the sub queries

A subquery is an SQL statement that is used with another SQL statement. The subqueries are nested inside the WHERE or HAVING clause of the SELECT, INSERT, UPDATE or DELETE statements. The outer query is known as the parent query and the subquery is known as the inner query. The inner query is executed first and the result is returned to the outer query.

10.1 Using the IN and EXISTS keywords

User can specify different conditions on subqueries by using the following keywords:

1) IN

2) EXISTS

1) IN

If any sub query returns more than one value, user might need to match a column value with the value in the list returned by the inner query. The IN keyword is used to perform the task.

The syntax for using the IN keyword is as mentioned below:

Consider an example of STUDENT table as shown below:

ROLLNO

FIRSTNAME

LASTNAME

SUBJECT

101

John

Fedora

Mathematics

102

Jenny

Thomas

Science

103

Henry

Clinton

Medicine

104

Alex

Domino

Arts

The query for retrieving the records is as shown below:

The table after executing the query is as shown below:

FIRSTNAME

LASTNAME

SUBJECT

John

Fedora

Mathematics

Alex

Domino

Arts

 

2) EXISTS

User can also check the use of a sub query to check if the set of records exists. The EXISTS clause is used with the sub query. The TRUE or FALSE value is returned by the query.

The query containing the EXISTS keyword differs from other query. The keyword is not preceded by the column name, constraint or any expression. It contains an (*) asterisk in the SELECT clause of the inner query.

The syntax of the EXISTS keyword is as shown below:

Consider the example of the SUPPLIERS and ORDERS tables as shown below:

SUPPLIERS

SUPPLIERID

SUPPLIERNAME

LOCATION

101

Mark

London

102

Peter

San Francisco

103

Henry

New Jersey

104

Jerry

Los Angles

105

Nichols

Tucson

 

ORDERS

SUPPLIERID

ORDERDATE

ORDERQTY

101

2010-03-07

20

103

2011-05-21

30

106

2009-08-15

45

107

2012-04-02

10

The query to retrieve the records from the table is as shown below:

The output table from the query is as shown below:

SUPPLIERID

ORDERDATE

ORDERQTY

101

2010-03-07

20

103

2011-05-21

30

 

10.2 Using modified comparison operators

In the subqueries, the comparison operators are used. They are used for creating condition that checks the value returned by the sub query. If the sub query returns more than one values, user needs to apply the operators to all the values returned by the sub query. The ALL and ANY keywords are used to modify the comparison operators.

The ALL keyword returns TRUE value if all the values that are retrieved by the sub query satisfy the comparison operator. If it returns FALSE value, if some values satisfy the comparison operator or if the sub query does not return any rows to the outer statement.

The ANY keyword returns TRUE, if any value retrieved from the sub query satisfy the comparison operator. It returns FALSE, if no values in the sub query satisfy the comparison operator or if the sub query does not return any row to the outer statement.

The following table shows the operators that can be used with the ALL and ANY keywords.

1) > ALL: It means that greater than the maximum value in the list.

2) > ANY: It means that greater than the minimum value in the list.

3) = ANY: It means any value in the list. It functions similar to the IN clause.

4) <> ANY: It means not equal to any value in the list.

5) <> ALL: It means not equal to all the values in the list. It functions similar to the NOT IN clause.

Consider the example of the CUSTOMER and AGENTS table.

CUSTOMER

CUTOMERID

CUSTNAME

COUNTRY

GRADE

C101

George

UK

A

C102

Albert

USA

A

C103

Alexandra

Germany

B

C104

Navy

India

A

AGENTS

AGENTID

AGENTNAME

LOCATION

PROFIT

A111

Nick

UK

10%

A112

Alex

USA

20%

A113

Adam

Germany

25%

A114

Jennifer

UK

10%

 

The query to demonstrate the ANY keyword is as shown below:

The output for the query is as shown below:

AGENTID

LOCATION

PROFIT

A111

UK

10%

A114

UK

10%

 

10.3 Using Aggregate Functions

In the subqueires, the aggregate values of the inner query are generated using the aggregate functions.

Consider an example of the EMPLOYEE table.

EMPID

EMPNAME

JOB

SALARY

701

Smith

CLERK

10000

702

Jones

SALESMAN

8000

703

Harry

SALESMAN

9000

704

Nick

WORKER

7000

 

The query through the use of the aggregate function is as shown below:

The output for the query is as shown below:

EMPID

EMPNAME

JOB

SALARY

702

Jones

SALESMAN

8000

703

Harry

SALESMAN

9000

 

10.4 Using Nested Sub queries

A sub query can contain more than one subqueries. The subqueries are used when the condition of a query is dependent on the result of another query, that result is dependent on the result on another sub query.

Consider the EMPLOYEE table. The query for demonstrating the nested subqueries is as shown below:

The output for the query is as shown below:

EMPID

EMPNAME

JOB

SALARY

701

Smith

CLERK

10000

 

10.5 Using Correlated Sub queries

The correlated sub query can be defined as a query that depends on the outer query for its result. In the sub query, the inner query is executed only once. The output of the inner query is provided to the outer query or the main query.

In the correlated sub query, the inner query is executed depending on the outer query.

Consider the example of the PRODUCT table.

ProdID

ProductName

Unit

Price

301

Laptop

3

30000

302

Hard Disk

5

10000

303

CD Drivers

10

5000

304

Printer

7

4000

The query for demonstrating the correlated query is as shown below:

The output for the query is as shown below:

ProdID

ProductName

Unit

Price

301

Laptop

3

30000

302

Hard Disk

5

10000

10.6 Using APPLY operator

There is a condition when the user needs to combine the result of two queries such that each row of the first query, the second query is executed to show the rows returned. Consider an example of employee salary and the dividend provided on the salary.

There are two tables as employee and salary. The employee table contains the employee details and the salary table contains the pay information.

For displaying the records the user needs to retrieve the employee details and the salary information from the salary table. To combine the output of two query, the APPLY operator is used to display the data.

There are two types of APPLY operator supported by the SQL Server. They are as follows:

1) CROSS APPLY

2) OUTER APPLY

The syntax for the APPLY operator is as mentioned below:

Where,

left_table_expression represents the outer query result set.

right_table_expression represents the inner result set.

1) CROSS APPLY

The operator returns only those rows from the outer result set that matches the inner result set.

Consider the EMPLOYEE table as shown below:

Employee_Name

Account_Number

Nick

9876

John

1234

Henry

3456

Mark

1111

Consider the SALARY table as shown below:

Employee_Name

Salary

Nick

25000

John

15000

Henry

20000

The user wants to retrieve the Employee_Name, Account_Number and Salary from the EMPLOYEE and SALARY table. The query by applying the CROSS APPLY is as shown below:

The output for the query is as shown below:

Employee_Name

Account_Number

Salary

Nick

9876

25000

John

1234

15000

Henry

3456

20000

2) OUTER APPLY

The OUTER APPLY operator is used to return all rows from the outer result set even if the rows are no present in the inner result set. There is NULL value for the inner result set when the values do not match.

Consider the EMPLOYEE and SALARY table and apply the OUTER APPLY operator.

Employee_Name

Account_Number

Salary

Nick

9876

25000

John

1234

15000

Henry

3456

20000

Mark

1111

NULL

Like us on Facebook