What is the standard JOIN in SQL

7. SQL and relational algebra

7.1 SQL (Structured Query Language)

SQL is 'the' language that is used to create, manipulate, and query most relational databases. SQL is a so-called 4GL (Fourth-Generation Language). she is non-procedural, d. H. the questioner asks a question, but does not provide an algorithm for the solution. In a 3GL such as Cobol, Pascal or C, he would have to specify how the information he is looking for can be found, e.g. B. from opening the file to stepping through the records.

SQL is an ISO and ANSI standard that has been or is being specified several times:

  • SQL 89
    Defined in 1989. Two possible levels of language scope:
  • SQL 92 (SQL2)
    Defined in 1992. Four possible levels of language scope:
    • Entry level
    • Transitional
    • Intermediate level
    • Full level

In addition to a specific SQL standard, database systems usually support parts of higher standards as well as their own SQL extensions. Even today, SQL 89 Level 2 is the basis of the SQL supported by many database systems, with SQL 92 most systems are only Entry Level Compliant (e.g. Oracle8).

7.2 Relational algebra

With a suitable query language, desired data can be found in a relational database. For this z. B. SQL. The following set theory operations can be used:

7.2.1 Selection

Selects rows from a table that meet a certain condition.

Example:

The SQL statement

    returns all lines of the above table that meet the specified condition as the result. All columns are displayed in the output (after SELECT, the columns that are to be displayed are listed. The * character stands for 'all columns')

    7.2.2 Projection

    Selects specific columns in a table.

    Example:
    The SQL statement

      delivers z. B. as a result:

      Of course, selection and projection can also be used together.

      7.2.3 Union

      Merges the rows of two tables with the same number of columns in one table. The names of the respective columns of the two tables do not have to be identical, only the data type or value range of the content

      Example:

      The SQL statement

        gives as result:

        Duplicate lines are automatically suppressed. They are displayed with. UNION belongs to the SQL 92 Entry Level.

        7.2.4 Intersection

        Returns the lines contained in both specified tables.

        Example with the two tables "Employees" and "Customers":

        The SQL statement

          delivers as a result

          INTERSECT is not always available because it belongs to the SQL 92 intermediate level. However, it can be simulated if necessary, e.g. B. by a nested SELECT statement:

            7.2.5 Minus (difference)

            Returns the lines of the first table that are not contained in the second table.

            Example with the two tables Employee and Customers:

            The SQL statement

              delivers as a result

              MINUS is not always available because it belongs to the SQL 92 intermediate level. However, it can be simulated if necessary, e.g. B. by a nested SELECT statement:

                7.2.6 Join

                Combines the columns of two tables into one table. There are several variants of the join:

                • Cross Join, Cartesian Product
                  Joins each row of the first table with each row of the second table

                    (SQL92)

                    (often used before SQL92)

                  delivers as a result

                  Columns of the same name in the two tables are referenced by placing the table name in front, e.g. B. "Employee.Lastname".

                  Note:
                  With a cross join of large tables, the result table becomes very large. The result of a cross join is often useless!

                • Inner Join = Equivalent Join
                  Joins data records from two tables as soon as a common field contains the same values.
                • Example with the two tables "Employees" and "Projects":

                  The SQL statement

                    (SQL92)

                    (often used before SQL92)

                  compares for matching surnames () and returns as result

                  Note:
                  Since only the last name was compared, two different people appear in the first line (Huber Karl and Huber Anna). Of course, first and last names can also be compared:

                    In practice, an inner join will not display all but only selected columns. The SQL statement

                      delivers z. B .:

                      This join is known as a natural join (see below).

                      If only JOIN is specified instead of INNER JOIN in an SQL statement, an inner join is usually also carried out.

                    • Natural join
                      Links the two tables via the equality of all identical columns. Columns of the same name are only displayed once in the result. If the tables do not have identical columns, the natural join becomes a cross join. If there is only one column with the same name, the natural join is an inner join with a subsequent projection in which columns with the same name are hidden.
                    • There is no special SQL92 command for natural join. If required, it is generated from an inner join with subsequent projection.

                    • Left outer join = left join
                      With a left join, a so-called left inclusion join is created. Left inclusion links include all records from the first (left) table, even if there are no corresponding values ​​for records in the second table.
                    • Example with the two tables "Employees" and "Projects":

                      The SQL statement

                        delivers:

                        The employees who are not working on any project are also displayed here. NULL means that the field does not contain an entry.

                        Often you can also see the older notation for a left outer join (e.g. with Oracle):

                          The (+) indicates the columns in which space must be reserved for NULL values. In the case of a left outer join, this means for the columns on the right (!) Side.

                        • Right Outer Join = Right Join
                          With a right join, a so-called right inclusion join is created. Right inclusion links include all records from the second (right) table, even if there are no corresponding values ​​for records in the first table.
                        • Example with the two tables "Employees" and "Projects":

                          The SQL statement

                            delivers:

                            The projects that no employee of the company is working on (but perhaps external workers) are also displayed here.

                            Here, too, you can often see the older spelling for a right outer join:

                              The (+) indicates the columns in which space must be reserved for NULL values. In the case of a right outer join, this means for the columns on the left (!) Side.

                            • Full outer join = full join
                              A combination of the left outer join and the right outer join.
                            • Example with the two tables "Employees" and "Projects":

                              The SQL statement

                              delivers:

                              In the older notation, a full outer join cannot be created with (+) signs on both sides, but is put together via the union of a left outer join and a right outer join.

                            • Union join
                              Similar to the full outer join, data records from both tables are included. However, they are not linked via a condition.
                            • Example with the two tables "Employees" and "Projects":

                              The SQL statement

                              delivers:

                              The union join is not always available because it belongs to the SQL 92 intermediate level

                            • Semi-join
                              The semijoin of the tables "Employees" and "Projects" is a natural join of the two tables with subsequent projection onto the attributes of the first table:
                            • The SQL statement

                              delivers:

                            • Theta join, non-equivalent join
                              The theta join is a generalization of the inner join. While the inner join compares the equality of the content of two attributes, the theta join compares the content of the attributes i and j with an arbitrary formula theta (i, j), e.g. i = j (i equals j; InnerJoin), i < j (i less than j), i <= j (i less than or equal to j), i> j (i greater than j) etc.
                            • Example:

                              In the lexical sense z. B. Huber smaller than Trunstein, since H comes before T in the alphabet. The result is the following table:

                            • Self-join
                              The self-join is any join that does not use two different tables, but uses the same table twice.
                            • Example:

                              The name of the superior should be determined for each employee.

                              The SQL statement

                              delivers as a result

                              In order to be able to use the same table twice, it is given two different alias names a and b. The heading of the 'Name' column is changed to 'Chef' for the output in the second table. Without the left outer join (+), those employees who do not have a manager would not be displayed.

                            7.2.7 Division (quotient)

                            The concept of division is closely related to the Cartesian product T = R x S of two relations R and S, so that T / S (T divided by S) gives the relation R. If T has the number t columns and S the number s columns, then T / S has the number t - s columns.

                            Example:

                            Then project work / projects deliver:

                            Important:
                            In general, the division R = T / S does not mean that T = R x S, since additional tuples may appear in T. In the table above, e.g. B. anywhere the line

                            stand without changing the result of the division.


                              Table of Contents