Query SUBQUERY child in SQL Server

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

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:

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

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

3.3 ★ | 9 Vote