Subqueries and EXISTS in MariaDB

Subqueries and EXISTS are powerful tools in MariaDB that enable us to write complex and efficient queries. Subqueries allow us to nest one query inside another and provide a flexible way to retrieve data. The EXISTS operator, on the other hand, it checks for the existence of rows returned by a subquery. In this article, we will look at what subqueries are, how they work, and how the EXISTS operator can be used to extend their functionality in MariaDB.

What are Subqueries?

A subquery, commonly referred to as a nested or inner query, is a query within another SQL statement, such as SELECT, INSERT, UPDATE, and DELETE that sometimes references an outer query. Rather, a subquery's outcome can be utilized in different parts of the main query including the WHERE clause, FROM clause, or another subquery.

Syntax:

SELECT column1 FROM table1 WHERE column2 = (SELECT column3 FROM table2 WHERE condition);

Explanation: In this syntax, the subquery (SELECT column3 FROM table2 WHERE condition) is enclosed within parentheses and returns a single value. This value is then compared to column2 in the outer query's WHERE clause.

What is an EXISTS Operator?

The EXISTS operator makes the subquery returns only the records that are present in the result set of the of query. It will return TRUE if the sub-query has one or more rows, if not it will return FALSE. EXISTS operator is regularly employed in conjunction with correlated sub-queries, where the first query is related to outer columns.

Syntax:

SELECT column1 FROM table1 WHERE EXISTS (SELECT * FROM table2 WHERE table2.column = table1.column);

Explanation: In the above Syntax, the EXISTS operator checks whether there are any rows in table2 that match the condition specified in the subquery. If at least one row exists, the outer query returns true.

Examples of Subqueries with Exists Operator

Here are the some examples of using subqueries with exists operator:

Let's first create table and insert values into it.

Create table customers:

CREATE TABLE customers (
    customers_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Insert data:

INSERT INTO customers (name, age) VALUES
('Minal', 25),
('Harsha', 30),
('Asad', 35),
('Mridul', 40);

Output:

Explanation: As we can see our table got created successfully.

Create table orders.

CREATE TABLE orders(
order_id INT AUTO_INCREMENT PRIMARY KEY,
customers_id INT,
order_date DATE,
total_amount DECIMAL(10, 2),
FOREIGN KEY (customers_id) REFERENCES customers(customers_id)
);

Insert data:

INSERT INTO orders (customers_id, order_date, total_amount) VALUES
(1, '2024-01-01', 50.00),
(2, '2022-01-19', 100.00),
(3, '2024-01-15', 150.00),
(4, '2021-01-04', 200.00);

Output:

Explanation: As we can see table got created successfully.

Example 1: Using EXISTS to Find Customers Older Than 30.

This query provides the names of customers who have placed orders but are more than 30 years old.

Query:

SELECT NAME FROM customers c WHERE EXISTS 
(SELECT * FROM orders WHERE customers_id = c.customers_id) AND c.age > 30;

Output:

Explanation:

• SELECT NAME FROM customers c: It selects the name column from the customers table and as customers the aliases.
  
• WHERE EXISTS (SELECT * FROM orders WHERE customers_id = c.customers_id): This is a subquery that checks if there exists at least one record in the orders table where the customers_id matches the customers_id of the outer query (c.customers_id). It checks if the customer has any orders.
  
• AND c.age > 30: Such a condition restricts the result set to only include customers whose age (c.age) is greater than 30.

Example 2: Using EXISTS to Find Customers Whose Name Starts with 'M'

It finds the customers' names that have the first letter ‘M’ and who has at least one other customers with the same name beginning with ‘M’.

Query:

SELECT name FROM customers u WHERE EXISTS 
(SELECT * FROM customers WHERE name LIKE 'M%' AND customers_id = u.customers_id);

Output:

Explanation:

• SELECT name FROM customers u: This will select the name column from the customer table and aliases it as u.
  
• WHERE EXISTS (SELECT * FROM customers WHERE name LIKE 'M%' AND customers_id = u.customers_id): This is the subquery that illustrates if at least one other customer and who is a group other than the one in the outer query, the name of which begins with 'M' and customers_ids matches the customers_ids of the outer one.
  
• The subquery displays customers with beginning with 'M' and interactions from the same interactions_id value of the customer in the outer query.
  
• Hence, if there is such an having to carry out then the EXISTS condition is met and the outer query (u.name ) is contained in the final result set.

Example 3: Using EXISTS to Find Customers Who have Made Orders with a Total Amount Greater Than 100.

This SQL query fetches names of customers who have made orders with a total sum greater than $100.

Query:

SELECT name FROM customers u WHERE EXISTS 
(SELECT * FROM orders WHERE customers_id = u.customers_id AND total_amount > 100);

Output:

Explanation:

• SELECT name FROM customers u: This selects the name field from the customers table and aliases it as u.
  
• WHERE EXISTS (SELECT * FROM orders WHERE customers_id = u.customers_id AND total_amount > 100): This is a the query and subquery inherently is that one or more than one record exists in the orders list if the following holds:
  
• The equivalent of the subquery's customers_id (u.customers_id) is equal to the customers' id of the outer query.
  
• The the quantity of the entire order is larger than $100 (100<amount_total).
  
• This will involve the access of a record, satisfying the 'IF' condition in our outer query, and the customer's name (u.name) will also be fetched along.

Example 4: Using EXISTS to Find Customers Who have Made Orders in January 2024.

This query will only fetch those clients whose orders were placed in January 2024.

Query:

SELECT name FROM customers u WHERE EXISTS 
(SELECT * FROM orders WHERE customers_id = u.customers_id 
AND 
YEAR(order_date) = 2024 AND MONTH(order_date) = 1);

Output:

Explanation:

• SELECT name FROM customers u: This is the outer query that selects the name column from the customers table and this becomes the u alias.
  
• WHERE EXISTS (SELECT * FROM orders WHERE customers_id = u.customers_id AND YEAR(order_date) = 2024 AND MONTH(order_date) = 1): This is a subquery, which is looking for the order_id, if there is the at least one record in the orders table for which (order_id):
  
• The joins_statement.customers_id = u.customers_id, which is a matching square cabinet of the queries to the outer query (u.customers_id).
  
• We shall find the order date in January of 2024. This query will check whether the user gives correct order month and year by casting order date Year() and order_date Month() and compare them with 2024 and 1 respectively.
  
• If the above mentioned record is there, EXISTS condition will be satisfied and the outer query will include customer name (u.name) in the final result set.

Advantages of Subqueries and EXISTS Operator

• Modularity: Subqueries provide code encapsulation resulting in easier to understand complex queries.
  
• Improved Performance: If carefully optimized, subqueries improve query performance because the database engine executes the inner query and then uses its result further.
  
• Conditional Filtering: EXISTS is used to filter rows conditionally based on the existence of rows in a subquery result, which makes query construction flexible.

Conclusion

Subqueries and EXISTS operator are important parts of MariaDB which allow developers to write powerful SQL queries. Through the knowledge of how to invoke subqueries and EXISTS you can make your database operations easier to read and more flexible in addition to faster. Practice with the presented ideas in your own projects in order to gain their full advantage and improve your SQL queries.