SQL UNION ALL syntax
Tables Name: Table
Fields: ID username ytime
The results to be achieved are:
Query 1:
Select Top 5 * from table where ID>20 ORDER by ID ASC
Query 2:
Select Top 5 * from table where id<20 the ORDER by id DESC
And then query 1 and Query 2 of the sum, followed by the reverse ID. Originally thought: Query 2 Good To do, originally on the reverse, and are less than 20, so this does not need to change. Now it's time to sort the query 1 again.
So I wrote an SQL statement:
SELECT * FROM (SELECT Top 5 * to table where ID>20 ORDER by ID ASC) ORDER BY ID DESC
UNION ALL
Select Top 5 * from table where ID>20 ORDER by ID ASC
The result of the run is actually: Id>20 's 5 records have no reverse.
It has to be written in a different way:
SELECT * FROM (SELECT Top 5 * from table where ID>20 ORDER by ID ASC)
UNION ALL
Select Top 5 * "from table where ID>20 ORDER by ID ASC" ORDER BY ID DESC
Detailed description
Union
The Union command is used to select related information from two tables, much like the join command. However, when you use the Union command, the data types of all the selected columns should be the same.
Note: If you use union, only a different value will be selected.
SQL statement 1 Union SQL statement 2
The original table used in the following example:
Employees_china:
e_id E_name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Employees_usa:
e_id E_name
Adams, John
Bush, George
Carter, Thomas
Gates, Bill.
Using the Union command
Instance
List all the different employee names in China and the United States:
Select E_name from Employees_china Union select E_name from Employees_usa
Results
E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Gates, Bill.
Note: This command does not list all employees in China and the United States. In the example above, we have two employees with the same name, and only one of them is listed. The Union command selects only a different value.
UNION ALL
The Union ALL command and the Union command are almost equivalent, but the Union ALL command lists all values.
SQL statement 1 UNION ALL SQL statement 2
Using the UNION ALL command
Instance:
List all employees in China and the United States:
Select E_name from Employees_china UNION ALL select E_name from Employees_usa
result
E_name
Zhang, Hua
wa Ng, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill