Epic face: Database de-weight

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