Inicio‎ > ‎SQL‎ > ‎SQL (english)‎ > ‎T07 Sets‎ > ‎

Sets 2

The EXISTS operator

Exists is an operator with a single argument and its result is a value of true, informing the presence or not of tuples in a table. 

     [NOT] EXISTS (orden select) 

The exists operator informs us if a subquery has returned any result, or what is the same, if the result table has any rows.

Exists returns true if there is at least one tuple in the derived relation and false if the derived relation is void. 

¿Do we have teachers?

select exists (select 1 from profesores) answer
answer
1

Because we do not need nothing from the table PROFESORES but to know whether if there are tuples or not, "select 1" is enough: so many rows with a "1" as rows being in table profesores.

More often, exists will be part of a where condition componer una condición que nos permita filtrar filas.

All data from subjects when in imparte

select * from asignaturas a where exists (select 1 from imparte i where i.asignatura=a.codigo)
codigodescripcioncreditoscreditosp
DGBDDISEÑO Y GESTION DE BASES DE DATOS6.03.0
FBDFUNDAMENTOS DE LAS BASES DE DATOS6.01.5
PCPROGRAMACION CONCURRENTE6.01.5

Obviously, the same result will be obtained if

select * from asignaturas where codigo IN (select asignatura from imparte)

Efficiency

Although for the amount of rows managed in BD Ejemplo and in Proveedores the difference is not relevant, the use of exists can be appropriate to improve the performance of certain queries. For example:

select distinct nombre from profesores p, imparte i where p.dni = i.dni;

Actually, we are only interested in known the name of the teachers that teach any subject, without paying attention in what are these subjects. Due to this, it is better to use:

select nombre from profesores p where exists (select * from imparte where dni = p.dni);
nombre
EVA GOMEZ
RAFAEL ROMERO



The advantage of this second query is that the subselect only need to return one row for each teacher, whereas the first one firstly obtain the linking of all the subject's codes with their corresponding teacher and then projects over the nombre column.

We can suppose a state of the database with 1000 subjects, and that the 3 teachers teach all the subjects: the first query will work with 3000 rows, whereas the second will need no more than 3.

However, this will be more noticeable in environments with more efficiency needs or with a huge amount of data, and therefore, "helping" the query optimizer.  

Specific application

One of the possible uses, a bit confusing at first though, is returning queries like "x does something with all the y of our database".

Imagine an extra table in our BD Ejemplo, PREPARA, which is a M:M relationship between subjects and teachers, and it could be representing which teachers are involved in the preparation of each subject. Its content is shown next:
 

Teachers that prepare subjects.

select nombre from profesores p where not exists
(select codigo from asignaturas a where not exists
(select asignatura from prepara pp where pp.asignatura=a.codigo and p.dni=pp.dni)
)

nombre
RAFAEL ROMERO



We are going to reformulate the query following the appearances of the exists operator withing the next select statements:

"Name of the teachers that do not exists any subject that he/she does not prepare".

Imagine that the way the DBMS is returning this sentence is :
  1. In the first select we take over the profesores table.
  2. For each previous teacher, the second select takes over the table asignaturas.
  3. For each previous teacher and each previous subject, it checks that the first one prepare the second one.


Note that

select nombre from profesores p where exists
(select codigo from asignaturas a where exists
(select asignatura from prepara pp where pp.asignatura=a.codigo and p.dni=pp.dni)
)

will be equivalent to "name of the teachers that prepare some subject":

select nombre from profesores p, prepara pp where p.dni=pp.dni

nombre
EVA GOMEZ
RAFAEL ROMERO



Name of the teachers that do no prepare all the subjects (it exists at least one subject that it is not prepare it by him/her)

select nombre from profesores p where exists (select codigo from asignaturas a where not exists
(select asignatura from prepara pp where pp.asignatura=a.codigo and p.dni=pp.dni))
)

nombre
EVA GOMEZ
MANUEL PALOMAR



Alternatives

Sometimes, we are looking for a result that requires other types of solutions.

Name of the teachers that prepare all the subjects.

select nombre from profesores p, prepara ppwhere p.dni = pp.dni
group by p.dni, nombre
having count(*) = (select count(*) from asignaturas)


Name of the teachers that do not prepare any subject.

select nombre from profesores pwhere not exists
(select codigo from asignaturas a where exists (select asignatura from prepara pp where pp.asignatura=a.codigo and p.dni=pp.dni)
          );
 
select nombre from profesores pwhere not exists
(select asignatura from prepara pp where p.dni=pp.dni);
 
select nombre from profesoreswhere dni NOT IN (select dni from prepara); 
nombre
MANUEL PALOMAR

Division

NOTE: the division operator is complementary only, it won't be used for any exam.

The division as operator is defined in relational algebra as an operation that returns "something that is related with all the ones that fulfill a condition. For example, 

IMPARTE[asignatura, dni] ÷ (PROFESORES[dni])

This expression will return as a result the codes of the subjects that are being taught by all the teachers of my database.

There is no such operator in SQL, and therefore we must to simulate it with exists.

Relational algebra's division with EXISTS

One of the possible uses, although a bit confusing at first, is resolving queries like "x do something with all the y of our database".

Suppose an addditional table in our BD Ejemplo, PREPARA, that it is a many to many relationship between subjects and teachers, and that represents what teachers prepare each subject. Its content is the one shown next:

Teachers that prepare all the subjects.

select nombre from profesores p where not exists
(select codigo from asignaturas a where not exists
(select asignatura from prepara pp where pp.asignatura=a.codigo and p.dni=pp.dni)
)

nombre
RAFAEL ROMERO

If we rewrite the query following the presence of the exists operator in the selects:

"Name of the teachers that there are no subject that they do not prepare".

Suppose that the way that the DBMS use to resolve this query is :
  1. In the first select we go through the teachers table.
  2. For each teacher, the second select goes through the subjects table.
  3. For each teacher and subject, it will check that the first prepares the second.
Comments