Separation of complex commands

The Update, Delete and partially Insert commands are not atomic: they are based on subcommands. First, to modify or delete tuples in a table, you first need to find the table and tuples that need to be modified or deleted. And secondly, a tuple (record) in a table includes a full set of fields, while a change or add operation can specify a limited number of fields. This is different from objects in programming, which contain all the properties of a class at once. When tuples are added to a table, fields that are not explicitly specified take on a default value or take on an empty value (null).

It is not the content of these commands that is questionable, but the form in which they are given. The following chapters will show examples that would be more convenient for developers and analysts than current established practice.

Using data sources

Addressing data in databases occurs through specifying the data source (from). At the elementary or most basic level, this is specifying the name of the table. In a more complex case, it can be replaced with a subquery – an expression enclosed in brackets: (select field1, field2 from Table1). However, to apply direct changes, such as deleting or changing data, you must specify a table rather than a calculated expression (selection).

A possible subcommand that follows the data source selection is the join command. The join command can have many different variants, which are defined both syntactically and descriptively. The syntax stands out:

• The Cartesian Product is expressed by the cross join command or by sequentially listing data sources in the from construct.
• inner join (inner join or join)
• left outer join (outer left join or left join)
• right outer join (outer right join or right join)
• full outer join (outer full join or full join)

The join command includes 2 operands that indicate data sources (TableA join TableB). A join adds a new data source, so a combination of join commands can form a tree of tables. In addition to specifying the table itself as the data source, a selection expression (subcommands (Select)) can be used as the data source.

Remark: here and below, using the notation join, we mean the entire range of join operations, including cross-join (Cartesian product), internal join (join itself), left, right, full join, as well as semi-joins and anti-joins (left and right remainder).

Selection

If additional filters are not specified in relation to the table, then selecting, changing or deleting records applies to all tuples in the table. Additional refinements are specified through the selection subcommand. The selection command is an addition to the subcommand specifying data sources. In SQL, a selection subcommand is described using the where keyword.

In addition, the change and delete commands also have the ability to perform selection through joins: natural, left, right, full. After all, the join also limits the rows that can be selected from the main table. This is achieved by specifying the table in the query.

A simple construction is a construction with selection:

delete [from] Table1
where FieldA=’Name’

Complex construction using join:

delete a
from Table1 a
join Table2 b on a.FieldA=b.FieldA
where a.FieldB=’John’

The examples show that the basis for the adding (Insert), changing (Update) and deleting (Delete) commands is the data retrieval operation – that is, constructions similar to those used in the “Select” command, with some exceptions. The exception is grouping (aggregation) operations – they are used in the selection, but are not used when updating or deleting data, despite the fact that grouping is essentially similar to a filter.

When changing data and when deleting data, the deleted and modified table tuples must be identified, that is, found among the table tuples in the database.

There is no specific sequence of tuples within a table, so addressing through an index (order) is not possible – that is, when updating and deleting, it is impossible to specify a change in one or more records in order, similar to how it is done in a select operation. Unique identification of an element in a table occurs by specifying the specific value of the fields of the primary or alternative keys.