SELECT statement

Conventional set-theoretic operations and the SELECT statement

Within the standard of   SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL language there are clauses of SELECT statement for fulfillment of operations of intersection and subtraction of the queries. Such queries are INTERSECT  [ALL] (intersection) and EXCEPT [ALL] (subtraction), which operate analogously to UNION statement. Into the resulting set only those rows are included that are in the both queries (INTERSECT) and only those rows of the first query, that are absent in the second one (EXCEPT). Thereby both of the queries, that are involved in the operation, should be characterized by the same number of columns, and the corresponding columns should have the same (or implied) data types. The titles of the columns of the resulting set are formed from the titles of the first query.

If the key word ALL is not used, then the duplicate strings should be automatically canceled during fulfillment of the operation. If ALL is indicated, then the number of duplicate rows is subject to the following rules (n1 – the number of duplicate rows of the first query, n2 – the number of duplicate rows of the second query):

• INTERSECT ALL: min(n1, n2)
• EXCEPT ALL: n1 - n2, if n1>n2.

Example 5.7.3

Select the ships, which are included both into Ships table and into Outcomes table.

Console

Execute

SELECT name FROM Ships
INTERSECT
SELECT ship FROM Outcomes;

SELECT name FROM Ships INTERSECT SELECT ship FROM Outcomes;

    Scheme

In relational algebra intersection operation is commutative, as it is applied to relationships with identical titles. In SQL we can also change the order of the queries. The above-cited solution will give the same result  as the following:

Console

Execute

SELECT ship FROM Outcomes
INTERSECT
SELECT name FROM Ships;

SELECT ship FROM Outcomes INTERSECT SELECT name FROM Ships;

    Scheme

Console

Execute

SELECT name FROM (
SELECT ship FROM Outcomes
INTERSECT
SELECT name FROM Ships
) x;

SELECT name FROM ( SELECT ship FROM Outcomes INTERSECT SELECT name FROM Ships ) x;

    Scheme