in/exists predicate sub-queries can only

be used within a WHERE clause in SQL. These sub-queries allow you to check for the existence or non-existence of records in a related table. For example, you may want to find all customers who have never placed an order, or all orders that have not been shipped yet.

The syntax for an IN/EXISTS sub-query is as follows:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (subquery);

or

SELECT column_name(s)
FROM table_name
WHERE column_name IN (subquery);

The sub-query returns a set of values that are compared to the main query. If any values are returned by the sub-query, the EXISTS operator returns true, and the main query returns the selected columns. If no values are returned by the sub-query, the EXISTS operator returns false, and the main query returns no result. The IN operator works similarly, but it compares a single column to the set of values returned by the sub-query.