Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎

T07 Sets

Set operations and 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 in relational algebra, in what is based SQL, are union, interesection, difference, cartesian product and division. In Oracle engines, we have got some like UNION, INTERSECT and MINUS.

In MySQL only UNION operator is available, although the operativity of the others can be simulated with other operators seen before.

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 nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and creditos=6;




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

select nombre from profesores where categoria='ASO6'UNIONselect nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and creditos=6;

nombre
RAFAEL ROMERO
EVA GOMEZ



The same query using UNION ALL

select nombre from profesores where categoria='ASO6'
UNION ALL
select nombrefrom profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and creditos=6;
nombre
RAFAEL ROMERO
EVA GOMEZ
EVA GOMEZ
RAFAEL ROMERO

Intersection

Although it does not exists this operator in MySQL, it can be done combining queries already known.

DNI of the teachers that teach and prepare.

IMPARTE[dni](COORDINADORES[dni])

select dni from imparteINTERSECTselect dni from prepara;

Actually, it can be expressed as:

select distinct i.dnifrom imparte i, prepara ppwhere pp.dni=i.dni;

dni
21111222
21333444

It is important to use the distinct modifier to implement the intersection: the set operations do not return duplicates (union all is an extension performed to resolve certain type of queries that do need duplicates)

Difference

The MINUS operator is not implemented in MySQL neither but it is easily replaceable by expressions as NOT IN. In any case, the final result will be compound only of those rows that appear in the result of the first select and not in the result of the second select.

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 MySQL's):

select dni, nombre from profesores where categoria='TEU'
MINUS
select dni, p.nombre from profesores p, imparte i, asignaturaswhere p.dni=i.dni and asignatura=codigo and creditos=6;

It can be resolved as:

select dni,nombre from profesores where categoria='TEU' and dni NOT IN  (select p.dni from profesores p, imparte i, asignaturas  where p.dni=i.dni and asignatura=codigo and creditos=6);

dni nombre
21222333 MANUEL PALOMAR


Obviously, we can simplify the query:
select dni,nombre from profesores where categoria='TEU'
and dni NOT IN
(select dni from imparte i, asignaturas  where asignatura=codigo and creditos=6);


Cartesian product

In SQL, as said before, the cartesian product will be done performing a select "without where". 

select dni, codigo from profesores, asignaturas

dni codigo
21111222 DGBD
21222333 DGBD
21333444 DGBD
21111222 FBD
21222333 FBD
21333444 FBD
21111222 FP
21222333 FP
21333444 FP
21111222 HI
21222333 HI
21333444 HI
21111222 PC
21222333 PC
21333444 PC

However, the cartesian product, as any other type of query, can filter the rows if we want it.


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;

Natural join

This operator is implemented by MySQL and Oracle Database but it is not sure that other DBMS have it. Same as in Relational Algebra, this operator assumes that there are common columns in two different tables (at first, with the same name) and automates the join based on equal values for these common columns. With another words: 

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

select p.dni, p.nombre, p.categoria, p.ingreso, i.asignatura
from profesores p, imparte i

where p.dni=i.dni
;

gets the same result as 

select * from profesores NATURAL JOIN imparte;


In  both cases the output is shown next. Note that only the "dni" column is shown (specifically the one in TEACHER table, the table on the left of the operator).

dni nombre categoria ingreso asignatura
21111222 EVA GOMEZ TEU 1993-10-01 DGBD
21111222 EVA GOMEZ TEU 1993-10-01 FBD
21333444 RAFAEL ROMERO ASO6 1992-06-16 PC

Its expression in Relational Algebra will be: PROFESORES ∞ IMPARTE Despite its easy use, we must be care with this operator, because if there is not common columns it will return the cartesian product. Another possible problem is that there are more columns with the same name that we do not want to join. 

Additional material

Although it is an operator that behaves in a different way, EXISTS could be included into the category of SQL set operators. Besides, it is the base to a classical simulation fo relational algebra's DIVISION. Both of them are explained in a complementary lesson.
Subpáginas (1): Sets 2