1.How does I merge both tables in Access while removing duplicates?
Ref: http://stackoverflow.com/questions/7615587/how-do-i-merge-two-tables-in-access-while-removing-duplicates
Here are the experimental results:
A UNION query returns only distinct rows. (There is also UNION all, but this would include duplicate rows, so you don ' t want it's here.)
1Mysql>Select* fromPersons2; +-----------+2 3| FirstName |4 5+-----------+6 7| Zelin |8 9| Qihao |Ten One+-----------+ A - 2Rowsinch Set(0.00sec) - the - -Mysql>Select* frompersons; - ++-----------+ - +| FirstName | A at+-----------+ - -| Yu | - -| Zhixu | - in| Zelin | - to+-----------+ + - 3Rowsinch Set(0.00sec) the * $ Panax NotoginsengMysql> - theMysql>Select* fromPersons UnionSelect* frompersons2; + A+-----------+ the +| FirstName | - $+-----------+ $ -| Yu | - the| Zhixu | - Wuyi| Zelin | the -| Qihao | Wu -+-----------+ About $ 4Rowsinch Set(0.00Sec
View Code
2. Join
By the way, several db commonly used merge statements are introduced:
Http://www.w3schools.com/sql/sql_join.asp
An SQL JOIN clause are used to combine rows from the or more tables, and based on a common field between them.
The most common type of join Is:sql INNER join (simple join). An SQL INNER join return all rows from multiple tables where the join condition is met.
Let's look at a selection from the "Orders" table:
OrderID |
CustomerID |
OrderDate |
10308 |
2 |
1996-09-18 |
10309 |
37 |
1996-09-19 |
10310 |
77 |
1996-09-20 |
Then, there is a look at a selection from the "Customers" table:
CustomerID |
CustomerName |
ContactName |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mexico |
Notice that the ' CustomerID ' column in the ' Orders ' table refers to the ' CustomerID ' in the ' Customers ' table. The relationship between the tables above is the "CustomerID" column.
Then, if you run the following SQL statement (that is contains an INNER JOIN):
Exampleselect Orders.OrderID, Customers.customername, orders.orderdate
From Orders
INNER JOIN Customers
On Orders.customerid=customers.customerid;
Try it yourself»
It'll produce something like this:
OrderID |
CustomerName |
OrderDate |
10308 |
Ana Trujillo Emparedados y helados |
9/18/1996 |
10365 |
Antonio Moreno Taquería |
11/27/1996 |
10383 |
Around the Horn |
12/16/1996 |
10355 |
Around the Horn |
11/15/1996 |
10278 |
Berglunds Snabbköp |
8/12/1996 |
Different SQL JOINs
Before we continue with examples, we'll list the types the different SQL JOINs you can use:
- INNER join:returns All rows when there are at least one match in BOTH tables
- Left Join:return all rows from the left table, and the matched rows from the right table
- Right Join:return all rows from the right table, and the matched rows from the left table
- Full Join:return All rows when there are a match in one of the tables
3. Full Join
There is no full join statement in MySQL, we need to use Union:
Mysql> SELECT * from persons left JOIN persons2 on Persons.firstname=persons2.firstname UNION SELECT * from Persons RIG HT JOIN persons2 on Persons.firstname=persons2.firstname;
+-----------+-----------+
| FirstName | FirstName |
+-----------+-----------+
| Zelin | Zelin |
| Yu | NULL |
| Zhixu | NULL |
| NULL | Qihao |
+-----------+-----------+
4 rows in Set (0.00 sec)
Epic face: Database de-weight