Subquery - SUBQUERY in SQL

The following article will show you how to use subquery in SQL along with specific syntax and examples.

SUBQUERY - Subquery is a query within another SQL query and is embedded in the WHERE clause. This element is also called a subquery or nested query.

The subquery returns the data to be used in the main query, which is treated as a condition to narrow the acquisition data.

Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements with operators such as =, <,>,> =, <=, IN, BETWEEN .

Subqueries must follow the following rules:

1. SUBQUERY must be in parentheses.
2. A SUBQUERY can have only one column in the SELECT clause, unless multiple columns in the main query for SUBQUERY to compare its selected columns.
3. An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. GROUP BY is used to perform an ORDER BY feature in a subquery.
4. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
5. SELECT list cannot include any references to values ​​evaluated BLOB, ARRAY, CLOB or NCLOB.
6. Subqueries cannot be accompanied by set functions.
7. The BETWEEN operator cannot be used with a subquery but can be used within subqueries.

SUBQUERY with SELECT command in SQL

Frequent subqueries are used with SELECT statements . The basic syntax is as follows:

 SELECT ten_cot [, ten_cot ] 
 FROM bang1 [, bang2 ] 
 WHERE ten_cot OPERATOR 
  (SELECT ten_cot [, ten_cot ] 
  FROM bang1 [, bang2 ] 
  [WHERE]) 

Considering the table NHANVIEN has the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 1 | Thanh | 32 | Haiphong | 2000.00 | | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

Now, we check the subquery with the SELECT statement as follows:

 SQL> SELECT * 
  FROM NHANVIEN 
  WHERE ID IN (SELECT ID 
  FROM NHANVIEN 
  WHERE LUONG > 4500); 

The result is:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 4 | Manh | 25 | Hue | 6500.00 | | 5 | Huy | 27 | Hatinh | 8500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

SUBQUERY with INSERT command in SQL

Subqueries can also be used with INSERT statements . The INSERT statement uses the data returned from the subquery to insert into another table. The data selected in the subquery can be modified by any character, date / time function or number function.

The basic syntax is as follows:

 INSERT INTO ten_bang [ (cot1 [, cot2 ]) ] 
  SELECT [ *|cot1 [, cot2 ] 
  FROM bang1 [, bang2 ] 
  [ WHERE GIA_TRI TOAN_TU ] 

Follow the NHANVIEN_QTM table with the same structure as NHANVIEN table . Now, copy the entire NHANVIEN table into the NHANVIEN_QTM table , you can use the following syntax:

 SQL> INSERT INTO NHANVIEN_QTM 
  SELECT * FROM NHANVIEN 
  WHERE ID IN (SELECT ID 
  FROM NHANVIEN); 

SUBQUERY with UPDATE command in SQL

You can use subqueries associated with UPDATE statements . One or more columns in a table can be updated using a subquery with the UPDATE statement.

The basic syntax is as follows:

 UPDATE bang 
 SET ten_cot = giatri_moi 
 [ WHERE TOAN_TU [ GIA_TRI ] 
  (SELECT TEN_COT 
  FROM TEN_BANG) 
  [ WHERE) ] 

Suppose we have the available NHANVIEN_QTM table as a backup table of NHANVIEN. The following example updates LUONG twice in the NHANVIEN table for all customers with LUONG greater than or equal to 27:

 SQL> UPDATE NHANVIEN 
  SET LUONG = LUONG * 2 
  WHERE TUOI IN (SELECT TUOI FROM NHANVIEN_QTM 
  WHERE TUOI >= 27 ); 

This command affects two rows and finally the NHANVIEN table will have the following records:

SUBQUERY with DELETE command in SQL

Subqueries are also used with the DELETE statement and we have the following basic syntax:

 DELETE FROM TEN_BANG 
 [ WHERE TOAN_TU [ GIA_TRI ] 
  (SELECT TEN_COT 
  FROM TEN_BANG) 
  [ WHERE) ] 

Suppose we have the available NHANVIEN_QTM table which is the backup of NHANVIEN table . The following example will delete records from the NHANVIEN table with TUOI greater than or equal to 27.

 SQL> DELETE FROM NHANVIEN 
  WHERE TUOI IN (SELECT TUOI FROM NHANVIEN_QTM 
  WHERE TUOI >= 27); 

This command affects two rows and finally the NHANVIEN table will have the following records:

 +----+----------+-----+-----------+----------+ | ID | TEN |TUOI | DIACHI | LUONG | +----+----------+-----+-----------+----------+ | 2 | Loan | 25 | Hanoi | 1500.00 | | 3 | Nga | 23 | Hanam | 2000.00 | | 4 | Manh | 25 | Hue | 6500.00 | | 6 | Cao | 22 | HCM | 4500.00 | | 7 | Lam | 24 | Hanoi | 10000.00 | +----+----------+-----+-----------+----------+

In the next section, we will learn how to use SQL Sequece , please keep track.

Previous post: Handling copy - HANDLING DUPLICATE in SQL

Next post: SEQUENCE in SQL