SQL Basics: Data Querying

This chapter presents the basics of SQL language: data querying. A database managing marathons illustrates this chapter and the following ones.

3.1 Selection queries: SELECT

Selection is the most common operation in SQL. It involves querying the database to extract one or more fields, specifying which table they are found in.

Here is a selection query showing the order of SQL commands.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
WHERE <conditions>
GROUP BY <field1>, <field2>, …
HAVING <conditions>
ORDER BY <field1>, <field2>, …
LIMIT ;

Projection consists of selecting only certain columns for display

To know the names, first names and birth dates of runners, the selection query is written as follows:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR;

To display an entire table, the wildcard character "*" is used.

Thus the following query displays the entire COUREUR table without projection:

SELECT *
FROM COUREUR;

3.2 Avoiding duplicates: DISTINCT

Using the DISTINCT keyword avoids redundant rows in the result of a query. It is placed just after the SELECT command.

SQL Syntax

SELECT DISTINCT <field1>, <field2>, …
FROM <table> ;

To display city names while avoiding duplicates, the SQL query is written:

SELECT DISTINCT CL_VILLE
FROM CLUB;

3.3 Renaming a column: AS

For better readability of query results, it is possible to temporarily rename a column using the AS command. This command allows you to create an alias.

SQL Syntax

SELECT <field1> AS <alias1>, <field2>, …
FROM <table> ;

To rename the CL_VILLE column of the CLUB table to VILLE, the SQL query is presented as:

SELECT DISTINCT CL_VILLE AS VILLE
FROM CLUB

3.4 Sorting results: ORDER BY

The ORDER BY clause allows sorting of query results in ascending (ASC) or descending (DESC) order on one or more columns. Note that if the sort type is not specified, it will be ascending (ASC) by default.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
ORDER BY <field1> ASC/DESC, <field2> ASC/DESC … ;

To sort the results of example 3, the SQL query becomes:

SELECT DISTINCT CL_VILLE AS VILLE
FROM CLUB
ORDER BY CL_VILLE;

3.5 Limiting the number of result rows: TOP / LIMIT

The TOP or LIMIT clauses can be used to limit the number of rows to display in the result of a query. The TOP clause is compatible with MS SQL Server and MS Access, while LIMIT works with MySQL and ORACLE®.

In practice, these clauses allow producing a subset of the selection query result.

SQL Syntax

SELECT TOP <number> <field1>, <field2>, …
FROM <table> ;

or

SELECT <field1>, <field2>, …
FROM <table>
LIMIT <number> OFFSET <number> ;

Based on example 1, the query to execute to display the first 5 results sorted by runner names is written:

SELECT TOP 5 CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
ORDER BY CO_NOM;

or

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
ORDER BY CO_NOM
LIMIT 5;

3.6 Restricting results: WHERE

The WHERE clause allows restricting the results of a SELECT according to one or more conditions. Selection consists of restricting the records in the result according to a set of conditions called restriction.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
WHERE <conditions> ;

To display only women from the COUREUR table, simply execute the query:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_SEXE = 2;

While the above example shows how to make a selection with a simple "=" operator, there are of course more complex operators.

3.6.1 The LIKE operator: string comparison

The LIKE operator allows searching for all records having a field containing the value specified in the condition.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
WHERE <field> LIKE <condition> ;

To display runners whose name begins with the letter K, the query appears as follows:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_NOM LIKE 'K%';

This example illustrates the use of a so-called "wildcard" character: the %. This allows searching for all records whose name begins with K regardless of the characters that follow. This is therefore a PATTERN search. With MySQL, % replaces multiple characters and _ replaces a single one.

Here are other examples of possible patterns with the LIKE operator:

• K%: returns all records starting with "K" regardless of the characters that follow.

• %K%: returns all records containing "K" regardless of the preceding and following characters.

• Kl_in: returns all five-character records beginning with "Kl" and ending with "in".

• K[L-Q]%: returns all records beginning with the letter "K" followed by a letter between "L" and "Q", followed by at least one character.

• K[^l]: returns all records beginning with "K" and whose next character is not an "l".

As you can understand, the combinations are multiple, very powerful and allow exploiting expressions. Wildcard characters are not standardized and vary according to Database Management Systems.

3.6.2 The IN operator: comparison to a list

The IN operator allows searching for all records whose field has a value belonging to a given list.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
WHERE <field> [NOT]IN (<value1>, <value2> …) ;

To display runners whose first name is Cédric, Etienne or Richard, the query is written:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_PRENOM IN ('Cédric', 'Etienne', 'Richard');

This operator simplifies the use of the OR condition because it allows placing a condition on a list of values. The syntax below clearly shows this heaviness:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_PRENOM = 'Cédric'
OR CO_PRENOM = 'Etienne'
OR CO_PRENOM = 'Richard';

3.6.3 The BETWEEN operator: interval

The BETWEEN operator allows searching for all records whose condition field is in a defined interval.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <table>
WHERE <field> BETWEEN <value1> AND <value2> ;

The query to execute to display runners whose birth date is between 01/03/1940 and 31/12/1960 is presented as:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_NAISSANCE BETWEEN '1940-03-01' AND '1960-12-31';

3.6.4 Using AND and OR

As mentioned above, it is also possible to combine conditions using logical operators AND or OR as well as parentheses.

The execution of the following query:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_NOM LIKE '%a%' AND YEAR(CO_NAISSANCE) < 1950;

Whereas for the query:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_NOM LIKE '%a%' OR YEAR(CO_NAISSANCE) < 1950;

The result of the first query corresponds to people whose name contains an "a" and who were born before 1950, while the result of the second includes all those who have an "a" in their name or who were born before 1950.

In an SQL query, AND takes precedence over OR. Thus the following two restrictions are equivalent:

• WHERE A OR B AND C.

• WHERE (B AND C) OR A.

Indeed, since AND has priority, the query never processes "A OR B" since it breaks down the query into two parts: "B AND C" then result of "B AND C" OR A.

To change these priorities, you must use parentheses to prioritize certain conditions.

To display runners named either Buzz Aldrin, or born in January, the SQL query is:

SELECT CO_NOM, CO_PRENOM, CO_NAISSANCE
FROM COUREUR
WHERE CO_NOM = 'ALDRIN'
AND CO_PRENOM = 'BUZZ'
OR MONTH(CO_NAISSANCE) = 1;

10.1 Introduction to joins

10.1.2 The JOIN clause

Queries on multiple tables contain what are called joins, and rely on relationships between tables, materialized by primary and foreign keys.

Although it is possible to make a join in a WHERE clause, this should be avoided because, to improve the readability of queries, and for performance reasons of their execution, it is preferable to use another syntax introduced with SQL92: the JOIN clause. This new syntax allows clearly separating the restrictions present in the WHERE and the joins.

Here is the complete syntax of a SELECT with the appearance of join definitions in the FROM and the criteria in the ON clause.

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA>[INNER][LEFT/RIGHT/FULL][OUTER] JOIN <tableB>
ON <tableA>.<key> = <tableB>.<key>
WHERE <conditions>
GROUP BY <field1>, <field2>, …
HAVING <conditions>
ORDER BY <field1>, <field2>, …
LIMIT <number> ;

Based on the previous example, with the addition of the JOIN clause to define the join criteria, the SQL query becomes:

SELECT CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR,
AD_LICENCE, AD_ANNEE
FROM COUREUR JOIN ADHESION
ON COUREUR.CO_ID = ADHESION.AD_COUREUR_FK
LIMIT 5;

Thus by specifying the join criteria between the two tables, the results obtained are correct.

There are several types of joins depending on the needs. The most commonly used joins are internal and external joins. But there are also less common ones such as natural or cross joins.

10.2 Common Joins

10.2.1 Inner join

An inner join returns records common to both tables, according to the criteria set. This is the default join seen in the previous paragraph. The clause to use is JOIN or INNER JOIN. The INNER keyword being implicit, it is optional.

Figure 10-1: Graphical representation of the INNER JOIN command

10.2.2 Outer join

An outer join returns a maximum of records, even if they do not meet the criteria in one of the two tables, or even in both.

The left join: LEFT [OUTER] JOIN

The left join allows returning records from the first table and also those that are common to both tables, according to the criteria set. The command to use is LEFT JOIN or LEFT OUTER JOIN. Just as in the inner join, the OUTER keyword being implicit, it is optional.

Figure 10-2: Graphical representation of the LEFT OUTER JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA> LEFT [OUTER] JOIN <tableB>
ON <tableA>.<key> = <tableB>.<key> ;

If we transform the join from the previous example with the use of a LEFT JOIN, this amounts to listing all runners, whether they have joined a club or not. The SQL query becomes:

SELECT CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR,
AD_LICENCE, AD_ANNEE
FROM COUREUR LEFT JOIN ADHESION
ON COUREUR.CO_ID = ADHESION.AD_COUREUR_FK
LIMIT 5;

The result becomes:

In this example, Nicole El Karoui appears with a license number and a year as NULL, which means she has never joined a club. A variant of LEFT JOIN consists of completely excluding table B from the join as in the diagram below:

Figure 10-3: Graphical representation of the LEFT JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA> LEFT [OUTER] JOIN <tableB>
ON <tableA>.<key> = <tableB>.<key>
WHERE <tableB>.<key> IS NULL ;

Example 4

From the previous example, applying this new syntax amounts to displaying runners who have never joined a club. The SQL query is:

SELECT CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR,
AD_LICENCE, AD_ANNEE
FROM COUREUR LEFT JOIN ADHESION
ON COUREUR.CO_ID = ADHESION.AD_COUREUR_FK
WHERE AD_COUREUR_FK IS NULL;

The right join: RIGHT [OUTER] JOIN

The right join allows returning records from the second table and those that are common to both tables, according to the criteria set. The command to use is RIGHT JOIN.

Figure 10-4: Graphical representation of the RIGHT JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA> RIGHT [OUTER] JOIN <tableB>
ON <tableA>.<key> = <tableB>.<key> ;

Example 5

To display the first ten club names as well as the names of runner members or not, we need to query the CLUB, ADHESION and COUREUR tables. The SQL query is:

SELECT CL_NOM, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM CLUB RIGHT JOIN ADHESION
ON CLUB.CL_ID = ADHESION.AD_CLUB_FK
RIGHT JOIN COUREUR
ON ADHESION.AD_COUREUR_FK = COUREUR.CO_ID
LIMIT 10;

In the same way as in example 4, Nicole El Karoui and Lenore Blum appear without club names. They have therefore never joined a club. As with LEFT JOIN, there is a variant of RIGHT JOIN that consists of completely excluding table A from the join as illustrated in the diagram below:

Figure 10-5: Another graphical representation of the RIGHT JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA>
RIGHT [OUTER] JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>
WHERE <tableA>.<key> IS NULL;

Example 6

From the previous example, to display runners who have never joined a club, the SQL query is:

SELECT CL_NOM, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM CLUB
RIGHT JOIN ADHESION ON CLUB.CL_ID = ADHESION.AD_CLUB_FK
RIGHT JOIN COUREUR ON ADHESION.AD_COUREUR_FK = COUREUR.CO_ID
WHERE ADHESION.AD_COUREUR_FK IS NULL;

The result obtained becomes:

This syntax allowing performing an exclusion, the same result can be obtained with the NOT IN command.

The full join: FULL JOIN

This type of join allows retrieving records from the entirety of both tables. It is the simultaneous use of LEFT JOIN and RIGHT JOIN. The command to use is FULL JOIN.

Figure 10-6: Graphical representation of the FULL JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA>
FULL [OUTER] JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>;

Since FULL JOIN does not exist under MySQL, it is not possible to illustrate this paragraph with an example of syntax with the Marathon project. But it is quite possible to imagine one.

Example 7

Based on the previous example, to display all clubs, with or without members, as well as all runners having joined a club or not, the SQL query is:

SELECT CL_NOM, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM CLUB
FULL JOIN ADHESION ON CLUB.CL_ID = ADHESION.AD_CLUB_FK
FULL JOIN COUREUR ON ADHESION.AD_COUREUR_FK = COUREUR.CO_ID
LIMIT 10;

The above query is not executable under MySQL, but the expected result should be the following:

To obtain this same result with MySQL, the query is:

SELECT CL_NOM AS CLUB, 'NULL' AS COUREUR
FROM CLUB
UNION ALL
SELECT CL_NOM AS CLUB, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM COUREUR
LEFT JOIN ADHESION ON CO_ID = AD_COUREUR_FK
LEFT JOIN CLUB ON AD_CLUB_FK = CL_ID;

It is also possible to exclude records common to both tables, as illustrated in the diagram below:

Figure 10-7: Graphical representation of the FULL [OUTER] JOIN command

SQL Syntax

SELECT <field1>, <field2>, …
FROM <tableA>
FULL [OUTER] JOIN <tableB> ON <tableA>.<key> = <tableB>.<key>
WHERE <tableA>.<key> IS NULL OR <tableB>.<key> IS NULL;

Example 8

Continuing from the previous example, to display only clubs without members and runners who have never joined a club, the SQL query is:

SELECT CL_NOM, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM CLUB
FULL JOIN ADHESION ON CLUB.CL_ID = ADHESION.AD_CLUB_FK
FULL JOIN COUREUR ON ADHESION.AD_COUREUR_FK = COUREUR.CO_ID
WHERE CLUB.CL_ID IS NULL
   OR ADHESION.AD_CLUB_FK IS NULL AND ADHESION.AD_COUREUR_FK IS NULL
   OR COUREUR.CO_ID IS NULL;

The above query is not executable under MySQL, but the expected result should be the following:

To obtain this same result with MySQL, the query is:

SELECT CL_NOM AS CLUB, 'NULL' AS COUREUR
FROM CLUB
LEFT JOIN ADHESION ON CL_ID = AD_CLUB_FK
WHERE AD_CLUB_FK IS NULL
UNION ALL
SELECT 'NULL' AS CLUB, CONCAT(CO_PRENOM, ' ', CO_NOM) AS COUREUR
FROM COUREUR
LEFT JOIN ADHESION ON CO_ID = AD_COUREUR_FK
WHERE AD_COUREUR_FK IS NULL;

11. SQL Functions

11.1 The MIN and MAX functions

The MIN and MAX functions allow returning the minimum or maximum value of a field.

SQL Syntax

MAX/MIN(<field>)

Example 1

To search for the oldest runner present in the registrations, the SQL query is:

SELECT MAX(IN_AGE) AS MAX_AGE, CO_NOM
FROM INSCRIPTION
INNER JOIN COUREUR ON IN_COUREUR_FK = CO_ID;

The result obtained is:

Example 2

Conversely, to find the youngest runner registered in the registrations, the SQL query is:

SELECT MIN(IN_AGE) AS MIN_AGE, CO_NOM
FROM INSCRIPTION
INNER JOIN COUREUR ON IN_COUREUR_FK = CO_ID;

The result obtained is:

11.2 The Average function

The MOY or AVG function allows returning the average value of a column.

SQL Syntax

MOY/AVG(<field>)

Example 3

To know the average age of registered runners, the SQL query is:

SELECT AVG(IN_AGE) AS AGE_MOYEN
FROM INSCRIPTION;

11.3 The Sum function

The SUM function returns the sum of the values of a field.

SQL Syntax

SUM(<field>)

Example 4

To search for the number of valid certificates registered in the INSCRIPTION table of our database, the SQL query is:

SELECT SUM(IN_CERTIF) AS NB_CERTIFICAT
FROM INSCRIPTION;

The result of this query is:

11.4 The counting function

The COUNT function allows returning the number of records of a query.

SQL Syntax

SELECT COUNT(<field>) AS <alias>
FROM <table>;

Example 5

Another way to know the number of certificates received with the following query:

SELECT COUNT(IN_CERTIF) AS NB_CERTICAT
FROM INSCRIPTION
WHERE IN_CERTIF = 1;

The result obtained is identical to that of the previous example.

11.5 The variance calculation function

"In statistics and probability theory, variance is a measure used to characterize the dispersion of a sample or distribution. It indicates how the statistical series or random variable disperses around its mean or expectation." (Wikipedia)

A variance of zero indicates that all values are identical. A small variance is a sign that the values are close to each other, while a high variance is a sign that they are very disparate.

The VAR or VARIANCE function allows returning the variance of a column identified in a given set of records and deducing the amplitude of the present values compared to the average.

SQL Syntax

VAR/VARIANCE(<field>)

Example 6

To know the variance of the ages of participants registered for the different marathons, the SQL query is:

SELECT VARIANCE(IN_AGE) AS VARIANCE
FROM INSCRIPTION;

The result obtained is:

This value is the sum of the differences between the average age and the age of runners at registration.

11.7 The GROUP BY command

Aggregative functions can be applied to the entirety or to subsets of the result of a query.

Example 8

To know, for the year 2015, the number of participants grouped by city of the clubs to which they were members, the SQL query is:

SELECT COUNT(*) AS NB, CL_VILLE
FROM INSCRIPTION INNER JOIN COUREUR
ON IN_COUREUR_FK = CO_ID
INNER JOIN EPREUVE
ON (IN_EPREUVE_FK = EP_ID AND YEAR(EP_DATE) = 2015)
INNER JOIN ADHESION
ON (CO_ID = AD_COUREUR_FK AND AD_ANNEE = 2015)
INNER JOIN CLUB
ON AD_CLUB_FK = CL_ID;

The result always presents only one record per city. Moreover, the returned value is false because it does not correspond to the number of runners registered in Bordeaux clubs but to the total number of runners.

The execution of this query does not correspond to the need because the grouping criterion (the city) is not specified.

This operation will be done by using the GROUP BY clause. It allows constituting subsets of records during the execution of the query. Each attribute cited in the GROUP BY must be included in the SELECT, unless the argument used is *. The results of the FROM and WHERE clauses are grouped according to the GROUP BY criteria. In the result, each group is reduced to one occurrence and all operations that follow are applied to it.

SQL Syntax

SELECT …
FROM <table>
GROUP BY <field2>, <field3> …

Example 9

The previous example with the correct syntax.

SELECT CL_VILLE, COUNT(*) AS NB
FROM INSCRIPTION INNER JOIN COUREUR
ON IN_COUREUR_FK = CO_ID
INNER JOIN EPREUVE
ON (IN_EPREUVE_FK = EP_ID AND YEAR(EP_DATE) = 2015)
INNER JOIN ADHESION
ON (CO_ID = AD_COUREUR_FK AND AD_ANNEE = 2015)
INNER JOIN CLUB
ON AD_CLUB_FK = CL_ID
GROUP BY CL_VILLE;

When several fields are present in the GROUP BY command, the groupings being done in the order of the GROUP BY, special attention should be paid to this order according to the desired result.

Taking the previous query, but also adding a grouping on the sex of the runners, the SQL query is:

SELECT CL_VILLE, CO_SEXE, COUNT(*) AS NB
FROM INSCRIPTION INNER JOIN COUREUR
ON IN_COUREUR_FK = CO_ID
INNER JOIN EPREUVE
ON (IN_EPREUVE_FK = EP_ID AND YEAR(EP_DATE) = 2015)
INNER JOIN ADHESION
ON CO_ID = AD_COUREUR_FK
INNER JOIN CLUB
ON AD_CLUB_FK = CL_ID
GROUP BY CL_VILLE, CO_SEXE;

The result obtained is:

Example 11

The same example but simply reversing the 2 fields of GROUP BY:

SELECT CL_VILLE, CO_SEXE, COUNT(*) AS NB
FROM INSCRIPTION INNER JOIN COUREUR
ON IN_COUREUR_FK = CO_ID
INNER JOIN EPREUVE
ON (IN_EPREUVE_FK = EP_ID AND YEAR(EP_DATE) = 2015)
INNER JOIN ADHESION
ON CO_ID = AD_COUREUR_FK
INNER JOIN CLUB
ON AD_CLUB_FK = CL_ID
GROUP BY CO_SEXE, CL_VILLE;

We notice that the results are not returned in the same order. In the first case, the grouping is done on the city then the sex, while in the second case, the grouping is done first on the sex then the city. This behavior can be compensated by using the ORDER BY instruction.

11.8 The HAVING clause

The HAVING clause is linked to the GROUP BY clause and cannot be used without it. It allows specifying a restriction criterion on the results of the aggregate.

SQL Syntax

SELECT …
FROM <table>
GROUP BY <field2>, <field3> …
HAVING <field_name> [operator] [condition];

Example 12

To now search for the number of runners registered for an event in 2015 and members of a club with at least 2 participants, the SQL query is:

SELECT CL_VILLE, COUNT(*) AS NB
FROM INSCRIPTION INNER JOIN COUREUR
ON IN_COUREUR_FK = CO_ID
INNER JOIN EPREUVE
ON (IN_EPREUVE_FK = EP_ID AND YEAR(EP_DATE) = 2015)
INNER JOIN ADHESION
ON (CO_ID = AD_COUREUR_FK AND AD_ANNEE = YEAR(EP_DATE))
INNER JOIN CLUB
ON AD_CLUB_FK = CL_ID
GROUP BY CL_VILLE
HAVING NB > 1;

12. Nested queries

12.1 Nested query returning a table or a field

This type of query can be used in a FROM or a SELECT, as a data source for the main query. It is useful in the FROM, particularly to limit the number of records on which the rest of the query should apply. For the SELECT, it allows displaying in a query a field from another query, without having to perform a particular join.

SQL Syntax for a table

SELECT <field1>, <field2>, … FROM
(SELECT <field1>, <field2>, … FROM <tableB>) AS
<RESULT_TABLE_NAME>;

SQL Syntax for a field

SELECT <field1>, (SELECT <field1> FROM <tableB>) AS
<RESULT_TABLE_NAME>
FROM
<table>;

Example 1

We can calculate the number of runners registered for the Bordeaux Marathon with the following query:

SELECT MA_NOM, (
  SELECT COUNT(IN_ID)
  FROM INSCRIPTION
  INNER JOIN EPREUVE ON IN_EPREUVE_FK = EP_ID
  INNER JOIN MANIFESTATION ON EP_MANIF_FK = MA_ID
  AND MA_NOM = 'Marathon de Bordeaux'
) AS NB_INSCRIT
FROM MANIFESTATION
WHERE MA_NOM = 'Marathon de Bordeaux';

12.2 Nested query returning a column

This type of query can be used with the IN predicate for the evaluation of a field compared to a list of values returned by this query.

SQL Syntax

SELECT <field1>, <field2>, … FROM <tableA>
WHERE <field1> IN (
SELECT <field1> FROM <tableB>);

12.3 Nested queries testing the existence of a value

The use of a nested query with the IN predicate from the previous paragraph should not be confused with the use of the EXISTS predicate. This one does not check the concordance with one or more values, but if a value exists, or not. For an equivalent set of records, this predicate is therefore more performant than IN because it will not process all possibilities, but stop as soon as it becomes "true".

SQL Syntax

SELECT <field1>, <field2>, … FROM <tableA>
WHERE EXISTS (
SELECT * FROM <tableB>);

Example 2

In the Marathon project, to know the runners who have only participated in the Bordeaux Marathon, the SQL syntax is:

SELECT CO_ID, CO_NOM, CO_PRENOM
FROM COUREUR C1
INNER JOIN INSCRIPTION ON CO_ID = IN_COUREUR_FK
INNER JOIN EPREUVE ON IN_EPREUVE_FK = EP_ID
INNER JOIN MANIFESTATION ON EP_MANIF_FK = MA_ID
AND MA_NOM = 'Marathon de Bordeaux'
WHERE NOT EXISTS
(SELECT IN_COUREUR_FK
FROM INSCRIPTION
INNER JOIN EPREUVE ON IN_EPREUVE_FK = EP_ID
INNER JOIN MANIFESTATION ON EP_MANIF_FK = MA_ID
AND MA_NOM <> 'Marathon de Bordeaux' WHERE C1.CO_ID =
IN_COUREUR_FK);

In this example, the EXISTS command is preceded by NOT to verify the absence of a concordance.

This example also allows seeing that it is possible to reference, in a subquery, a field of the main query:

WHERE C1.CO_ID = IN_COUREUR_FK

The execution of this query therefore proceeds as follows: for each runner having participated in the Bordeaux Marathon, the subquery executes to verify if this runner is present, or not, at another event.

12.4 Nested query returning a value

This type of query can be used as a field of a SELECT in the main query, or as a value of a condition in a WHERE or a HAVING.

SQL Syntax

SELECT <field1>,
SELECT <field1> FROM <tableB>), … FROM <tableA>

or

SQL Syntax

SELECT <field1>, <field2>, … FROM <tableA>
WHERE <field1> = (
SELECT <field1> FROM <tableB>);

Example 3

Another way to know the runners who participated in the Bordeaux Marathon using a nested query with the following SQL syntax:

SELECT CO_ID, CO_NOM, CO_PRENOM
FROM COUREUR

EXERCISE 1

List the events displaying the name of the championship to which they belong as well as the start date.

EXERCISE 2

Write the query that provides the list of women as well as the name of the club they joined in 2015.

EXERCISE 3

Display the achieved times of participants in the half-marathon of the 2016 Bordeaux Marathon.

EXERCISE 4

List the runners who are members of "La foulée arcachonnaise" who have already participated in the Bordeaux Marathon.

EXERCISE 5

Display runners who have never joined a club.

EXERCISE 6

Who are the two runners who achieved the best times in the 2016 Médoc Marathon?

EXERCISE 7

List women who have already been registered for an event in the Masters category.

EXERCISE 8

Display the list of runners who have not participated in any event.

EXERCISE 9

List the names of runners, sorted in alphabetical order, who are members of the club "Les scientifiques font du sport" in 2016.

EXERCISE 10

Which runners have already run a marathon in more than 3 hours? Specify the first name-last name of the runners, the name of the marathon, the time and the year.