Sets

Set operations and MariaDB/MySQL

A set operator combines the results of two SELECT sentences in a single result. Depending on the type of the operation, these sentences must fulfill a set of requirements regarding the result they return. The set operators defined for relational algebra, the foundation upon which SQL is built, are:

union
interesection
difference
cartesian product
natural join

Not all features will always be implemented. However, in current versions of MariaDB/MySQL, we can find them.

UNION
INTERSECT
EXCEPT
CROSS JOIN
NATURAL JOIN

Cartesian product

In SQL, the cartesian product of two tables is obtained if no condition is specified in the WHERE clause . In fact, we propose thinking of any query as an ordered sequence of operations, of which FROM is the first, and whose result is the cartesian product of all the specified tables. The operators in relational algebra would be the cartesian product, selection, and projection.

SELECT nombre, codigo FROM PROFESORES, IMPARTE WHERE PROFESORES.dni = IMPARTE.dni

PROFESORES x IMPARTE -- from

where PROFESORES.dni = IMPARTE.dni -- where

[nombre, codigo] -- select

All possible combinations of teacher ID and subject code

In relational algebra, this would be solved with

PROFESORES x ASIGNATURA [dni, codigo]

Or with

PROFESORES[dni] x (ASIGNATURA[codigo])

In SQL

select dni, codigo from profesores, asignaturas

However, the cartesian product, like any other type of query, can filter the resulting rows to suit our needs.

Dni of the teachers that teach 2 or more subjects

select distinct i1.dni

from imparte i1, imparte i2

where i1.dni = i2.dni

and i1.asignatura != i2.asignatura;

From the cartesian product of a table with itself, we only want the rows in which the teacher "at the left" is the same as the teacher "at the right"; besides, if the subjects are different, the conclusion is that this teacher fulfill the condition.

Although for the example here there is a more friendly way to solve this query, like the one shown next. Anyway, the cartesian product is an option that can be used when we consider it

select dni from imparte group by dni having count(*) >= 2;

The SQL standard defines CROSS JOIN as the specific operator for the cartesian product. However, depending on the database engine, its implementation differs, making it more or less efficient.

In small databases, that performance difference is irrelevant, and in any case, in MariaDB/MySQL they are equivalent:

select * from profesores CROSS JOIN asignaturas;

select * from profesores JOIN asignaturas;

select * from profesores, asignaturas;

Intersection

The INTERSECT operator retrieves common rows from the results of two queries. It might be confused with a simple JOIN, and indeed many results can be obtained with both operators, but JOIN combines columns from multiple tables and, if there are multiple matches, duplicates the rows from the left table, while INTERSECT compares entire rows and returns only one copy of the matches. INTERSECT ALL is used when we don't want strictly relational behavior, when we don't want to remove duplicates if they exist.

ID number of the teachers who teach and prepare: IMPARTE[dni]∩(COORDINADORES[dni])

What should be resolved as

select dni from imparte

INTERSECT

select dni from prepara

Note that it is necessary for the queries to retrieve compatible tables, with the same number of columns and comparable data types in each column position.

Subject Coordinators:

select * from coordinadores

Subject supervisors:

select * from supervisores

Subject coordinators who are also supervisors:

select * from coordinadores

INTERSECT

select * from supervisores

/* SQL Error (1222): The used SELECT statements have a different number of columns */

select dni,nombre from coordinadores

INTERSECT

select * from supervisores

Union

When using the UNION operator between two SELECT sentences, the final result will be compound of all those rows that appear in the result of at least one of the SELECT sentences. The UNION operator eliminates duplicated rows in the final result. The UNION ALL operator performs the same as the UNION, but it does no eliminate duplicates in the final result.

Imagine that we want to know the name of the teachers that are ASO6 or teach a subject with 6 credits. We will see the result of each query separately.

1) Name of the teachers whose category is ASO6.

select nombre

from profesores

where categoria='ASO6';

2) Name of the teachers that teach subjects with 6 credits.

select nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas on asignatura=codigo

where creditos=6

Name of the teachers that are ASO6 or teach a subject with 6 credits.

select nombre from profesores where categoria='ASO6'

UNION

select nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas on asignatura=codigo

where creditos=6

The same query using UNION ALL

select nombre from profesores where categoria='ASO6'

UNION ALL

select nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas on asignatura=codigo

where creditos=6

Difference

The EXCEPT operator retrieves rows that appear in the result of the first SELECT statement but not in the result of the second. Like UNION, it is a set operator and does not return duplicate rows.

DNI and name of the teachers that are TEU and do not teach subjects with 6 credits.

Relational Algebra solution:

PROFESORES donde categoría='TEU' [dni,nombre]

(PROFESORES x IMPARTE x ASIGNATURA

donde (PROFESORES.dni = IMPARTE.dni y codigo=asignatura y créditos=6)

[PROFESORES.dni,nombre])

SQL solution (but not MariaDB/MySQL's):

select dni, nombre from profesores where categoria='TEU'

EXCEPT

select dni, nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas on asignatura=codigo

where creditos=6

Remember that we are dealing with a set operation; we must ensure compatibility between each subquery, they must return the same number of columns, and be comparable in each position:

select x1,y1,z1...

EXCEPT

select x2,y2,z2

EXCEPT is not the same as NOT IN

Although many queries have alternative expressions, the use of EXCEPT and NOT IN is not the same. EXCEPT is the difference between sets of rows, while NOT IN is actually a concatenation of logical expressions connected by OR: x NOT IN (a, b, c) == ( x != a OR x != b OR x != c)

Yes, the result will be the same for these two examples:

select dni, nombre from profesores

where categoria='TEU'

EXCEPT

select p.dni, p.nombre

from profesores p

join imparte i on p.dni=i.dni

join asignaturas a on a.codigo=i.asignatura

and creditos=6

select dni,nombre from profesores

where categoria='TEU'

and dni NOT IN (

select p.dni

from profesores p

join imparte i on p.dni=i.dni

join asignaturas a on a.codigo=i.asignatura

and creditos=6)

But that's because in the second query we're working with a single column that doesn't allow nulls (dni). The IN operator follows ternary logic ( true , false , unknown ) which, in the case of null values, leads to non-intuitive results:

d NOT IN (a, b, null) == ( d !=a OR d !=b OR d != NULL ) == false OR false OR unknown == unknown

Applied to SQL queries, this means not returning any rows, which might seem counterintuitive. However, `{d} EXCEPT {a, b, null}` does return `d` as an element that is in the first set but not in the second.

IN / NOT IN is safe to use when:

We work with lists of manually defined constant values (e.g., WHERE province IN ('03', '28', '46')).
The subquery returns a single column and there is absolute certainty that there are no NULL values in the data of that column.

Natural join

This operator is implemented in MariaDB/MySQL and Oracle Database, but it's not certain that other DBMSs also support it. Similar to relational algebra, this operator assumes that there are common columns in two different tables (ideally, tables with the same name) and automates the join based on the equality of values in these common columns.

Get all the data from teachers that teach any subject and the code of those subjects.

select distinct p.dni, p.nombre, p.categoria, p.ingreso, i.asignatura

from profesores p join imparte i on p.dni=i.dni

The same resulta if obtained by

select * from profesores NATURAL JOIN imparte

En ambos casos la salida es la que se muestra a continuación. Nótese que solo se muestra una columna de "dni" (concretamente la de PROFESORES, la tabla a la izquierda del operador).

In both cases, the output is shown below. Note that only one "dni" column is displayed (specifically the one for PROFESORES, the table to the left of the operator).

Its expression in Relational Algebra will be:

PROFESORES ⨝ IMPARTE

Although in algebra the operator does not return duplicate rows, in SQL —unlike UNION, INTERSECTION or EXCEPT—, NATURAL JOIN does not filter out those possible duplicates.

Despite the potential ease of use of this operator, caution is advised; if no common columns exist, a cartesian product will be returned. Another source of unexpected results is having more columns than desired with identical names.

EXISTS

Although it behaves differently from the previous operators, EXISTS has some relation to SQL set operators. Furthermore, it forms the basis of one of the classic simulations of the DIVISION operator in relational algebra.

EXISTS is a single-argument operator whose result is a truth value:.

[NOT] EXISTS (select order)

The EXISTS operator tells us whether a subquery returns any rows or not. It returns true if there is at least one tuple in the derived relation, and false if the derived relation has no rows. Therefore, unlike IN, EXISTS is not concerned with nulls, only with the presence of rows, regardless of the data they contain.

Do we have teachers?

select exists (select 1 from profesores) respuesta

Since we don't need anything from the teachers table except to know if there are rows or not, "select 1" is sufficient. But don't confuse it with the "1" in the result table; that's a truth value: 1 is true, 0 is false.

Is the PROFESORES table empty?

select not exists (select 1 from profesores) respuesta

It is more common to compose a condition that allows us to filter rows.

All the data for the subjects taught by a professor

select * from asignaturas a

where exists (select 1 from imparte i where i.asignatura=a.codigo)

We use what's known as a correlated subquery: i.asignatura=a.codigo links both tables. It's essentially like "find the subject code in the PROFESORES table". This will be done for each subject row.

Note that a join is not performed ; values are only searched for until they are found, which can be done on an index if one exists.

We can also check the complete opposite:

All data for subjects that are not taught by any teacher

select * from asignaturas a

where not exists (select 1 from imparte i where i.asignatura=a.codigo)

In the EJEMPLO database schema we have a PREPARA table with the following structure

CREATE TABLE prepara (

dni VARCHAR(10),

asignatura CHAR(5),

PRIMARY KEY (dni, asignatura),

FOREIGN KEY (asignatura) REFERENCES asignaturas (codigo),

FOREIGN KEY (dni) REFERENCES profesores (dni)

);

This table represents professors who have been assigned to prepare the content, materials, and assessments for certain subjects. It is a many-to-many relationship between PROFESORES and ASIGNATURAS.

Names of the teachers who prepare a subject:

select nombre from profesores p

where exists

(select 1 from prepara pp where p.dni=pp.dni)

We could read this query as something like "give me the names of the teachers for whom there is at least one row in PREPARA".

Names of the teachers who do not prepare students for all subjects (there is at least one subject they do not prepare for)

select nombre from profesores p

where exists -- existe al menos una asignatura

(select codigo from asignaturas a

where not exists -- que el profesor no prepara

(select 1 from prepara pp

where pp.asignatura=a.codigo and p.dni=pp.dni)

)

Here, it's necessary to access all the subjects (the SUBJECTS table) to check if there are any subjects for which a certain professor is not listed in the PREPARA table. Reading the query literally: "names of professors for whom there is a subject they don't prepare for." The "trick" is to link the three tables in the last subquery.

We can also emulate the division operator from relational algebra.

Names of the teachers who prepare all the subjects

select nombre from profesores p -- profesores tales

where not exists -- que no existe asignatura

(select 1 from asignaturas a

where not exists -- que no prepare él

(select 1 from prepara pp

where pp.asignatura=a.codigo and p.dni=pp.dni)

)

Let's say it's something like, "Give me the names of the professors who teach every subject ." Dealing with double negatives is definitely tricky, but not impossible.

Alternatives

Sometimes, the desired result allows for other types of solutions.

Names of the teachers who prepare all the subjects.

select nombre from profesores p join prepara pp on p.dni = pp.dni

group by p.dni, nombre

having count(*) = (select count(*) from asignaturas);

Or with certain set operators:

select dni,nombre from coordinadores

INTERSECT

select * from supervisores

select dni,nombre from coordinadores c where exists (select 1 from supervisores s where s.dni=c.dni)

select dni,nombre from coordinadores

EXCEPT

select * from supervisores

select dni,nombre from coordinadores c where NOT EXISTS (select 1 from supervisores s where s.dni=c.dni)

The consensus is that EXISTS is more efficient for the database engine—once it finds the first row, it stops searching. However, for significant performance differences to occur, we need to talk about really large tables, and queries written this way can be more difficult to read.

Page updated

Google Sites

Report abuse