MS SQL Server: Query SUBQUERY Child in SQL Server

What is subquery in SQL (subquery)?

In SQL Server, a subquery is a query that resides in another query. You can create queries in SQL commands. These subqueries are in the WHERE, FROM or SELECT clause.

Note:

• Subqueries are also called INNER QUERY or INNER SELECT.
• The main query that contains a subquery is called OUTER QUERY or OUTER SELECT.

WHERE clause

typically the subquery is in the WHERE clause. These subqueries are called nested queries or subqueries.

 SELECT s.sanph am_id, s.sanpham_ten 
 FROM sanpham s 
 WHERE s.sanpham_id IN 
  (SELECT htk.sanpham_id 
  FROM hangtonkho htk 
  WHERE htk.soluong > 10); 

This is the sub-query segment in the SELECT statement above:

 (SELECT htk.sanp ham_id 
 FROM hangtonkho htk 
 WHERE htk.soluong > 10); 

The subquery allows to find sanpham_id values from the hangtonkho table with a number greater than 10. Then the subquery helps filter the result from the main query using the IN condition.

Subqueries can be written as INNER JOIN as shown below.

 SELECT s.sanpham_id, s. sanpham_ten 
 FROM sanpham s 
 INNER JOIN hangtonkho htk 
 ON s.sanpham_id = htk.sanpham_id 
 WHERE htk.soluong > 10; 

The INNER JOIN will return more efficiently than the original subquery. It is also important to note that no queries are rewritten by JOIN.

The FROM clause

 SELECT nhacu ng.nhacung_ten, truyvancon1.tong_sl 
 FROM nhacung, 
  (SELECT nhacung_id, SUM(donhang.soluong) AS tong_sl 
  FROM donhang 
  GROUP BY nhacung_id) truyvancon1 
 WHERE truyvanco n1.nhacung_id = nhacung.nhacung_id; 

In this example we have created the subquery in the FROM clause as follows:

 (SELECT nhacung _id, SUM(donhang.soluong) AS tong_sl 
 FROM donhang 
 GROUP BY nhacung _id) truyvancon1 

This subquery is also associated with the alias truyvancon1. This will be the name used to refer to this subquery or any of its information fields.

SELECT clause

typically the subquery is set in the SELECT clause to calculate by the sum functions such as SUM, COUNT, MIN or MAX but do not want to count in the main query.

 SELECT n1.ho, n1 .ten, 
  (SELECT MAX(luong) 
  FROM nhanvien n2 
  WHERE n1.nhanvien_id = n2.nhanvien_id) truyvancon2 
 FROM nhanvien n1 ; 

Below is the subquery created in the above example

 (SELECT MAX ong) 
 FROM nhanvien n2 
 WHERE n1.nhanvie n_id = n2.nhanvien_id) truyvancon2