{"id":22266,"date":"2023-06-09T11:39:06","date_gmt":"2023-06-09T11:39:06","guid":{"rendered":"https:\/\/machinelearningplus.com\/?p=22266"},"modified":"2023-06-10T19:15:01","modified_gmt":"2023-06-10T19:15:01","slug":"sql_in","status":"publish","type":"post","link":"https:\/\/machinelearningplus.com\/sql\/sql_in\/","title":{"rendered":"SQL IN – SQL IN with Practical Examples"},"content":{"rendered":"

Let’s get a handle on one of its powerful operators, the SQL IN operator. I’m going to keep this as simple as possible, while still covering everything you need to know.<\/strong><\/p>\n

What is the SQL “IN” Operator?<\/h2>\n

In SQL, the IN<\/code> operator allows you to specify multiple values in a WHERE<\/code> clause. Essentially, it’s a shorthand for multiple OR<\/code> conditions. This might not seem like a big deal, but when you’re dealing with complex databases with many records, this small efficiency can have a huge impact.<\/p>\n

Basic Syntax of SQL IN<\/h1>\n

The basic syntax of SQL IN<\/code> is pretty straightforward<\/p>\n

SELECT column_name(s)\nFROM table_name\nWHERE column_name IN (value1, value2, ...);\n<\/code><\/pre>\n
In this syntax, you can see that the IN<\/code> operator is used in the WHERE<\/code> clause to filter the records. The values in the parentheses (value1, value2, …)<\/strong> represent the values you’re interested in.<\/p>\n
A Practical Example of SQL “IN”<\/h2>\n
Let’s illustrate the SQL IN<\/code> operator with an example. Suppose you’re working with a table called Employees<\/code><\/p>\n
Employee_ID | First_Name | Last_Name | Department\n----------------------------------------------\n1           | John       | Doe       | IT        \n2           | Jane       | Smith     | Marketing \n3           | Sara       | Jones     | HR        \n4           | James      | Davis     | IT        \n5           | Linda      | Taylor    | Sales\n<\/code><\/pre>\n
Imagine that you want to find the details of employees who work in either the ‘IT’ or ‘Sales’<\/strong> department. You could certainly use the OR<\/code> operator, like so<\/p>\n
SELECT *\nFROM Employees\nWHERE Department = 'IT' OR Department = 'Sales';\n<\/code><\/pre>\n
However, using the IN<\/code> operator simplifies this query<\/p>\n
SELECT *\nFROM Employees\nWHERE Department IN ('IT', 'Sales');\n<\/code><\/pre>\n
Both of these queries return the same results<\/p>\n
Result:<\/p>\n
Employee_ID | First_Name | Last_Name | Department\n----------------------------------------------\n1           | John       | Doe       | IT        \n4           | James      | Davis     | IT        \n5           | Linda      | Taylor    | Sales\n<\/code><\/pre>\n
As you can see, the IN<\/code> operator is not only easier to write but also makes your SQL statement cleaner and easier to read.<\/p>\n
SQL IN with Subquery<\/h2>\n
Another way you can use the IN<\/code> operator is with a subquery. A subquery is a SQL query nested inside a larger query. Let’s say you have another table called Departments<\/code><\/p>\n
Employees Table:\n\nEmployee_ID | First_Name | Last_Name | Department\n----------------------------------------------\n1           | John       | Doe       | IT        \n2           | Jane       | Smith     | Marketing \n3           | Sara       | Jones     | HR        \n4           | James      | Davis     | IT        \n5           | Linda      | Taylor    | Sales\n\nDepartments Table:\n\nDepartment_ID | Department | Manager\n-------------------------------------\n1             | IT         | John Doe\n2             | Marketing  | Jane Smith\n3             | HR         | Sara Jones\n4             | Sales      | Linda Taylor\n<\/code><\/pre>\n
And you want to find all employees who are managers. You could do this using the IN<\/code> operator with a subquery<\/p>\n
SELECT *\nFROM Employees\nWHERE First_Name + ' ' + Last_Name IN (SELECT Manager FROM Departments);\n<\/code><\/pre>\n
Result:<\/p>\n
Employee_ID | First_Name | Last_Name | Department\n----------------------------------------------\n1           | John       | Doe       | IT        \n2           | Jane       | Smith     | Marketing \n3           | Sara       | Jones     | HR        \n5           | Linda      | Taylor    | Sales\n<\/code><\/pre>\n
This query first retrieves the list of managers from the Departments<\/code> table, then uses the IN<\/code> operator to filter the Employees<\/code> table based on that list.<\/p>\n
Conclusion<\/h2>\n
That’s the basic idea behind the SQL IN<\/code> operator. It provides an efficient way to check if a certain column’s value is in a list of specified values. Its biggest strengths are its readability and its compatibility with subqueries, which are essential when working with large, complex databases.<\/p>\n
Related Topics<\/h2>\n