Русский    English

Intersect and Except page 1

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.

  1. SELECT name FROM Ships
  3. SELECT ship FROM Outcomes;

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:

  1. SELECT ship FROM Outcomes
  3. SELECT name FROM Ships;
excluding the title. In the first case the title of the single column is name while in the second case the title is ship. Therefore the query
  1. SELECT name FROM (
  2. SELECT ship FROM Outcomes
  4. SELECT name FROM Ships
  5. ) x;
will lead to error:

Invalid column name 'name'.

Suggested exercises: 23, 38

Bookmark and Share
Pages 1 2 3 4
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
Как правильно пользоваться стеклорезом
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.