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 ."
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 :
|1||Article 1||Article 1 Body Content ...||1|
|2||Article 2||Article 2 Body Content ...||1|
|3||Article 3||Article 3 Body Content ...||2|
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:
For INNER JOIN, equivalent to the following SQL statement:
SELECT article.id, article.title, user.username FROM article, user WHERE article.uid = user.uid