Subqueries in MariaDB can be used in various ways, including in the SELECT, FROM, WHERE, and JOIN sections of a query. Here’s how to use them:
1. Subquery in the SELECT section: In this case, the subquery is used as an additional column to return additional information.
```
SELECT customer_id,
(SELECT COUNT FROM orders WHERE customer.customer_id = orders.customer_id) as order_count
FROM customer;
```
In this example, the subquery is used to display an additional column “order\_count” displaying a count of orders for each customer.
1. Subquery in the FROM section: Here, the subquery is used to create a temporary table that is then used for the main query.
```
SELECT AVG
FROM (
SELECT COUNT as order_count
FROM orders
GROUP BY customer_id
) as order_counts;
```
This example calculates the average number of orders per customer.
1. Subquery in the WHERE section: In this case, the subquery is used to set the conditions for returning the data.
```
SELECT customer_id, last_name
FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_date > ’2021-01-01’);
```
This example selects all the customers who have made an order after January 1, 2021.
1. Subquery in the JOIN section: The subquery is used to define the table to join.
```
SELECT c.customer_id, c.last_name, o.order_count
FROM customer c
JOIN (
SELECT customer_id, COUNT as order_count
FROM orders
GROUP BY customer_id
) o ON c.customer_id = o.customer_id;
```
This example lists each customer along with their total number of orders.
Remember that subqueries can be slower than joins or other methods of retrieving data, so it’s best to use them when necessary or when the performance impact is negligible.