Mysql combined table expression union, uniondistinct, and unionall

1union introduction UNION is a set operator (setoperator) that allows us to combine multiple table expressions into a composite table expression. It puts the result of one table expression under another table expression, the UNION and UNIONALL keywords are provided in the mysql database. The columns selected at the corresponding position of each SELECT statement should have the same class.

1 union introduction UNION is a set operator that allows us to combine multiple table expressions into a composite table expression. It puts the results of one table expression under another table expression, the UNION and UNIONALL keywords are provided in the mysql database. The columns selected at the corresponding position of each SELECT statement should have the same class.

1 union Introduction
UNION is a set operator that allows us to combine multiple table expressions into a composite table expression. It puts the results of one table expression under another table expression, the UNION and union all keywords are provided in the mysql database. The selected columns at the corresponding position of each SELECT statement should have the same type. The column name used in the first SELECT statement is also the name of the Column Used for the result.
If UNION does not use the keyword ALL, ALL returned rows are unique, as if DISTINCT has been used for the entire result set. If ALL is specified, ALL matched rows are obtained from ALL used SELECT statements. The DISTINCT keyword is a self-selected word and does not play any role, but can be used in the syntax according to the requirements of the SQL standard. You can also mix union all and union distinct in the same query. The mixed UNION type is treated in this way, that is, the DISTICT shared body overwrites ALL the shared bodies located on the left. The DISTINCT shared body can be explicitly generated using union distinct, or implicitly generated using UNION (without the DISTINCT or ALL keyword.
2 union syntax

SELECT column, ...FROM table1UNION [ALL | DISTINCT]SELECT column, ...FROM table2[UNION [ALL | DISTINCT]SELECT column, ...FROM table3]

Union:It is used to merge the result sets of two or more SELECT statements and eliminate any duplicate rows in the table.
Union distinct:Same as union
Union all:It is used to merge the result sets of two or more SELECT statements and retain duplicate rows in the table.

3. Use union rules

3.1 The select statement of all related table expressions must have the same number of expressions, and the table expressions under a table expression must be of the same or comparable data type. Note: If the two data types are the same, or if the expression can be converted to the same data type through an implicit condition, they can be compared.
3.2 for data from multiple tables, the names of the columns retrieved from multiple SQL statements may be inconsistent, but the names of the final columns shall be the names of the first SQL statement.
3.3 order by can only be specified after the last table expression. After all intermediate results are combined, the sorting is executed on the entire final result.
3.4 The select clause does not need to contain distinct (but is allowed) because mysql automatically removes duplicate rows from the final result when union is used.
4. Create a test table and initialize the data.

4.1 create a test table

CREATE   TABLE PLAYERS        (PLAYERNO       INTEGER      NOT NULL,         NAME           CHAR(15)     NOT NULL,         INITIALS       CHAR(3)      NOT NULL,         BIRTH_DATE     DATE                 ,         SEX            CHAR(1)      NOT NULL,         JOINED         SMALLINT     NOT NULL,         STREET         VARCHAR(30)  NOT NULL,         HOUSENO        CHAR(4)              ,         POSTCODE       CHAR(6)              ,         TOWN           VARCHAR(30)  NOT NULL,         PHONENO        CHAR(13)             ,         LEAGUENO       CHAR(4)              ,         PRIMARY KEY    (PLAYERNO));CREATE   TABLE PENALTIES        (PAYMENTNO      INTEGER      NOT NULL,         PLAYERNO       INTEGER      NOT NULL,         PAYMENT_DATE   DATE         NOT NULL,         AMOUNT         DECIMAL(7,2) NOT NULL,         PRIMARY KEY    (PAYMENTNO));

Note: PLAYERS indicates the player information table, and PENALTIES indicates the player penalty record table.

4.2 Insert Test Data

INSERT INTO PLAYERS VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');INSERT INTO PLAYERS VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');INSERT INTO PLAYERS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL);INSERT INTO PLAYERS VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');INSERT INTO PLAYERS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513');INSERT INTO PLAYERS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);INSERT INTO PLAYERS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);INSERT INTO PLAYERS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');INSERT INTO PLAYERS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458', '6409');INSERT INTO PLAYERS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratford', '070-353548', '1608');INSERT INTO PLAYERS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);INSERT INTO PLAYERS VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');INSERT INTO PLAYERS VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');INSERT INTO PLAYERS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');INSERT INTO PENALTIES VALUES (1,  6, '1980-12-08',100);INSERT INTO PENALTIES VALUES (2, 44, '1981-05-05', 75);INSERT INTO PENALTIES VALUES (3, 27, '1983-09-10',100);INSERT INTO PENALTIES VALUES (4,104, '1984-12-08', 50);INSERT INTO PENALTIES VALUES (5, 44, '1980-12-08', 25);INSERT INTO PENALTIES VALUES (6,  8, '1980-12-08', 25);INSERT INTO PENALTIES VALUES (7, 44, '1982-12-30', 30);INSERT INTO PENALTIES VALUES (8, 27, '1984-11-12', 75);

5. instance analysis

5.1 union

Obtain the numbers and cities of each player from Inglewood and from Plymouth.

select playerno, town from PLAYERS where town = 'Inglewood' union select playerno, town from PLAYERS where town = 'Plymouth';

If the two tables left and right of union are the same table, they can be replaced by or. For different tables, union can only be used. The following two examples:

The two tables are the same.

select playerno, town from PLAYERS where town = 'Inglewood' ortown = 'Plymouth';

The two tables are different.

select birth_date as dates from PLAYERS union select payment_date from PENALTIES;

5.2 union all

The only difference between union all and union is that when we use union to automatically remove duplicate rows and use union all, duplicate rows are retained.

select joined from PLAYERS where town = 'Stratford' union all select joined from PLAYERS where town = 'Inglewood';

6. Notes

6.1 efficiency of union and union all

In terms of efficiency, union all is much faster than UNION. Union all simply merges two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data. UNION filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results. If you can confirm that the merged two result sets do not contain duplicate data, use union all.

**************************************** **************************************** ********
Http://blog.csdn.net/jesseyoung/article/details/40427849
Blog homepage: http://blog.csdn.net/jesseyoung
**************************************** **************************************** ********