Complex Database Queries on Multiple TablesArticle contents:
Composite Quantities (JOIN)
As a rule, the data stock in relational databases is distributed over several tables. However, composite quantities are used to retrieve specific information from the database that is distributed over several tables. The basic idea behind composite quantities is to generate a new relation from several already existing relations. The SELECT statement is used to generate a result table from a database table. With the composite quantities presented in the following, targeted information from several database tables can also be compiled in a virtual table.
Basic Principle of Composite Quantities
The following example illustrates how a compound set works: There are two tables, Article and Film, in a database, where the ArticleID attribute of the Film table is a foreign key that references the ArticleID attribute of the Articles table:
|1||UZEHN78126||10.99||On the Road to Nothingness||At the drop of a hat|
|2||ZHBH789123||13.99||Behind Us||At the drop of a hat|
|5||KJNSM7873||9.99||Help you||In 3 days|
|6||UIHJKHSD7||8.99||Around the Planet||In 2 days|
|FilmID||Director||Languages||Subtitles||Number of discs||Studio||Medium||ArticleID Article > ArticleID|
|3||Sophie Little||DE, EN, FR||DE, EN||1||DreamPictures||DVD||1 [>]|
|4||Fred van Heldt||EN, FR||EN, FR, DE||2||MovieFactory||DVD||2 [>]|
With the help of a composite, both tables article and film are merged into one table (also: combined). The new table created in this way has all the columns from article and movie.
When tables are merged, the data records are also merged. Because each row in a table corresponds to a data record, you must specify which data records are to be merged for a compound. For this purpose, a compound criterion must be determined. In the example, the records that have the same value in the ArticleID column are merged. If a record in the Article table has the same ArticleID as a record in Film, these records are combined in the new table. The formal comparison criterion is therefore Article.ArticleID=Film.ArticleID.
After merging the data records, the resulting table is as follows:
However, because not all information on each film will be searched for but only the attribute’s name, director, price, and availability, only these desired columns are selected in the compound set and displayed in the chosen order. All other columns are not required. Therefore, the outcome of the request is as follows:
|On the Road to Nothingness||Sophie Little||10.99||At the drop of a hat|
|Behind Us||Fred van Heldt||13.99||At the drop of a hat|
The SQL syntax for the example just described is shown and explained in figure 27.
Different types of alliances are distinguished in relational databases. In addition to the inner join, there is also frequent use of the natural join, the left join, and the right join. The example database shown in table 44 shows the most important types of alliances.
|Table 45: Important Compound Types of Relational Databases|
|Composite type with SQL statement||Result|
|INNER JOIN with ON Compound criterionSELECT * FROM Article INNER JOIN Film ON Article.ArticleID=Film.ArticleID;||Merges all data records for which the compound criterion is fulfilled. All columns of all tables are included in the result.|
|INNER JOIN with USING(columns); used if the columns of the compound criterion have the same name.SELECT * FROM Article INNER JOIN Film USING(ArticleID);Note: USING can contain one or more columns as parameters.||Merges all data records whose values match in all columns named as parameters of USING. The comparison columns only exist once in the result table.|
|INNER JOIN is often abbreviated as JOIN, i.e., a JOIN command without a more precise specification is an INNER JOIN.|
|NATURAL JOIN: Variant of INNER JOINSELECT * FROM article NATURAL JOIN film;||Compares the records of all columns with the same column name. In the example, this would be the columns ArticleID and Number. The data record is only included in the result table if there is a complete match. The columns with the same name exist only once in the result.|
|Instead of ON, you can also specify the JOIN condition with USING for LEFT JOIN and RIGHT JOIN.|
|LEFT JOINSELECT * FROM Article LEFT JOIN Film ON Article.ArticleID=Film.ArticleID;||Merges all data records for which the compound criterion is fulfilled. All columns of all tables are included in the result. In addition, all data records of the table to the left of the JOIN statement (here: article) are included in the result. The columns copied from the right table that have no entries matching the left table IDs are filled with NULL.|
|RIGHT JOINSELECT * FROM Article RIGHT JOIN Film ON Article.ArticleID=Film.ArticleID;||Merges all data records for which the compound criterion is fulfilled. All columns of all tables are included in the result. In addition, all data records from the table to the right of the JOIN statement (here: film) are included in the result. The columns copied from the left table that have no entries matching the right table IDs are filled with NULL.|
The use of associations does not fundamentally change the structure of the SELECT statement. The result set of a compound can also be grouped with GROUP BY and sorted with ORDER BY.
The following example determines the number of instantly available movies delivered on more than one disc, grouped by the number of discs:
SELECT NumberDiscs, COUNT(*) AS Number FROM Article NATURAL JOIN FILM WHERE Availability=’immediate’ AND Number of discs > 1 GROUP BY Number of discs;
Link to WHERE
An outdated alternative to maintaining inner networks can be implemented with the keyword WHERE, as shown in table 46. This variant returns the same result as INNER JOIN, but mixes the compound criterion and the filter condition and is therefore considered bad programming style.
|Table 46: Internal Bond with WHERE|
|Composite Type with SQL Statement||Result|
|INNER JOIN with WHERESELECT * FROM Article, Film WHERE Article.ArticleID=Film.ArticleID;||Creates a result with all records from articles and films for which WHERE conditions are met.|
The result of a SELECT request is a set of identically structured data records. Therefore, in principle it is possible to perform set operations on these results. The SQL standard specifies the set operations Union, Intersection, and Minus. However, many DBMSs do not implement the operations Intersection and Difference Set, since they are rarely needed in practice and can be replicated with WHERE using conditions.
Because set operations deliver sets as results, they can be used to clean up the result sets of several SELECT queries and then process them further. The general form for the set operation is shown in figure 28.
The keyword for a duplicate-free union is UNION. If duplicates are not to be eliminated during unification, the keyword UNION ALL must be used. The keyword INTERSECT is used for an intersection and, depending upon the DBMS used, the keyword MINUS (e.g., Oracle) or EXCEPT (e.g., MariaDB, since version 10.3.0) is used for differences. The following example illustrates the use of the set operation union: The stock of all articles in the online shop, with name, price, and article type sorted by name, is to be indicated regardless of which concrete article type it concerns. The SQL statement shown and explained in figure 29 can be formulated for this purpose.
The result set of the individual SELECT requests must have the same number of columns, otherwise the set operation will fail. If, however, data records with different attributes have to be combined, the unequal columns can be filled with NULL or another constant. In the above example, adding the Type column and filling the attributes with the fixed values “Film” or “Musical Album” provides information about the origin of the records to be merged.
Data Views with CREATE VIEW
In order not to have to formulate complex, frequently required database queries anew each time as SQL statements, relational databases offer the possibility to create views. Another application of views is the implementation of access restrictions on data for different user groups. In principle, these are SELECT statements that are stored under a specific name in the database. No additional data records are stored in a view. A view is therefore sometimes called an “imaginary” or “virtual” table. A generated view can be addressed in other SQL statements like a table in the database.
A view is to be created in the ONLINESHOP database that contains a list sorted by name of all immediately available films and their detailed information.
SELECT * FROM Article INNER JOIN FILM USING(ArticleID) WHERE Availability = 'immediately' ORDER BY Name;
To create a view, use the SQL statement CREATE VIEW as shown in Figure 30. Using a prefix, views can be clearly distinguished from tables when used later.
The view created above can now be reused under the name ViewImmediatelyAvailableFilms. The SQL command in figure 31 uses the created view to output all immediately available movies that are available in German. The view is used there like a normal database table in the SELECT statement.
Views are primarily used to read data. Under the following conditions, you can also make changes to the dataset directly in the view:
- The view does not contain an aggregate function, constant, or expression;
- The view only refers to one table, so no JOIN is allowed;
- The view does not use DISTINCT and does not use GROUP BY;
- The view does not contain a subquery.
If all these conditions are met, the view is writable. Otherwise, a Read Only View is defined.