More queries

Ranges

Expressions such as 10 <= x <= 100 can be built using the range operator BETWEEN. The syntax of the subexpression of the where clause is:

expression [NOT] BETWEEN expression AND expression

Credits and description of the subjects whose number of credits is between 5 and 8.

select creditos, descripcion

from asignaturas

where creditos between 5 and 8

Equivalent to

select creditos, descripcion

from asignaturas

where creditos >= 5 and creditos <= 8

The opposite condition, that the compared value is not within the range, requires the NOT modifier.

select creditos, descripcion

from asignaturas

where creditos NOT between 5 and 8

Substrings

It is possible to ask for substrings within character columns. For doing so, the LIKE (or MATCHES) can be used, with the following syntax:

expression [NOT] LIKE 'string'

The character string admits the % and _ (underlined):

- % indicates a string of any length ('Ali%' = 'Alicante', 'Aligerar', 'Ali', ...)
- _ any character ('Ali_' = 'Alic', 'Alig', 'Ali', ...)
- combined ('_l%' = 'Alicante', 'Albacete', 'llamada',...)

Teachers that respond to the name 'RAFA'.

select * from profesores where nombre like 'RAFA%'

Code of the 'Database' subjects

select codigo from asignaturas

where descripcion like '%BASES DE DATOS%'

Code of the subjects, with a two characters code

select codigo from asignaturas where codigo like '__'

Description of the subjects whose last word contains 'INFORMATIC' and an extra character

select descripcion from asignaturas

where descripcion like '%INFORMATIC_'

Don't use LIKE when you mean "equality":

select codigo from asignaturas where codigo like 'FP'

It is true that MariaDB will not "complain" and will execute accordingly, but leave LIKE for comparisons with substrings and use the simple equal (=) when looking for exact match:

select codigo from asignaturas where codigo = 'FP'

If you are looking for several different substrings that cannot be combined into a single expression, the correct approach is to propose two LIKE comparisons:

select descripcion from asignaturas

where descripcion like '%INFORMATIC_'

OR descripcion like '%MULTI%'

The opposite condition, which does not conform to the specified format string, requires the use of the NOT modifier:

select descripcion from asignaturas

where descripcion NOT like '%INFORMATIC_'

Lists

Using the IN operator is possible to look for a certain value in a list built using constants.

expression [NOT] IN (ValueList)

Description of the subjects FBD and DGBD.

select descripcion

from asignaturas

where codigo in ('FBD', 'DGBD')

Name of the teachers that do not teach HI, FBD or DGBD.

select nombre

from profesores p, imparte i

where p.dni = i.dni

and asignatura not in ('HI', 'FBD', 'DGBD')

Note that MANUEL PALOMAR, who is not teaching any of the subjects objective in the search, is neither appearing in the result-table because his id is not in the IMPARTE table.

Actually, it is just a more compact - and convenient - way of describing logical conditions. The above queries are equivalent to, respectively:

select descripcion from asignaturas

where codigo = 'FBD' OR codigo = 'DGBD';

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

and NOT(asignatura='HI' OR asignatura='FBD' OR asignatura='DGBD');

In this last query, we could also have transformed the negation as:

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

and (asignatura != 'HI' AND asignatura != 'FBD' AND asignatura != 'DGBD')

(Let "subject" be other than 'HI', and other than 'FBD', and other than 'DGBD')

Nested queries and lists

We have seen that I can replace several OR-connected expressions with a list of constants. What if we generate that list of constants from another select query? We are getting into the slightly more complex area of nested queries or subqueries. For the moment, let's stick with one of the simplest and most intuitive situations.

expr [NOT] IN (select command)

A query like select dni from imparte will only produce a column with 0 or several rows, in our case without removing duplicates, 3 rows, as you can see in the image beside. Although we see it as a "vertical" list of values, in reality it does not have to be like that.

Suppose we pose the following query:

select * from teachers where dni='21111222' OR dni='21111222' OR dni='21333444'

It is obvious that one of the expressions is redundant, but we are trying to show the idea behind what will be developed below. As we have seen, these logical expressions can be expressed more compactly with:

select * from teachers where dni IN ('21111222', '21111222', '21333444')

SQL allows us to get that list from IN directly with a query.

select * from teachers where dni IN (select dni from imparte)

The main application of this type of command is to retrieve data from our database that may eventually change over time. In that aspect, it is a "dynamic" query, its result can be different according to the moment in which we execute it.

Obtain all the data of the teachers that teach any subject.

select * from profesores

where dni IN (select dni from imparte)

Or in other words: "data of all the teachers whose id appears in imparte table".

In this case (not always happens), it would be the same result when processing the following statement:

select distinct p.*

from profesores p, imparte i

where p.dni = i.dni

NOTE: using IN in this way does not generate duplicate rows, it achieves the same result as the distinct modifier of the second query. While IN goes through the TEACHERS table and asks one by one if it is in the IMPARTE table or not, the second query performs the Cartesian product and discards the rows where the DNI values do not match. This generates duplicate rows that are eliminated with distinct.