IN and EXISTS Operators: What's the Difference? (Basic 1 - 15)

The IN and EXISTS operators in SQL are both used to compare values between two tables. However, there are some key differences between the two operators.

IN operator

The IN operator is used to check if a value in one column is present in a list of values in another column. The syntax for the IN operator is as follows:

SQL

SELECT * FROM table_name WHERE column_name IN (list_of_values);

The list_of_values can be a list of literal values or a subquery.

Example:

SQL

SELECT * FROM employees WHERE salary IN (10000, 20000, 30000);

This query will return all employees with a salary of 10000, 20000, or 30000.

EXISTS operator

The EXISTS operator is used to check if a row exists in another table that matches the criteria in the current table. The syntax for the EXISTS operator is as follows:

SQL

SELECT * FROM table_name WHERE EXISTS (subquery);

The subquery can be any valid SQL query.

Example:

SQL

SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE department_id = employees.department_id);

This query will return all employees who work in a department.

Differences between IN and EXISTS operators

The main difference between the IN and EXISTS operators is that the IN operator checks for a specific value in a list of values, while the EXISTS operator checks for the existence of any rows in another table that match the criteria in the current table.

Another difference between the two operators is that the IN operator can be used to compare values to a list of literal values or a subquery, while the EXISTS operator can only be used to compare values to a subquery.

Finally, the EXISTS operator is generally faster than the IN operator, especially when the subquery is large.

When to use IN and EXISTS operators

The IN operator should be used when you need to check if a value is present in a specific list of values. The EXISTS operator should be used when you need to check for the existence of any rows in another table that match the criteria in the current table.

Here are some specific examples of when to use each operator:

• Use the IN operator to check if a product is in stock.
• Use the IN operator to check if a customer is eligible for a discount.
• Use the EXISTS operator to check if an order has been placed.
• Use the EXISTS operator to check if a user has logged in.

Conclusion

The IN and EXISTS operators are both useful tools for comparing values between two tables. By understanding the differences between the two operators, you can choose the right operator for your needs.