Using EXISTS and NOT EXISTS
Introduction The EXISTS clause is used to solve a single problem in data logic which is checking the existence of data based on a condition, without worrying about how many times it exists or what the specific value is. How it works Just like when using IN with EXISTS , Postgres will also automatically use Semi-Join algorithms (or Anti-Semi-Join for NOT EXISTS ) Semi Join (EXISTS) : Returns rows from the main table (left table) if there is at least one matching row in the sub-table. The optimizer stops searching as soon as it finds the first matching row for each record of the main table. Anti Join (NOT EXISTS) : Contrary to Semi Join , it returns rows from the main table if no matching rows are found in the sub-table. It still uses popular algorithms such as Nested Loop Semi Join, Hash Semi Join, Merge Semi Join with a Short-circuit mechanism, meaning that for each row value, it only needs to iterate until the first value is found, rather than finding all matching values like wh...