IN versus EXIST

When you want to make a query faster you can look -

EXISTS is more faster than IN.
Because EXISTS returns a Boolean value whereas IN returns a value.

Example:

Wrong:
SELECT COUNT(distinct emp.id) AS emp_id FROM emp
LEFT JOIN program_client ON (emp.client_id = program_client.client_id
AND emp.prov_id = program_client.prov_id)
WHERE program_client.id IN
(
SELECT pc_id FROM pgm_role ar
WHERE ar.login_id = 100 AND ar.role = 1
)
AND program_client.status = 1
AND emp.status IN (1,6) AND emp.prov_id = 580;


Right:
SELECT COUNT(emp.id) AS emp_id FROM emp
WHERE EXISTS
(
SELECT psr.client_id FROM TABLE2 psr
(WHERE emp.client_id = psr.client_id AND psr.login_id = 100
AND psr.role = 1 AND psr.status = 1
)
AND emp.status IN (1,6) AND emp.prov_id = 580;

So -

• Syntax: SELECT columns FROM tables WHERE EXISTS ( subquery );
• Used 'EXISTS' instead of 'Distinct'. Which works Faster.
• The subquery returns distinct result. It's like 'IN' operation.
• But 'IN' takes more time as 'EXISTS' just check for true/false but 'IN' returns the result then compare.
• The EXISTS condition is considered "to be met" if the subquery returns at least one row.
• The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.
• You can also use 'NOT EXISTS'.