SQL – Sub Queries

SUB Queries in SQL

A Subquery in SQL is also called as inner query, or inner select which will be always enclosed within parenthesis, basically to define it in simple words we can say subquery as query inside another query.

Sub Queries are always used inside the main query where the result of this sub will be used by the main one as a condition to restrict the retrieval of data of the main query.

Subqueries can go together with SELECT statement and also with DML commands of SQL like DELETE, UPDATE OR INSERT majorly with WHERE clause of all these statements and can also work different operators like =, <=,>=,>, <, IN, etc.

Before Writing a Subquery, we need to follow a few rules and instructions, let us have a look at them:

• A subquery must be always enclosed within parenthesis ()
• A subquery can occur in SELECT, FROM OR WHERE clauses of the main Query, but majorly used along with WHERE clause at the right side of any comparison operator.
• Execution of subquery will be done first as the main one uses its result further.
• SELECT Clause of subquery would normally have only one column, but multi-column sub quires also do exist and it works only when you tend to compare the same number of columns in an outer(main) query.
• Normally sub queries are used to return single rows for comparison, but we can also use multiple rows returning sub-queries with proper operators like IN.
• Sub query cannot go along with BETWEEN operator, however, you can use the BETWEEN operator inside a sub query.
• For sorting values in a sub query, one cannot use the ORDER BY clause, but we can use GROUP BY for the same purpose inside the sub query.

Syntax of Sub Query:

There is no specific syntax that we can lay down as portrait of subquery since they can be used with different clauses and different commands, but here is syntax of most used version of sub query:

SELECT Select_list
FROM Table
WHERE Column(or Columns) Operator(
SELECT Select_list
FROM Table);

Let us learn how to write Different kinds of sub queries with examples

Here is a sample tables and data inside table that we will use for all future references:
CREATE TABLE  “GangBoard_AGENTS_DATA”

(    
" GangBoard _AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,
"NAME" CHAR(30),
"AREA" CHAR(25),
"COMM" NUMBER(15,2),
"PH_NO" CHAR(10),
"NATIONALITY" VARCHAR2(20)
);

CREATE TABLE  “BESANT_CUSTOMER”

( " GangBoard _CUSTOMERCODE" VARCHAR2(6) NOT NULL PRIMARY KEY,
            " GangBoard _CUSTOMERNAME" VARCHAR2(30) NOT NULL,
            " GangBoard _CUSTOMERCITY" CHAR(30),
            " GangBoard _WORKING_AREA" VARCHAR2(30) NOT NULL,
            " GangBoard _CUSTOMERCOUNTRY" VARCHAR2(15) NOT NULL,
            "CUSTOMER_GRADE" NUMBER,
            "OPENING_AMOUNT" NUMBER(14,2) NOT NULL,
            "RECEIVE_AMOUNT" NUMBER(14,2) NOT NULL,
            "PAYMENT_AMOUNT" NUMBER(14,2) NOT NULL,
            "OUTSTANDING_AMOUNT" NUMBER(14,2) NOT NULL,
            "PH_N0" VARCHAR2(10) NOT NULL,
            " GangBoard _AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS
);

Sub Query in SELECT clause:

Sub queries in select clause are majorly as corelated ones, but we can also write some simple sub queries in select clause and we also can include expressions.

Example 1:

Simple subquery in select clause to select averages of commercial paid

SELECT GANGBOARD _AGENT_CODE, NAME,
(SELECT AVG(COMM) FROM BESANT_AGENTS_DATA ) AS AVARAGE_COMM
FROM GANGBOARD _AGENTS_DATA
WHERE GANGBOARD _AGENT_CODE IN ('B007','B003','B011')

Result:

Example2:

Result of sub queries in select can also be used in other expressions, let us just improvise the previous Query a little bit to see how we can use result of sub query in main query calculations

Query:

SELECT GANGBOARD _AGENT_CODE, NAME,
COMM - (SELECT AVG(COMM) FROM GANGBOARD _AGENTS_DATA ) AS MEAN_DIST_COMM
FROM GANGBOARD _AGENTS_DATA
WHERE GANGBOARD _AGENT_CODE IN ('B005','B004')

Result:

Sub Query in Where clause:

This is the most common use of sub Query; this is majorly used when you want compare the results of sub query and then restrict your main query output.

Example 1:

Let us say we want to retrieve only rows that has more COMM value than Avarage COMM value in the table, then first we need to write sub query that gets AVG value of COMM and then compare it with the present row value.

Query:

SELECT GANGBOARD _AGENT_CODE, NAME
FROM GANGBOARD _AGENTS_DATA
WHERE COMM > (SELECT AVG(COMM) FROM GANGBOARD _AGENTS_DATA )

Result:

Note: we can use any comparison operator along with the subquery as mentioned above in rules, but we need to be careful that to use operators with subquery only when it returns single row.

Example2:

When sub query returns more than one rows, we need to use ‘IN’ instead of operator in where clause.

Query:

SELECT GANGBOARD _AGENT_CODE, NAME
FROM GANGBOARD _AGENTS_DATA
WHERE GANGBOARD _AGENT_CODE IN (SELECT BESANT_AGENT_CODE
FROM GANGBOARD _AGENTS_DATA
WHERE COMM>40)

Result:

Example3:

Sub Query using two tables

Let us think that we want Agents data of those people who are serving customers in London city,
then First we have to fetch all the Agent_codes from the customer data whose city is London in sub Query and then use that result to fetch the Agent details of those people serving customers in London city

Query:

SELECT GANGBOARD _AGENT_CODE,NAME
FROM   GANGBOARD _AGENTS_DATA
WHERE  GANGBOARD _AGENT_CODE IN
SELECT GANGBOARD _AGENT_CODE
FROM   GANGBOARD _CUSTOMER
WHERE  GANGBOARD _CUSTOMERCITY = 'London')

Result:

SELECT GANGBOARD _CUSTOMERNAME
FROM   GANGBOARD _CUSTOMER C
WHERE EXISTS
(SELECT 1
 FROM   GANGBOARD _AGENTS_DATA A
 WHERE  COMM > 10000
 AND A. GANGBOARD _AGENT_CODE = C.BESANT_AGENT_CODE)
ORDER BY GANGBOARD _CUSTOMERNAME;

My sub query returns 1 only if the commercial value of Agent_code of customer table is matching and its more than that of 10000, and in main Query I list only customer names served by those agents.

Result:

SELECT ROUND(AVG(COMM_AVG), 0) As AVARAGE_COMM
FROM   (SELECT  AVG(COMM) COMM_AVG
FROM   BESANT_AGENTS_DATA );

Result:

Sub Query with DML Commands:

Sub Queries can also be used with DML commands of UPDATE, INSERT and DELETE. Let us look at few examples of those.

Sub Query with INSERT command:

Basically, this can be used only when you want to create a new table with subset of the records from the old table. This would be very useful and works as direct transfer of records from old to new table.

Example:

Let us assume that we have created a new table for all the Agents who has commercial more than that of 10000, we need not insert all of them newly. We can use a sub query along with insert those records from old table to new.

Query:

INSERT INTO NEW_AGENTS
SELECT * FROM  BESANT_AGENTS_DATA
WHERE COMM > 10000;

Sub Query with UPDATE command:

We can use sub Query in UPDATE statement when we want to set the new column value using the result of sub Query.

Example:

Let us say that we want to update all the columns in GANGBOARD _AGENT_DATA where COMM value is greater than its AVG value

UPDATE GANGBOARD _AGENTS_DATA
SET NATIONALITY='INDIAN'
WHERE COMM<
(SELECT  AVG(COMM)
  FROM  GANGBOARD _AGENTS_DATA);

Result:

10 rows affected.

Sub Query with DELETE command:

Sub Query with DELETE can be used when we want to use the result of Subquery to delete particular rows.

Example:

If we want to delete all the rows from GANGBOARD _AGENT_DATA with max of COMM  then write a sub query to fetch value of MAX of commercial and then compare it in main query to achieve the delete

Query:

DELETE FROM GANGBOARD _AGENTS_DATA
WHERE COMM=
(SELECT MAX(COMM) FROM BESANT_AGENTS_DATA);

Result:

1 rows affected.