The following article will show you how to use sub-queries in SQL Server along with syntax and examples.

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:

WHERE clause

Usually 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 is used to 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

Usually 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 

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

The trick of placing subqueries in the SELECT clause is used because the subquery returns a single value. That's why the sum functions such as SUM, COUNT, MIN or MAX are often used in subqueries.

Previous article: EXCEPT Operator in SQL Server

The following article: PIVOT clause in SQL Server

ncG1vNJzZmismaXArq3KnmWcp51kvrax0bJkrK2SpsKmvthmmqGhnJl6qrqMrKilZaOav7ex0Q%3D%3D