MariaDB uses SQL (Structured Query Language) and it is an open-source relational database management system (RDBMS) for managing and manipulating data. IN operator is one of the useful operators for data retrieval in MariaDB. It allows you to specify a list of values in a WHERE clause to filter the result set based on those values. In this article, we will go through the multiple examples of the IN operator in MariaDB.
IN Operator
It is also a logical operator that compares a value with a list of values logically. You can include more than one value in a WHERE clause by using the IN operator. Multiple OR conditions can be reduced to the IN operator.
Syntax:
SELECT field1, field2,.. FROM table_name WHERE column_name IN(value1, value2,…);
The column you wish to filter in this case is called column_name, and the list of values to match against is (value1, value2,…).
Example of Queries using IN Operator in MariaDB
Query 1: Filter by String Values
SELECT product_name FROM products WHERE category IN ('Electronics', 'Appliances');
This query will retrieve the names of products from the products table where category is either Electronics or Appliances.
Output:
IN Operator
Query 2: Using AND Operator
SELECT product_id, category , unit_price FROM products WHERE product_id IN (1,2,3) AND stock_quantity > 20;
This query will give a list of product_id, category and unit_price from the products table where product_id is either 1, 2 or 3 and stock_quantity is greater than 20.
Output:
IN Operator
Query 3: Filter Using Multiple Columns
SELECT product_name FROM products WHERE (product_id, category) IN ((1, 'Electronics'), (2, 'Electronics'), (3, 'Appliances'));
This query will give the list of product names which belongs to specified id and category.
Output:
IN Operator
Query 4: Filtering With Character Values
SELECT * FROM products WHERE product_name IN ('Laptop', 'Smartphone', 'Coffee Maker');
This query will retrieve all the columns from the product table where product_name is either Laptop, Smartphone or Coffee Maker.
Output:
IN Operator
Query 5: Using Not Operator
SELECT * FROM products WHERE product_name NOT IN ('Laptop', 'Smartphone');
Output:
IN Operator
Conclusion
The IN function in MariaDB is a valuable tool for filtering data by multiple objectives, providing simplicity, performance gains, and flexibility in executing dynamic queries Whether you work with small data sets or large databases understanding and using the IN function can increase the performance and the readability of your SQL queries.