A subquery is a query inside another. A scalar subquery returns one value and can sit anywhere a value belongs. A row subquery returns one row of several columns. A subquery in FROM - a derived table - returns a result set you then query, and must be given an alias.
A correlated subquery refers to the outer query, so it is evaluated once per outer row. That makes it expressive and potentially slow: a correlated subquery over a million-row outer query is a million executions, and the usual rewrite is a join or a derived table.
EXISTS tests whether a subquery returns any row at all, and stops at the first one it finds. For "does this customer have an order", EXISTS is normally faster than IN with a subquery, because IN may build the whole list first.
The real reason to prefer NOT EXISTS over NOT IN is correctness, not speed. If the subquery returns a single NULL, NOT IN returns no rows at all - because "is this value not in a list containing an unknown" cannot be answered as true. A query that worked for a year quietly returns nothing the day one NULL appears in that column. NOT EXISTS is immune, and should be the default.
-- Scalar: one value, usable anywhere a value belongs.
SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c;
-- Derived table: a result set you query. It MUST have an alias.
SELECT city, AVG(order_count) AS avg_orders
FROM (
SELECT c.city, c.id, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.city, c.id
) AS per_customer
GROUP BY city;
-- Correlated: evaluated once per outer row. Expressive, and a million
-- executions on a million-row outer query.
SELECT c.name
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 5;
-- EXISTS stops at the first match.
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- THE CORRECTNESS TRAP. If any customer_id in orders is NULL, this
-- returns ZERO rows - not "customers without orders". It worked until
-- the day one NULL appeared.
-- SELECT * FROM customers
-- WHERE id NOT IN (SELECT customer_id FROM orders);
-- NOT EXISTS is immune, and should be the default.
SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);