SQL sub-query

SQL sub-query

---

Sub Query, or Inner Query, also known as Nested Query, is a query nested in the WHERE clause of other SQL queries.

Sub-queries are used to return the data they need for the primary query, or to further restrict the retrieval of data.

Sub-queries can be used in SELECT, INSERT, UPDATE, and DELETE statements with operators such as .

There are several rules that must be followed when using sub-queries:

• Sub-queries must be enclosed in parentheses.
• There can only be one column in the SELECT clause of a subs query, unless there are more than one column in the main query to compare with the columns selected by the sub-query.
• The child query cannot use ORDER BY, but the main query can. In sub-queries, GROUP BY can do the same thing as ORDER BY.
• Sub-queries that return multiple rows of data can only be used with multi-value operators, such as IN operators.
• The SELECT list cannot contain any references to BLOB, ARRAY, CLOB, or NCLOB type values.
• Sub-queries cannot be used directly in aggregate functions.
• The BETWEEN operator cannot be used with a child query, but the BETWEEN operator can be used in a child query.

Sub-query in select statement

---

Typically, sub-queries are used with SELECT statements, and the basic syntax is as follows:

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

Example:

---

Consider the CUSTOMERS table, which is recorded as follows:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Now, let's try sub-querying in the SELECT statement:

SQL> SELECT * 
     FROM CUSTOMERS 
     WHERE ID IN (SELECT ID 
                  FROM CUSTOMERS 
                  WHERE SALARY > 4500) ;

The result of the above statement is as follows:

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

Sub-queries in INSERT statements:

---

Child queries can also be used in INSERT statements. I NSERT statements can insert data returned by sub-queries into other tables. The data selected in the subquery can be modified by any character, date, or numeric function.

The basic syntax is as follows:

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]

Example:

---

Consider a table that has a similar structure to CUSTOMERS_BKP customers table. Now to copy all the data from the CUSTOMER table to the CUSTOMERS_BKP table, the code is as follows:

SQL> INSERT INTO CUSTOMERS_BKP
     SELECT * FROM CUSTOMERS 
     WHERE ID IN (SELECT ID 
                  FROM CUSTOMERS) ;

Sub-queries in UPDATE statements:

---

Child queries can be used in UPDATE statements. When a child query is used with UPDATE, you can update neither a single column nor multiple columns.

The basic syntax is as follows:

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example:

---

Suppose we have a CUSTOMERS_BKP as a backup of the CUSTOMERS table.

The following example changes the SALARY field for all customers with AGE greater than or equal to 27 in the CUSTOMERS table to 0.25 times the original:

SQL> UPDATE CUSTOMERS
     SET SALARY = SALARY * 0.25
     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
                   WHERE AGE >= 27 );

This affects two rows of data, and then the records in the CUSTOMERS table look like this:

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |   125.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  2125.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Sub-queries in DELETE statements:

---

As with the other statements mentioned earlier, subsysdries can also be used with DELETE statements.

The basic syntax is as follows:

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Example:

---

Suppose we have a CUSTOMERS_BKP as a backup of the CUSTOMERS table.

The following example removes all records with AGE greater than or equal to 27 from the CUSTOMERS table:

SQL> DELETE FROM CUSTOMERS
     WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
                   WHERE AGE > 27 );

This affects two rows of data, and then the records in the CUSTOMERS table look like this:

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  2 | Khilan   |  25 | Delhi   |  1500.00 |
|  3 | kaushik  |  23 | Kota    |  2000.00 |
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+