DML: Subqueries

name: portada
layout: true
class: portada-slide, middle, right
---
# DML: Subqueries
## Databases

.footnote[Joan Puigcerver Ibáñez ([j.puigcerveribanez@edu.gva.es](mailto:j.puigcerveribanez@edu.gva.es))]

---
layout: true
class: regular-slide
.right[.logo[![logoMislata](/itb/images/logo_mislata.png)]]

---
# Subqueries
Sometimes a uknown value is needed to make a query, but you can retrieve it using another query.
- __Example__: _Obtain the students that are older than the average age_

```
select DNI, name
from student
where age >= average_age; --> What is the avegare age of the students?
```

```
select avg(age)
from student;
```

We can combine both queries using __()__:

```
select DNI, name
from student
where age >= (
    select avg(age) --> This is a subquery
    from student
);
```

---
# Subqueries
A __subquery__ is a query that is nested inside another query. Multiple queries can be nested and they can be used in different clauses (WHERE, FROM, ...)

Usually, it's less efficient using subqueries (some of then can be solved using only joins), but sometimes subqueries are the only solution.

.center[[MariaDB Subqueries](https://mariadb.com/kb/en/subqueries/)]

---
# Subqueries: WHERE clause
The most common place to use a subquery is the WHERE clause.

```
-- Obtain the students that are older than the average age
select DNI, name
from student
where age >= (
    select avg(age)
    from student
);
```

Subqueries can be used in combination with the same set of comparison operators that are used in single queries, but some else aswell.

---
# Subqueries: single value operators
These operators can be used when the subquery returns a single value.
```
=, !=, <>, <, <=, >, >=
BETWEEN, NOT BETWEEN, LIKE
```

```
-- Obtain the students that are older than the average age
select DNI, name
from student
where age >= (
    select avg(age)
    from student
);
```

---
# Subqueries: [NOT] IN
The __IN__ and __NOT IN__ operators can be used if the subquery returns mutiple values, but only a single column.

```
-- Obtain the teachers that are not tutors of any group
select DNI, name
from teacher
where DNI not in (
    select distinct DNI_tutor
    from `group`
);
```

---
# Subqueries: [NOT] EXISTS
The __EXISTS__ and __NOT EXISTS__ operators can be used to check if the subquery returns any row.

.center[[MariaDB EXISTS](https://mariadb.com/kb/en/subqueries-and-exists/)]

- The `EXISTS` keyword will return `true` if the subquery returns any rows.

```
-- Obtain the students that are enrolled in the subject "DBA"
select s.DNI, s.name
from student s
where exists (
    select e.subject
    from enrolled e
    where e.studentDNI = s.DNI and e.subject = "DBA"
);
```

---
# Subqueries: ANY
The __ANY__ operator can be used to check if a condition is met in any row returned by a subquery.

ANY returns:
- `TRUE` if the comparison operator returns `TRUE` for at least one row returned by the subquery.
- `NULL` if the comparison operator returns `NULL` for at least one row returned by the subquery and doesn't returns TRUE for any of them.
- `FALSE` if the comparison operator returns `FALSE` for all rows returned by the subquery, or subquery has zero rows.

```
-- Obtain the students that have passed at least a subject
select s.DNI, s.name
from student s
where 5 <= ANY (
    select e.grade
    from enrolled e
    where e.studentDNI = s.DNI
);
```

---
# Subqueries: ALL
The __ALL__ operator can be used to check if a condition is met in all the rows returned by a subquery.

ALL returns:
- `NULL` if the comparison operator returns `NULL` for at least one row returned by the subquery.
- `FALSE` if the comparison operator returns `FALSE` for at least one row returned by the subquery.
- `TRUE` if the comparison operator returns `TRUE` for all rows returned by the subquery, or if subquery returns no rows.

```
-- Obtain the students that have passed all the subjects that is enrolled in
select s.DNI, s.name
from student s
where 5 <= ALL (
    select e.grade
    from enrolled e
    where e.studentDNI = s.DNI
);
```

---
# Subqueries: ALL
The `ALL` operator isn't defined in all SQL languages, but a mathematical expression can be used to
rewrite it and get the exact same result, using __NOT EXISTS__.

$$ \forall x | f(x)  \equiv \nexists x | \neg f(x) $$

The following queries are __equivalent__.
.columns[
.column[
_Obtain the students that have passed all the subjects that is enrolled in_
```
select s.DNI, s.name
from student s
where 5 <= ALL (
    select e.grade
    from enrolled e
    where e.studentDNI = s.DNI
);
```
]
.column[
_Obtain the students that don't have failed any subject that is enrolled in_
```
select s.DNI, s.name
from student s
where NOT EXISTS (
    select e.grade
    from enrolled e
    where e.studentDNI = s.DNI 
        and e.grade < 5
);
```
]
]