Epic face: Database de-weight

Source: Internet
Author: User
Tags joins

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

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.