Exists in Correlated Subquery in SQL Server (Part -14)

Exists in Correlated Subquery in SQL Server :

how to use the SQL Server EXISTS operator in the condition to test for the existence of rows in a subquery.
The EXISTS operator is a logical operator that allows you to check whether a subquery returns any row. The EXISTS operator returns TRUE if the subquery returns one or more row.

As soon as the subquery returns rows, the EXISTS operator returns TRUE and stop processing immediately.

Example 1: Using EXISTS in correlated subquery:-
/*
This query uses EXISTS keyword in WHERE clause
to return a list of customers whose products
were ordered.

Note that the outer query only returns a row
where the subquery returns TRUE.
*/

select CustomerID, CompanyName from customers as a
where exists
(
select * from orders as b
where a.CustomerID = b.CustomerID

);

Customers

Orders

/*
This query uses INNER JOIN and returns the same result set as the query above. It shows you that the correlated subquery can be rewritten as join operation.
*/
select distinct a.CustomerID, a.CompanyName from customers as a inner join orders as b on a.CustomerID = b.CustomerID
or

select CustomerID, CompanyName from customers where CustomerID in(select CustomerID from Orders)

Видео Exists in Correlated Subquery in SQL Server (Part -14) канала Syed Ali