SQL set arithmetic difference set Union set intersection

Document directory

• Difference set limit t:
• Intersection intersect:
• Union:

SQL-3The standard provides three commands for set operations on the search results: Union, intersect, and minus ). In some databases, This is not fully supported, for exampleMySQLThere is only union, and there are no other two types. In fact, these operations can be implemented through Common SQL, although sometimes cumbersome.

Assume that two tables (or views) S, T, and s have two fields SA and Sb; t has two fields TA and TB;

Difference set limit t: Plain text SQL:

1. Selectsafroms
2. Except
3. Selecttafromt;

Able to write

Plain text SQL:

1. Selectsafroms
2. Wheresanotin
3. (Selecttafromt)

In the above example, the separate conditions for S and T are ignored. These conditions can be added to and, or the view can be used. It is troublesome to use multiple fields, for example:

Plain text SQL:

1. Selectsa, sbfroms
2. Except
3. Selectta, tbfromt;

To be written

Plain text SQL:

1. Selectsa, sbfroms
2. Where (SA, Sb) notin
3. (Selectta, tbfromt)

 

The syntax used above is not supported by the database. Fortunately, MySQL that does not support t supports this syntax, while MSSQL that does not support this syntax supports.

Note that such a row Constructors (MySQL term) is not equivalent to the following statement (and other similar statements.

Plain text SQL:

1. Selectsa, sbfroms
2. Wheresanotin
3. (Selecttafromt)
4. Andsbnotin
5. (Selecttbfromt)

One solution in MSSQL is to combine the two fields (assuming the character type), that is

Plain text SQL:

1. Selectsa, sbfroms
2. Wheresa + sbnotin
3. (Selectta + tbfromt)

 

Intersection intersect: Plain text SQL:

1. Selectsafroms
2. Intersect
3. Selecttafromt;

Can be written

Plain text SQL:

1. Selectsafroms
2. Wheresa in
3. (Selecttafromt)

Of course, it can also be written

Plain text SQL:

1. Selectsafroms
2. Whereexists
3. (Select * fromtwheret. Ta = S. SA)

Or use the connection

Plain text SQL:

1. Selectsafroms, T
2. Wheresa = Ta

 

In fact, these statements have some problems, that is, the semantics of intersect in the case of repetition. According to SQL-3 standards, similar to union, there can be clear intersect all or intersect distinct syntax. The general intersect implementation does not clarify this point, and it does not make much logical sense. If there are duplicates in S or T, for example, SA = 'X' has two, SB = 'X' has three, the preceding subquery returns two rows and six rows. Of course, you can add distinct to both statements to implement the Intersect distinct semantics.

Union:

MySQL supports Union (all and distinct) from 4.0. for completeness, let's also list it.
In fact, implementing such a result is very troublesome.

Plain text SQL:

1. Selectsafroms
2. Uniondistinct
3. Selecttafromt;

External connection is required, and the connection is full.

Plain text SQL:

1. Selectdistinctnvl (S. SA, T. Ta)
2. Froms fullouterjointon (S. SA = T. Ta)

 

In the preceding example, I used the Oracle syntax. In fact, MySQL does not support full outer join (although left and right outer join are supported). Fortunately, MySQL supports union.

For the Union all semantics, I have not figured out how to implement it using a common query. If distinct is removed from the preceding statement, the result is definitely incorrect.