Union/Union all usage

Source: Internet
Author: User

Please refer to the question of the next Query
I have two tables.

There are records in tab1:
Aid bid
AS1 19
As2 19
As3 23
As4 45
There are records in tab2:
Aid bid
As2 19
As3 19
As4 19

Now we need to find the records with bid = 19 in the two tables, which should be 5 Records.
The method I used is:
Method 1: Select tab1.aid, tab2.aid from tab1, tab2 where tab1.bid = tab2.bid and tab2.bid = 19
Method 2: Select tab1.aid, tab2.aid from tab1 join tab2 on tab1.bid = tab2.bid where tab1.bid = 19
There are six records selected by the above two methods. Why is this problem?
Select * Form tab1 where Bid = 19
Union all
Select * Form tab2 where Bid = 19
Create Table tab1
(Aid char (10 ),
Bid char (10 ))
Insert into tab1 (aid, bid) values ('as1', '19 ')
Insert tab1 values ('as2', '19 ')
Insert tab1 values ('as3', '38 ')
Insert tab1 values ('as4', '45 ')
Create Table tab2
(Aid char (10 ),
Bid char (10 ))
Insert tab2 values ('as2', '19 ')
Insert tab2 values ('as3', '19 ')
Insert tab2 values ('as4', '19 ')
Select * From tab1
Select * From tab1 where tab1.bid = '19' Union all select * From tab2 where tab2.bid = '19'
Bamboga's words:
1. Join produces cartesian products.
2. Union will exclude Repeated Records
3. Union all does not exclude Repeated Records


Union clause
The Union operator combines two query results into a result set. When two tables are merged for connection query: The number and sequence of columns must be consistent during query; data types are compatible.
Select statement
Union [all]
Select statement
1. The union operation deletes duplicate records from the final result set. If you want to delete duplicate records, use the all keyword.
2. The order by or compute clause cannot be used in the first select statement. It can only be used after the last select clause

For example, if the students of the computer system are not older than 19 years old, they are sorted by age.
Select * from department where dept = 'computer ';
Select * from student where age <= 19
Order by age DESC

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.