MySQL Inner join


SQL (MySQL) JOIN is used to derive data from these tables based on the relationships between the fields in two or more tables.

JOIN is usually used with the ON keyword, the basic syntax is as follows:

... FROM table1 INNER | LEFT | RIGHT JOIN table2 ON conditiona

table1 is usually called the left table, table2 is called the right table. The ON keyword is used to set the matching conditions that define which rows you want in the result set. If you need to specify other conditions, can be followed by WHERE conditions or LIMIT to limit the number of records returned.

The following two tables to connect the most common to illustrate the use of MySQL JOIN, multi-table JOIN see " MySQL JOIN multi-table ."


classification

JOIN by function can be divided into the following three categories:

  • INNER JOIN (internal connection ): access to the two tables exist in the matching relationship between the records.
  • LEFT JOIN (left connection ): to obtain the left table (table1) complete record, that is, the right table (table2) does not match the corresponding record.
  • RIGHT JOIN (right join): In contrast to LEFT JOIN, get the right table (table2) complete record, that is, the left table (table1) does not match the corresponding record.

MySQL FULL JOIN (full connection)

MySQL does not provide SQL standard FULL JOIN (full connection): two table records are removed, regardless of whether there is a corresponding record. To solve this problem, you can use the UNION keyword to merge LEFT JOIN and RIGHT JOIN, to simulate the purpose of FULL JOIN.


MySQL INNER JOIN

INNER JOIN used to obtain the two tables exist in the matching relationship between the records. Here are the two raw data tables:

Article table :

id title content uid
1 Article 1 Article 1 Body Content ... 1
2 Article 2 Article 2 Body Content ... 1
3 Article 3 Article 3 Body Content ... 2

User table:

uid username password email
1 admin b7e591c246d010bb2ccd77d52490c85e admin@gmail.com
2 John a193686a53e4de85ee3f2ff0576adf01 john@gmail.com
3 Jack 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com

The user of the article table is associated with the user table through the uid field. Through observation data is not difficult to find, for uid = 3 users, and did not publish any article; the article id = 4 can not find the corresponding record in the uid table (the user may be deleted and their articles were retained Down).

We list the articles that correspond to the user's data.

SELECT ... INNER JOIN ... ON statement is as follows :

SELECT article.id, article.title, user.username FROM article 
INNER JOIN user ON article.uid = user.uid

The results are as follows:

id title username
1 Article 1 Admin
2 Article 2 Admin
3 Article 3 John

For INNER JOIN, equivalent to the following SQL statement:

SELECT article.id, article.title, user.username FROM article, user WHERE article.uid = user.uid



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook