SQL Getting Started collection operations

Although you can only process one row of data at a time while interacting with a database, the relational database typically handles a collection of data. The commonly used set operations in mathematics are: and (Union), intersection (intersect), poor (except). The following two requirements must be met for a set operation:

• Two data sets must have the same number of columns
• The data type of the corresponding column in a data set must be the same (or the server can convert one of these types to another)

Each set operator in the SQL language contains two adornments: one containing duplicates, the other removing duplicates (but not necessarily eliminating all duplicates).

0.union operator

The union and Union All operators can concatenate multiple datasets, except that the former removes duplicates and the latter remains. Theunion All operation is more rapid for the database because the database does not need to check the repeatability of the data. For even a table

Select ' IND ' type_cd, cust_id, lname name  from individual Union  All Select ' BUS ' type_cd, cust_id, name  from business;

The result after performing the operation is:

Of these, 18 data came from the individual table and 8 from business. To see the difference more clearly, look at the following two examples:

Select a.cust_id, A.name  from  allselect  b.cust_id, b.name from business b;

Result is

and

Select a.cust_id, A.name  from Business a Union Select b.cust_id, B.name  from business b;

Result is

The difference can be clearly seen.

1.intersect and except Operators

The MySQL version I used does not seem to implement these two features. ：（

SQL Getting Started collection operations