Requests using connections

3.2.4. Table composition

To exclude all columns by which the tables are joined, you need to create a composition

  SELECT Meal, View, Dish, Basis, Exit, Labor
 FROM Menu, Meals, Vid_blyud, Dishes
 WHERE Menu. T = Meals. T
 AND Menu. In = View_w.
 AND Menu. BL = Dish. BL; 

looking

3.2.5. Theta join of tables

In the PENSION database, it is difficult to find a simple example illustrating theta joining of tables. Therefore, we construct such an invented query:

  SELECT View_bud. *, Meals. *
 FROM Vid_blyud, Meals
 WHERE View> Meal; 

allowing you to select from the Cartesian product of Table Vid_Blyud and Trapeze obtained in paragraph 3.2.1 only those lines in which the meal value is "less" (in alphabetical order) of the type of dish:

3.2.6. Connection of tables with an additional condition

When forming a connection, a work table is created to which all operations described in Chapter 2 are applicable: selection of the necessary connection lines (WHERE phrase), ordering the result obtained (ORDER BY phrase) and data aggregation (SQL functions and GROUP BY phrase).

For example, to get the list of dishes offered in the breakfast menu, you can form a request based on the composition (Section 3.2.4):

  SELECT View, Dish, Basis, Outlet, 'Number -', BL
 FROM Menu, Meals, Vid_blyud, Dishes
 WHERE Menu. T = Meals. T
 AND Menu. In = View_w.
 AND Menu. BL = Dish. BL
 AND Meal = 'Breakfast'; 

Will get

In clause 3.6, you can get acquainted with a fairly complete example of the connection of tables with various additional phrases.

3.2.7. Join table with your copy

In a number of applications, it is necessary to simultaneously process the data of a table and one or more of its copies created during the execution of the query.

For example, when creating student lists (the Students table) it is possible to re-enter data about a student with the assignment of a second gradebook number to him. To identify such errors, you can connect the Students table with its temporary copy by setting in the WHERE phrase the equality of the values ​​of all the same columns of these tables except the columns with the record book number (for the latter it is necessary to establish the value inequality condition).

A temporary copy of the table can be formed by entering the alias name followed by the table name in the FROM clause. So, using the phrase

  FROM Dishes X, Dishes Y, Dishes Z 

three copies of the Dishes table with the names X, Y and Z will be generated.

As an example of joining a table with it, we will form a request for the output of such pairs of dishes from the Dish table in which the base coincides, and the name of the first course of the pair is smaller (alphabetically) than the number of the second course of the pair. To do this, you can create a request with one copy of the Dish (Copy) table:

  SELECT Dish, Copy. Dish, Basis
 FROM Dishes, Dishes Copy
 WHERE Basis = Copy. Basis
 AND Dish <Copy. Dish; 

or two copies of it (First and Second):

  SELECT First. Dish, Second. Dish, Basis
 FROM Dishes First, Dishes Second
 WHERE First. Basis = Second. Basis.
 AND First. Dish <Second. Dish; 

We get the result of the form