EXISTS condition in SQL Server

In SQL Server (Transact-SQL) condition EXISTS is correct to associate with the internal query (subquery). The condition is met if the internal query returns at least 1 row. This condition can be used in SELECT, INSERT, UPDATE or DELETE commands.

EXISTS clause syntax in SQL Server

 WHERE EXISTS ( subquery); 

Variable name or variable value

subquery

Subquery - internal query is a SELECT command. If this query returns at least 1 record in the result set, the EXISTS clause is evaluated as true and the EXISTS condition is met. If the internal query does not return any records, the EXISTS clause is evaluated as false and the EXISTS condition is not met.

Note

Use an inefficient EXISTS condition due to internal RE-RUN query (rerun) on each row in the table in the external query. There are more effective ways without using EXISTS conditions.

For example - with SELECT statement

The SELECT statement is used with EXISTS conditions as shown below.

 SELEC T * 
 FROM nhanvien 
 WHERE EXISTS (SELECT * 
  FROM danhba 
  WHERE nhanvien.ho = danhba.ho 
  AND nhanvien.t en = danhba.ten); 

This example will return all records from the table of names when there is at least one table in the list of names that has the first and last names in the table.

For example - SELECT command uses NOT EXISTS

The EXISTS condition can be combined with the NOT operator.

 SELEC T * 
 FROM nhanvien 
 WHERE NOT EXISTS (SELECT * 
  FROM danhba 
  WHERE nhanvien.ho = danhba.ho 
  AND nhanvien .ten = danhba.ten); 

The returned result is all the records in the table of names if there is no record of family names and names in the list that match the first and last names in the table.

Example - INSERT command

This is an example of using the INSERT command with the EXISTS condition

 INSERT INTO danhba 
 (danhba_id, danhba_ten) 
 SELECT nhacung_id, nhacung_ten 
 FROM nhacung 
 WHERE EXISTS (SELECT * 
  FROM donhang 
  WHERE nhacung.n hacung_id = donhang.nhacung_id); 

Example - UPDATE command

Below is an example of an UPDATE statement using the EXISTS condition.

 UPDATE of acung 
 SET nhacung_ten = (SELECT khachhang.ten 
  FROM khachhang 
  WHERE khachhang.khachhang_id = nhacung.nhacung_id) 
 WHERE EXISTS (SELECT khachhang.ten 
  FROM khachhang 
  WHERE khachhan g.khachhang_id = nhacung.nhacung_id); 

Example - DELETE command

The DELETE command can also be used with EXISTS conditions as shown below.

 DELETE FR OM danhba 
 WHERE EXITS (SELECT * 
  FROM nhanvien 
  WHERE nhanvi en.ho = danhba.ho); 

Last lesson: TRUNCATE TABLE command in SQL Server

The following article: GROUP BY clause in SQL Server