SELECT *
FROM orders o
WHERE o.user_id IN (
  SELECT u.id FROM users u WHERE u.country = 'US'
);

SELECT *
FROM orders o
WHERE EXISTS (
  SELECT 1
  FROM users u
  WHERE u.id = o.user_id
    AND u.country = 'US'
);

SELECT
  COUNT(CASE WHEN status = 'success' THEN 1 END) AS success_cnt,
  COUNT(CASE WHEN status = 'error' THEN 1 END)   AS error_cnt
FROM events;

Копировать код
SELECT
  COUNT(*) FILTER (WHERE status = 'success') AS success_cnt,
  COUNT(*) FILTER (WHERE status = 'error')   AS error_cnt
FROM events;

SELECT *
FROM users u
WHERE u.id NOT IN (
  SELECT user_id
  FROM banned_users
);

SELECT *
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM banned_users b
  WHERE b.user_id = u.id
);

-- Плохо
SELECT COUNT(*)
FROM orders
WHERE user_id = 123;

-- Хорошо
SELECT EXISTS (
    SELECT 1
    FROM orders
    WHERE user_id = 123
);

-- Или в условии
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);