13SELECT-3

SELECT statement (3)

Outline

Join queries
Natural join queries
Column name join queries
Cross join queries
Join queries over more than 2 tables
Self-join queries

Assume, that we would like to implement a query, whose search condition applies to the columns in one relational table, while the information we search for is stored in another relational table.

For example, we would like to retrieve the values from a column: column_1, in a relational table : TABLE_1, such that the values in column: column_n in a relational table: TABLE_1 and in a column: column_1 in a relational table: TABLE_2 satisfy the given condition.

A query, that uses more than one relational table is commonly called: "join query". Implementation of join query requires application of the join operation, that connects the rows from two or more relational tables.

Next, we pick the second row from a relational table: TABLE_2. Again, we evaluate a join condition on the first row from a relational table: TABLE_1 and the second row from a relational table: TABLE_2. If a join condition evaluates to be true then we concatenate the rows into one row and we append it to the results of the join operation. Otherwise, we append nothing to the results of the join operation and we pick the third row from a relational table: TABLE_2.

Again, we evaluate a join condition on the first row from a relational table: TABLE_1 and the third row from a relational table: TABLE_2. If a join condition evaluates to true, then we concatenate the rows into one row and we append it to the results of join operation. Otherwise, we append nothing to the results of the join operation and we pick the third row from a relational table: TABLE_2.

And so on and so on.

Join queries

Join operation "connects" the rows from two relational tables

JOIN

Join queries

Join operation "connects" the rows from two relational tables

JOIN

When all rows from a relational table: TABLE_2, are used, we pick the third row from a relational table: TABLE_1 and again the first row from a relational table: TABLE_2. We evaluate a join condition on the third row from a relational table: TABLE_1 and the first row from a relational table: TABLE_2. If a join condition evaluates to be true, then we concatenate the rows into one row and we append it to the results of join operation.

Otherwise, we append nothing to the results of join operation and we pick the second row from a relational table: TABLE_2.

And so on and so on, until all rows from a relational table: TABLE_2, has been used for evaluation of a condition with the third row from a relational table: TABLE_1.

Then we pick the fourth row from a relational table: TABLE _1 and we again evaluate a join condition with all rows from a relational table: TABLE_2.

The join algorithm ends when all rows from a relational table: TABLE_1 are used for evaluation of a join condition with all rows from a relational table: TABLE_2.

Such algorithm is commonly called a "nested loop" algorithm because its implementation requires two iterations over the rows in the relational tables being joined. One iteration over a relational table: TABLE_2 is nested within the other iteration over a relational table: TABLE_1. When the relational tables are large, such algorithm is pretty poor from the performance point of view. However, it is the simplest computational model that can be used to explain how the relational tables are joined.

Join queries

Join operation "connects" the rows from two relational tables

JOIN

Join queries

Sample database

CREATE TABLE DEPARTMENT(
 name               VARCHAR(50)        NOT NULL,
 code               CHAR(5)            NOT NULL,
 total_staff_number DECIMAL(2)         NOT NULL,
 chair              VARCHAR(50)            NULL,
 budget             DECIMAL(9,1)       NOT NULL,
  CONSTRAINT dept_pkey PRIMARY KEY(name),
  CONSTRAINT dept_ckey1 UNIQUE(code),
  CONSTRAINT dept_ckey2 UNIQUE(chair),
  CONSTRAINT dept_check1 CHECK (total_staff_number BETWEEN 1 AND 50) );

CREATE TABLE COURSE(
 cnum               CHAR(7)           NOT NULL,
 title              VARCHAR(200)      NOT NULL,
 credits            DECIMAL(2)        NOT NULL,
 offered_by         VARCHAR(50)           NULL,
  CONSTRAINT course_pkey PRIMARY KEY(cnum),
  CONSTRAINT course_check1 CHECK (credits IN (6, 12)),
  CONSTRAINT course_fkey1 FOREIGN KEY(offered_by)
                        REFERENCES DEPARTMENT(name) ON DELETE CASCADE );

We start from few simple examples of queries, that require the applications of the join operation.

We consider the following query: "find the titles of all courses offered by a department chaired by Peter". We look for the relational tables required to process the query. We want to find a department chaired by Peter and to do so we need a relational table: DEPARTMENT. Then we look for the titles of courses in a relational table: DEPARTMENT, and we find no information about the courses. It means that we have to use another relational table: COURSE.

A relational table: COURSE, contains a column: title, with the titles of all courses. We discover, that the rows from a relational table: DEPARTMENT, must be joined with the respective rows from a relational table: COURSE.

The next problem is a join condition. What join condition must be used to connect the courses with the departments ?

Quite frequently, a join condition is an equality condition over a foreign key in one relational table and the respective primary key in another relational table.

In our case, it is an equality condition over the columns: name, in a relational table: DEPARTMENT, and: offered_by, in a relational table: COURSE.

A join condition over a pair: a foreign key in one table and the respective primary key in the other table, is very common, because at conceptual level such search is equivalent to the traversal of an association from one class of the objects to another class of the objects. It is one of the reasons, why we created the associations, at the conceptual modelling stage. The association allows for joining smaller pieces of information located in different classes into larger ones.

Returning to our query, at the moment we have "all cards" to implement the query. We use an operation: COURSE JOIN DEPARTMENT, in FROM clause and we follow it with a join condition: ON DEPARTMENT.name equals COURSE.offered_by.

Finally, we add WHERE condition: DEPARTMENT.chair = 'Peter' and we append SELECT clause that picks a column COURSE.title from the results of join operation. A complete SELECT statement is given at the bottom of the present slide.

Join queries

Consider the following query: Find the titles of all courses offered by a department chaired by Peter
- There are no titles of courses in a relational table DEPARTMENT !
- The titles of courses are in a relational table COURSE
- To implement the query we must use two tables: DEPARTMENT and COURSE
- The rows from a table DEPARTMENT must be joined with (connected to) the respective rows in a relational table COURSE over a condition DEPARTMENT.name = COURSE.offered_by

Implementation of a query: "find the titles of all courses offered by a department chaired by Peter", may have the following syntactical variations.

First, it is possible to remove the names of relational tables from a join condition, when the names of columns uniquely identify the columns in the relational tables being joined. It means, that a relational table: COURSE, does not have a column: name and a relational table: DEPARTMENT, does not have a column: offered_by.

The same rule applies to a condition included in WHERE clause. A name: chair, uniquely identifies a column in a relational table: DEPARTMENT, see the first SELECT statement in the present slide.

In the second example, we use the alias names instead of the names of relational tables in the SELECT, ON and WHERE clauses. To create an alias name we follow name of a relational table in FROM clause with a blank and an alias name. In the second SELECT statement a name of relational table: COURSE, is replaced with a name: C, and a name of a relational table DEPARTMENT, is replaced with a name D. Later on, both alias names: C and D, are used in the remaining clauses of the SELECT statement. Note, that when a relational table obtains an alias name, then the original name of the relational table cannot be used in the same SELECT statement anymore. For example, we cannot use the names of the relational tables: DEPARTMENT and COURSE, together with the names of their alias names in the same SELECT statement.

In the third example we use an implicit join operation. The relational tables to be joined are listed in FROM clause and the names of relational tables are separated with commas. A join condition is appended to WHERE condition as a conjunction of WHERE condition and the join condition.

Finally, the fourth version of the same SELECT statement, is created from the previous one, by elimination of the names of relational tables prefixing the names of attributes.

Join queries

Implementation of the query Find the titles of all courses offered by a department chaired by Peter has the following syntactical variations


SELECT title
FROM COURSE JOIN DEPARTMENT
            ON name = offered_by
WHERE chair = 'Peter';


SELECT C.title
FROM COURSE C JOIN DEPARTMENT D
              ON D.name = C.offered_by
WHERE D.chair = 'Peter';


SELECT COURSE.title
FROM COURSE, DEPARTMENT
WHERE DEPARTMENT.name = COURSE.offered_by AND DEPARTMENT.chair = 'Peter';


SELECT title
FROM COURSE, DEPARTMENT
WHERE name = offered_by AND chair = 'Peter';

Now, we present an implementation of the same query as before and consistent with ANSI SQL standard. ANSI SQL standard, requires application of the JOIN operation in FROM clause and it must have ON clause with a join condition.

The first SELECT statement is the original statement, that implements the query. It is a format of SELECT statement, that is strongly recommended by your lecturer, because compatibility with ANSI SQL standard makes your code relevant to many different relational database systems. It also provides all information, about the locations of the columns used in the SELECT and WHERE clauses of the relational tables.

The next two statements, are compatible with the ANSI SQL standard as well. The second SELECT statement, uses the uniqueness of the column names in the relational tables to skip the names of relational tables.

The last SELECT statement, uses the aliases for the names of relational tables, to simplify both SELECT and WHERE clauses.

ANSI SQL Syntax

The following implementations of the query Find the titles of all courses offered by a department chaired by Peter are consistent with ANSI SQL standard


SELECT COURSE.title
FROM COURSE JOIN DEPARTMENT
            ON DEPARTMENT.name = COURSE.offered_by
WHERE DEPARTMENT.chair = 'Peter';


SELECT title
FROM COURSE JOIN DEPARTMENT
            ON name = offered_by
WHERE chair = 'Peter';


SELECT C.title
FROM COURSE C JOIN DEPARTMENT D
              ON D.name = C.offered_by
WHERE D.chair = 'Peter';

SELECT statement (3)

The NATURAL JOIN operation, is a special case of the JOIN operation. A default join condition of the NATURAL JOIN is a conjunction of equality comparisons, over the columns with the same names in both relational tables.

Consider the relational tables: DEPARTMENT and EMPLOYEE. Assume, that both tables have a column with the same name: dname. Next, consider a query: "find the names of all employees from a department chaired by James Bond". The query can be implemented as SELECT statement with the operation of the NATURAL JOIN.

A default join condition is the following: DEPARTMENT.dname equals EMPLOYEE. dname. The results of the JOIN operation have only one column dname. This is because a join condition is an equality condition on the columns with the same names in both relational tables. Then, the values in such columns in the results of the JOIN operation must be the same and there is no need to replicate a column: dname, twice.

A sample implementation of SELECT statement with the operation of the NATURAL JOIN, is given in the middle of the present slide. An equivalent implementation, that uses the JOIN operation is given at the bottom of the present slide.

Natural join queries

Consider a query: Find the names of all employees from a department chaired by James Bond, that requires the following relational tables:

A natural join query


SELECT ename
FROM EMPLOYEE NATURAL JOIN DEPARTMENT
WHERE chair = 'James Bond';

is equivalent to a join query


SELECT ename
FROM EMPLOYEE JOIN DEPARTMENT
              ON EMPLOYEE.dname = DEPARTMENT.dname
WHERE chair = 'James Bond';

SELECT statement (3)

Outline

Join queries
Natural join queries
Column name join queries
Cross join queries
Join queries over more than 2 tables
Self-join queries

The operation of COLUMN JOIN is a special case of the JOIN operation. A default join condition of COLUMN JOIN is a conjunction of the equality comparisons over the columns listed in the USING clause of the operation. The names of columns listed in the USING clause must be included in both headers of the relational tables being joined.

The USING clause attached to the JOIN operation includes a name of a column: dname, to indicate a join condition, that is applied to join the rows from the relational tables. Then, a default join condition is: DEPARTMENT.dname = EMPLOYEE.dname.

The results of the JOIN operation have only one column: dname. This is because, the join condition is an equality condition on the columns with the same names listed in USING clause. Then, the values in such columns must be the same as in the results of the COLUMN JOIN operation and there is no need to replicate a column dname twice.

A sample implementation of SELECT statement with the operation COLUMN JOIN, is given in the middle of the present slide. An equivalent implementation with the JOIN operation is given at the bottom of the present slide.

Column name join queries

Consider a query: Find the names of all employees from a department chaired by James Bond, that requires the following relational tables:

A column name join query


SELECT ename
FROM EMPLOYEE JOIN DEPARTMENT
              USING(dname)
WHERE chair = 'James Bond';

is equivalent to a join query


SELECT ename
FROM EMPLOYEE JOIN DEPARTMENT
              ON  EMPLOYEE.dname = DEPARTMENT.dname
WHERE chair = 'James Bond';

SELECT statement (3)

Outline

Join queries
Natural join queries
Column name join queries
Cross join queries
Join queries over more than 2 tables
Self-join queries

Assume, that we would like to join all rows from one relational table, with all rows from the other relational table. You may ask why would we need to do something like that ? For example, you may wish to enforce a logical consistency constraint, on the combinations of pairs of values from two different columns. It can be done by creating a separate table with two columns, that contain the permitted pairs of values. When a lot of pairs of values is permitted and only few are forbidden, then it is easier, to create all possible pairs and to remove the pairs that are forbidden.

In another example, we would like to implement a query, that finds the departments that offer all courses. Then, to implement such query we join all departments with all courses and we remove from the result information about the departments that offer courses now. All departments, that disappear from the results offer all courses.

The CROSS JOIN operation "joins" all rows from one relational table, with all rows from another relational table.

For example assume, that a relational table: TABLE 1, contains one red row and one orange row and a relational table:TABLE 2, contains one green row and one blue row. Then, the CROSS JOIN operation creates a new relational table that contains four rows: red-green, red-blue, orange-green and orange-blue. It means, all combinations of rows from the relational tables: TABLE 1 and TABLE 2.

Cross join queries

Cross join operation "connects" all rows from a relational table with all rows from another relational table

CROSSJOIN

Consider the relational tables: DEPARTMENT and EMPLOYEE.

A query: "find all pairs of names of employees and names of chair people" can be implemented with the CROSS JOIN operation on the relational tables: EMPLOYEE and DEPARTMENT.

The first SELECT statement, in the present slide contains implementation of the query with the CROSS JOIN operation. The results of CROSS JOIN operation are the same as the results of a query with JOIN operation, that has missing join condition in ON clause.

Also a query, that lists two or more relational tables in FROM clause and it does not have a join condition in WHERE clause, can be used to compute the CROSS JOIN operation.

It is recommended, to explicitly use the CROSS JOIN operation in FROM clause, because in such a case, there are no doubts, that we really would like to compute the CROSS JOIN and it is not an accidental outcome of missing or incorrect ON clause or missing or incorrect join condition in WHERE clause.

Note, that if both relational tables contain 1000 rows, then the result of the CROSS JOIN will contain 1000 multiplied by 1000 rows, it means 1000000 rows.

Cross join queries

Consider a query: Find all pairs of the names of employees and the names of chair people, that requires the following relational tables:

A cross join query


SELECT ename, chair
FROM EMPLOYEE CROSS JOIN DEPARTMENT;

is equivalent to the following join queries


SELECT ename, chair
FROM EMPLOYEE JOIN DEPARTMENT;


SELECT ename, chair
FROM EMPLOYEE, DEPARTMENT;

SELECT statement (3)

Outline

Join queries
Natural join queries
Column name join queries
Cross join queries
Join queries over more than 2 tables
Self-join queries

Assume, that we would like to join three relational tables: TABLE 1, TABLE 2 and TABLE 3. To make things a bit easier, we create the alias names for the tables: T 1, T 2 and T 3.

Assume, that the relational tables: T 1 and T 2, must be joined over a join condition: Condition Then, the results of the first join must be joined with the relational table: T 3, over a condition Condition 2.

The first join is expressed in a usual way with the JOIN operation in FROM clause and with ON clause and a join condition: Condition 1. Please, see the first three lines of SELECT statement on the right hand side of the present slide.

Next, to join the results of the first JOIN with a relational table: T 3, we append the next JOIN operation with a name of a relational table T 3.

Then, we append the next join condition to join T3 with the results of the earlier join of T 1 and T 2.

It is possible to express the same sequence of operations by listing the names of relational tables to be joined in: FROM clause, and by adding a conjunction of join conditions: Condition 1 and Condition 2, to WHERE clause. Please, see SELECT statement on the left hand side of the present slide.

Join of three relational tables, is a single relational table that consists of all columns from the joined relational tables and all rows created from the triples of the rows from: T 1, T 2 and T 3 such that each triple satisfies both conditions: Condition 1 and Condition 2.

Join queries over more than 2 tables

A sample join of three relational tables TABLE_1, TABLE_2, and TABLE_3

3xJOIN

It is still possible to implement join of three or more relational tables, as a sequence of joins over two tables and inline views. An idea is to join two relational tables first, and save a temporary result of join as an inline view. And then, join a temporary inline view with the third relational table.

The present slide visualises a process of joining multiple relational tables, through creation of the intermediate inline views. We first join the relational tables: TABLE 1 and TABLE 2. The results of the join becomes a temporary inline view, that obtaines a name: TABLE 1 2.

SELECT statement, that joins the relational tables: TABLE 1 and TABLE 2 is enclosed within parenthesis and it is followed by a name TABLE 1 2. Then, a definition of a temporary inline view is placed in FROM clause and it is followed by the JOIN operation with TABLE 3.

A complete SELECT statement, is given in the left lower corner of the present slide.

Join queries over more than 2 tables

Application of inline view to simplify join of three relational tables TABLE_1, TABLE_2, and TABLE_3

3xJOIN

To practice the JOIN operation on many relational tables, we consider the following example.

A relational table: COURSE, contains information about the courses described by a course number ( a column cnum), course title ( a column title) and credits.

A relational table: STUDENT, contains information about the students and it consists of the columns: student number, (column snum), full name (column name) and degree enrolled by each student.

A relational table: ENROLMENT, contains information about the enrolments performed by the students.

The table consists of the columns: course number ( column cnum), student number (column snum), enrolment date (column edate) and the final result scored by each student (column: result).

We would like to implement a query: "find the names of all students who enrolled a Java course". To find a Java course, we need to access a column: title, in a relational table: COURSE. To find the names of students, we need to access a column: name, in a relational table: STUDENT. To join information about the courses, with information about the students, we must use information about the courses enrolled by the students. It means that we must use a relational table: ENROLMENT.

To join information about the courses, with information about the enrolments, we create a join condition: COURSE.cnum = ENROLMENT.cnum.

To join information about the students, with information about the enrolments we create a join condition: STUDENT.snum = ENROLMENT.snum.

Now, we can create SELECT statement that joins the relational tables: COURSE, ENROLMENT and STUDENT. First, we join in FROM clause the relational tables: COURSE and ENROLEMENT, over a join condition: COURSE.cnum = ENROLMENT.cnum. Next, the result is joined with a relational table: STUDENT, over a join condition: STUDENT.snum = ENROLMENT.snum. Finally, WHERE clause filters the rows where a title of a course is: Java.

A complete SELECT statement, that joins three relational tables, is given at the bottom of the present slide,

Join queries over more than 2 tables

Consider the relational tables with the following schemas

COURSE
       cnum | title | credits

STUDENT
       snum | name | degree

ENROLMENT
       cnum | snum | edate | result

A query find the names of all students who enrolled Java course can be implemented as the following join query


SELECT STUDENT.name
FROM COURSE JOIN ENROLMENT
            ON COURSE.cnum = ENROLMENT.cnum
            JOIN STUDENT
            ON ENROLMENT.snum = STUDENT.snum
WHERE COURSE.title = 'Java';

The present slide, shows three different implementations of a query: "find the names of all students who enrolled a Java course".

The first SELECT statement uses ANSI S Q L standard of the JOIN operation.

The second SELECT statement, lists all relational tables involved in the JOIN operation in the FROM clause and, it lists all join condition as the Boolean expression in the WHERE clause. In such case we expect, that a query processor will be able to correctly extract, a join condition from a conjunction of elementary conditions in WHERE clause.

The third SELECT statement, uses a technique of inline views, to first join a relational table: COURSE, with a relational table: ENROLMENT and to create a temporary inline view: CE. Then, a temporary inline view: CE, is joined with a relational table: STUDENT.

Join queries over more than 2 tables

Implementation of a query find the names of all students who enrolled Java course has the following syntactical variations

SELECT STUDENT.name
FROM COURSE JOIN ENROLMENT
            ON COURSE.cnum = ENROLMENT.cnum
            JOIN STUDENT
            ON ENROLMENT.snum = STUDENT.snum
WHERE COURSE.title = 'Java';

SELECT STUDENT.name
FROM COURSE, ENROLMENT, STUDENT
WHERE COURSE.cnum = ENROLMENT.cnum AND ENROLMENT.snum = STUDENT.snum AND 
       COURSE.title = 'Java';

SELECT STUDENT.name
FROM ( SELECT *
       FROM COURSE JOIN ENROLMENT
                   ON COURSE.cnum = ENROLMENT.cnum 
       WHERE COURSE.title = 'Java' ) CE JOIN STUDENT
                                        ON CE.snum = STUDENT.snum
WHERE COURSE.title = 'Java';

SELECT statement (3)

Outline

Join queries
Natural join queries
Column name join queries
Cross join queries
Join queries over more than 2 tables
Self-join queries

Implementation of certain queries may require a join of a relational table with itself. It happens, when a relational table implements one to many association, that links the same class of objects on its left and right hand side.

For example, an association: EMPLOYEE Is manager of EMPLOYEE, is a typical example of one to many association, that have the same class of objects at both of its ends. Then, the logical design transforms such association into a single relational table, that has a foreign key referencing the primary key, in the same relational table.

For example, a column: manager number, references the primary key: employee number, in a relational table: EMPLOYEE. Then, navigation over an association: Is Manager of, is equivalent in a relational implementation to a join of a relational table: EMPLOYEE, with itself.

How to join a relational table with itself, when it is not possible to list in FROM clause, two identical names of the relational tables ? To solve the problem, we give two alias names to the same relational table. Then, we imagine ourselves, that we operate on two relational tables, that have the different names and, that have the same columns and the same contents.

A sample SELECT statement, that creates two alias names: T 1 and T 2, for a relational table: TABLE, is given in the middle of the slide.

Self-join queries

What if a relational table must be joined with itself ?

SELFJOIN

A table, that must be joined with itself obtains two different alias names

SELFJOIN

Consider a relational table: EMPLOYEE, that contains information about the employees and their managers. A relational table: EMPLOYEE, consists of the columns enum (employee number), name ( employee name) and manager ( employee number of a manager ).

The sample contents of a relational table: EMPLOYEE, is given in the middle of the present slide.

An employee: John, has the employee number 10 and he does not have an employee number of his manager. It means, that John is the topmost manager. An employee: Peter, has the employee number 20 and his manager is an employee, that has the number 10, John. An employee: Mary, has the employee number 30 and her manager is also an employee, that has the number 10, John. An employee: Mike, has the employee number 40 and his manager is an employee, that has the number 20, Peter.

In the same way we can find the managers of the employees number 50, 60 and 70.

Assume, that we would like to implement a query: "find a name of manager of employee number 40". We can "plan" the implementation in the following way. First, we find the employee number of a manager of employee number 40. Then, we find a name of the employee found in the previous query.

Self-join queries

Consider a relational table with the following schema:

and the following contents:

+------+-------+---------+
| enum | name  | manager |
+------+-------+---------+
|   10 | John  |    NULL |
|   20 | Peter |      10 |
|   30 | Mary  |      10 |
|   40 | Mike  |      20 |
|   50 | Kate  |      20 |
|   60 | Greg  |      50 |
|   70 | Phil  |      50 |
+------+-------+---------+

Consider a query find a name of manager of employee number 40
We can "plan" the implementation in the following way:
- (1) Find an employee number of manager of employee number 40
- (2) Find a name of employee found in the previous query

Self-join queries

Implementation of a plan:

(1) Find employee number of manager of employee number 40
(2) Find a name of employee found in the previous query

is the following:

SELECT manager 
FROM EMPLOYEE
WHERE enum = 40;

SELECT name 
FROM EMPLOYEE
WHERE enum = 20;

Peter

Is it possible to implement the query as one SELECT statement ?
YES ! and it is possible to do it in more than one way !

Self-join queries

Solution 1

Assume that we have two identical relational tables E1 and E2

E1                                     E2
+------+-------+---------+             +------+-------+---------+   
| enum | name  | manager |             | enum | name  | manager |
+------+-------+---------+             +------+-------+---------+
|   10 | John  |    NULL |             |   10 | John  |    NULL |
|   20 | Peter |      10 |    -------->|   20 | Peter |      10 |
|   30 | Mary  |      10 |    |        |   30 | Mary  |      10 |
|   40 | Mike  |      20 |-----        |   40 | Mike  |      20 |
|   50 | Kate  |      20 |             |   50 | Kate  |      20 |
|   60 | Greg  |      50 |             |   60 | Greg  |      50 |
|   70 | Phil  |      50 |             |   70 | Phil  |      50 |
+------+-------+---------+             +------+-------+---------+

To find a name of manager of employee number 40 we take a row
- from a table E1 and we join over a condition E1.manager = E2.enum with a row
- from a table E2

Self-join queries

So, how do we implement such "magic" ?

SELECT E2.name
FROM EMPLOYEE E1 JOIN EMPLOYEE E2
                 ON E1.manager = E2.enum
WHERE E1.enum = 40;

E1                                     E2
+------+-------+---------+             +------+-------+---------+   
| enum | name  | manager |             | enum | name  | manager |
+------+-------+---------+             +------+-------+---------+
|   10 | John  |    NULL |             |   10 | John  |    NULL |
|   20 | Peter |      10 |    -------->|   20 | Peter |      10 |
|   30 | Mary  |      10 |    |        |   30 | Mary  |      10 |
|   40 | Mike  |      20 |-----        |   40 | Mike  |      20 |
|   50 | Kate  |      20 |             |   50 | Kate  |      20 |
|   60 | Greg  |      50 |             |   60 | Greg  |      50 |
|   70 | Phil  |      50 |             |   70 | Phil  |      50 |
+------+-------+---------+             +------+-------+---------+

Self-join queries

Solution 2

We use inline views technique to combine the following queries

SELECT manager 
FROM EMPLOYEE
WHERE enum = 40;

SELECT name 
FROM EMPLOYEE
WHERE enum = 20;

We use the first SELECT statement to create an inline view E40

Then we join an inline view E40 with a relational table EMPLOYEE

SELECT EMPLOYEE.name
FROM EMPLOYEE JOIN ( SELECT manager 
                     FROM EMPLOYEE
                     WHERE enum = 40 ) E40
              ON EMPLOYEE.enum = E40.manager;

Self-join queries

In another query we find the names of all employees directly managed by Kate

+------+-------+---------+
| enum | name  | manager |
+------+-------+---------+
|   10 | John  |    NULL |
|   20 | Peter |      10 |
|   30 | Mary  |      10 |
|   40 | Mike  |      20 |
|   50 | Kate  |      20 |
|   60 | Greg  |      50 |
|   70 | Phil  |      50 |
+------+-------+---------+

We "plan" the implementation in the following way:
- (1) Find an employee number of an employee Kate
- (2) Find the names of employees who have a number found in the previous query in a column manager

Self-join queries

Solution 1

Assume that we have two identical relational tables E1 and E2

E1                                     E2
+------+-------+---------+             +------+-------+---------+   
| enum | name  | manager |             | enum | name  | manager |
+------+-------+---------+             +------+-------+---------+
|   10 | John  |    NULL |             |   10 | John  |    NULL |
|   20 | Peter |      10 |             |   20 | Peter |      10 |
|   30 | Mary  |      10 |             |   30 | Mary  |      10 |
|   40 | Mike  |      20 |             |   40 | Mike  |      20 |
|   50 | Kate  |      20 |    -------- |   50 | Kate  |      20 |
|   60 | Greg  |      50 |<---|        |   60 | Greg  |      50 |
|   70 | Phil  |      50 |<---|        |   70 | Phil  |      50 |
+------+-------+---------+             +------+-------+---------+

To find a number of employee Kate we take a row
- from a table E2 and we join it over a condition E2.enum = E1.manager with the rows
- from a table E1

Self-join queries

So, how do we implement such "magic" ?

SELECT E1.name
FROM EMPLOYEE E1 JOIN EMPLOYEE E2
                 ON E1.manager = E2.enum
WHERE E2.name = 'Kate';

E1                                     E2
+------+-------+---------+             +------+-------+---------+   
| enum | name  | manager |             | enum | name  | manager |
+------+-------+---------+             +------+-------+---------+
|   10 | John  |    NULL |             |   10 | John  |    NULL |
|   20 | Peter |      10 |             |   20 | Peter |      10 |
|   30 | Mary  |      10 |             |   30 | Mary  |      10 |
|   40 | Mike  |      20 |             |   40 | Mike  |      20 |
|   50 | Kate  |      20 |    -------- |   50 | Kate  |      20 |
|   60 | Greg  |      50 |<---|        |   60 | Greg  |      50 |
|   70 | Phil  |      50 |<---|        |   70 | Phil  |      50 |
+------+-------+---------+             +------+-------+---------+

Self-join queries

Solution 2

We use inline views technique to combine the following queries

SELECT enum 
FROM EMPLOYEE
WHERE name = 'Kate';

SELECT name 
FROM EMPLOYEE
WHERE manager = 50;

We use the first SELECT statement to create an inline view KATE

( SELECT enum 
  FROM EMPLOYEE
  WHERE name = 'Kate' ) KATE

Then we join an inline view KATE with a relational table EMPLOYEE

SELECT EMPLOYEE.name
FROM EMPLOYEE JOIN ( SELECT enum 
                     FROM EMPLOYEE
                     WHERE name = 'Kate' ) KATE
              ON EMPLOYEE.manager = KATE.enum;

References

C. Coronel, S. Morris, A. Basta, M. Zgola, Data Management and Security, Chapters 5, 7, Cengage Compose eBook, 2018, eBook: Data Management and Security, 1st Edition
T. Connoly, C. Begg, Database Systems, A Practical Approach to Design, Implementation, and Management, Chapters 6.3.7 Multi-table Queries, Pearson Education Ltd, 2015
D. Darmawikarta, SQL for MySQL A Beginner’s Tutorial, Chapter 6, pages 55 - 61 Brainy Software Inc. First Edition: June 2014
How to ... ? Cookbook, How to implement queries in SQL ? (Part 1), Recipe 5.4 How to implement simple join queries ?
How to ... ? Cookbook, How to implement queries in SQL ? (Part 2) Recipe 6.1 How to implement self join queries ?