[NET Abstract] simple examples of SQL Union and SQL Union All

SQL Union and SQL Union All usage

The purpose of the UNION command is to combine the results of two SQL statements. From this perspective, UNION and JOIN are somewhat similar, because both commands can retrieve data from multiple tables. One Limit of UNION is that the columns generated by two SQL statements must be of the same data type. In addition, when we use the UNION command, we only see different data values (similar to select distinct ). Union only concatenates two results and displays them together. It does not join two tables ............ The syntax of UNION is as follows: [SQL statement 1]

UNION

[SQL statement 2] Suppose we have the following two tables,

Store_Information table Store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999

Internet Sales table Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

We need to find out all the days with a turnover. To achieve this goal, we use the following SQL statement: SELECT Date FROM Store_Information

UNION

SELECT Date FROM Internet_Sales result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

It is worth noting that if we use "select distinct Date" in any SQL statement (or both sentences), we will get the same result.

SQL Union All

The purpose of the union all command is to combine the results of two SQL statements. The difference between union all and UNION is that union all lists each qualified data, regardless of whether the data value is repeated. The syntax of union all is as follows: [SQL statement 1]

UNION ALL

[SQL statement 2] we use the same example on the previous page to show the differences between UNION ALL and UNION. Let's assume that we have the following two tables,

Store_Information table Store_name Sales Date Los Angeles $1500 Jan-05-1999 San Diego $250 Jan-07-1999 Los Angeles $300 Jan-08-1999 Boston $700 Jan-08-1999

Internet Sales table Date Sales Jan-07-1999 $250 Jan-10-1999 $535 Jan-11-1999 $320 Jan-12-1999 $750

However, we need to find out the days when there are store turnover and network turnover. To achieve this goal, we use the following SQL statement: SELECT Date FROM Store_Information

UNION ALL

SELECT Date FROM Internet_Sales result:

Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

========================================================== ============================================

Table 1

A B

A 1

B 0

C 3

D 0

E 2

Table 2

A B

C 0

E 4

Merge the two tables to remove duplicate data (dominated by table 2) and obtain the following table:

A B

A 1

B 0

C 0

D 0

E 4

Select A, B from table 1 where A not in (select A from table 2)

Union

Select A, B from table 2

Bytes ------------------------------------------------------------------------------------------

 

Select * from table1 inner join table2 on table1.id = table2.id

Select * from table1, table2
Where table1.id = table2.id

The execution results of the two methods are the same. What are their specific differences?

 

 

 

1. Connection statements used in the WHERE clause are called implicit connections in the database language. Inner join ...... The connection generated by the ON clause is called an explicit connection. (Other JOIN parameters are also explicit connections.) There is no essential difference between the connection relationship between WHERE and inner join, and the result is the same. But! Recessive connections have been gradually eliminated with the standardization and development of the database language, and the new database language has basically abandoned recessive connections, all of which adopt explicit connections.

2. join clauses can be used no matter how they are connected. When connecting to the same table, be sure to define aliases. Otherwise, an error is returned!

A> inner join: It is understood as "valid join". Only the data in both tables shows left join: It is understood as "with left display", for example, on. field = B. field to display all the data in Table a and all the data in Table a \ B. data in Table A and in Table B is displayed as null.

B> right join: it is interpreted as "right display", for example, on. field = B. field, all data in Table B and data in a \ B are displayed. data in Table B and in Table A is displayed as null.

C> full join: it is interpreted as "full join". All data in the two tables is displayed, which is actually inner + (left-inner) + (right-inner)

3. join can be divided into three types: Full outer join, left join, and right join.
Full outer join contains all records of the two tables.
The left join is based on the table on the left, supplemented by the right join, and the right join is opposite.