SQL Select In
SQL IN is an operator used in a SQL query to help reduce the need to use multiple SQL “OR” conditions. The IN operator allows you to specify multiple values in a WHERE clause. It is used in SELECT, INSERT, UPDATE, or DELETE statements.
Example:
Expression IN (value 1, value 2 … value n);
We have a table called Employee with four columns:
Take an example with character values.
SELECT * FROM Employee WHERE Emp_Name IN (Ankit, Bheem, Sumit);
The result will be shown as this table:
The result will be shown as this table:
SELECT * FROM Employee WHERE Emp_Salary IN (25000, 30000, 40000);
The result will be shown as this table:
Course Video
NOTE: Practice below questions on site editor.
1. Write SQL Query to get all the records from Person_Person table whose BusinessEntityID is 1, 5 and 10 using IN operator.
Table: (Person_Person)-(3 row count)
2. Write SQL Query to get all the records from Production_Culture table where Name is ‘Arabic’,
‘English’,’Thai’ using IN operator
Table: (Production_Culture)-(3 row count)
3. Make a SQL Query to find Name, ShiftID from HumanResource_Shift table where Name is (Day, Night, Evening) using IN operator.
Table: (HumanResource_Shift)-(3 row count)
4. Write SQL Query to get all the columns from HumanResources_EmployeePayHistory table where BusinessEntityID is (1,2,3,4) and Rate is less than 50 Using IN operator.
Table: (HumanResources_EmployeePayHistory)-(4 row count)
5. Write a SQl Query to get all the records from HumanResources_EmployeePayHistory table where BusinessEntityID is (1,2,3,4,5,6) and Rate is grater than 50 Using IN operator.
Table : (HumanResources_EmployeePayHistory)-(6 row count)
6. Write an SQL query to retrieve all columns from the ‘Employee’ table for employees with BusinessEntityID is 21, 22, 23, 24, 25, 26 and MaritalStatus equal to ‘M’. [OUTPUT: 4 ROWS]
7. Write an SQL query to retrieve all columns from the ‘PurchaseOrderDetail’ table where the OrderQty is 550 and 4. [output: 46 rows]
8. Write an SQL query to retrieve all columns from the ‘Purchasing_PurchaseOrderHeader’ table where the EmployeeID is 261, 251 and subtotal is greaterthan 1000. [ OUTPUT: 11 ROWS]
9. Write an SQL query to retrieve all columns from the ‘SalesOrderDetail’ table where the SalesOrderID is either 43659, 43665, or 43668, and the UnitPrice is greater than 100.[ OUTPUT: 27 ROWS ]
10. Write an SQL query to retrieve all columns from the ‘SalesReason’ table where the ReasonType is ‘other’ and ‘promotion’.[OUTPUT: 6 ROWS ]
YouTube Reference :
1) SQL Select In in Hindi/Urdu
2) SQL Select In in English
The SQL SELECT IN clause allows you to filter query results by matching values in a list. It simplifies conditions compared to multiple OR statements.
The basic syntax is:
sql
Copy code
SELECT column_name
FROM table_name
WHERE column_name IN (value1, value2, …)
For example, SELECT * FROM Employees WHERE department IN (‘HR’, ‘Finance’); retrieves all employees in HR and Finance departments
- Using IN with a Subquery:
sql
Copy code
SELECT * FROM Employees
WHERE department_id IN (SELECT id FROM Departments WHERE location = ‘New York’);
- Combining IN with NOT:
sql
Copy code
SELECT * FROM Products WHERE category_id NOT IN (1, 2, 3);
Yes, SQL SELECT IN is ideal for filtering rows based on multiple values in a single query. It’s more concise and readable than chaining multiple OR conditions.
- Using data types that don’t match between the column and the list values.
- Forgetting to enclose string values in single quotes.
- Applying IN with empty lists, which can cause unexpected results.
- IN: Filters based on a specific list of values.
- BETWEEN: Filters based on a range of values, such as numbers or dates.
Yes, SELECT IN works well with other clauses like ORDER BY, GROUP BY, and aggregate functions. For example:
sql
Copy code
SELECT COUNT(*) FROM Orders
WHERE status IN (‘Shipped’, ‘Delivered’)
GROUP BY customer_id;
Yes, our website offers a detailed SQL SELECT IN Tutorial with step-by-step examples for beginners and advanced learners.
The case sensitivity depends on the database configuration. For example:
- MySQL: Case-insensitive by default.
- PostgreSQL: Case-sensitive unless explicitly set otherwise.
- Verify that list values match the column’s data type.
- Check for missing or extra parentheses.
