MySQL connection left join,right Join,inner Join statement usage

In the talk of MySQL before the join grammar or the first review of the coupling of the grammar, oh, in fact, even I have forgotten almost, then we go over it together (if the content has errors or doubt, the domestic information on the MySQL connection is very small, I believe that after reading this article, we will have a fairly clear understanding of the MySQL connection syntax, and will not be confused by the external connection of Oracle ("+").
The (join) connection that is planned in the SQL standard is roughly divided into the following four kinds: PHP open source

1. INNER JOIN: A join of a recordset that conforms to those records in which the join relationship exists in the two tables.

2. Outer coupling: It is divided into outer left coupling and outer right connection. PHP Open Source

The left-bound table A and B means the connection of the records formed by the records of all the records in table A and the joined fields in table B to those recorded by the join of table A, and note that the final set of records includes all records of Table A.

The result of the right connection A and B table is the same as the result of the left coupling B and a, which means:

Select A.name B.name from the left of Join B on A.id=b.id

And the result of the execution of the select A.name b.name from B right Join A is the same.

3. Full join: Takes out all records of a joined field in two tables to form a recordset's join (this does not require memory, as long as the fields of the table mentioned in the query are taken out, regardless of whether the join conditions are met, and therefore not significant).

4. No connection: No need to explain, that is, there is no use of the connection function Bai, there are also the theory of self-connection.

Here I have a simpler method of memorizing, the difference between internal and external connections is that the inner joins will remove all records that are not eligible, and the outer joins retain the parts. The difference between the outer left and the outer right is that if all the records in a LEFT join B are retained in the result, then only the records in B that match the join condition, and the right join is the opposite, so it will not be confused. In fact, we recall that in the book "Introduction to Database Systems" published by Higher Education publishers, it is not difficult to understand the content of the Relationship algebra chapter (that is, the chapter of Cartesian product and projection).

MySQL left-side join:

A simple connection and a MySQL left connection are different. A MySQL left join provides extra consideration to the table on the left-hand side.

If you do a LEFT join, all of the records that you get are matched in the same way, and you get an extra record of each mismatched record, a join in the left-hand table-thus ensuring that each author is associated (in this example):

Instance:

Try the following example to understand the left JOIN:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
Mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count
-> from Tutorials_tbl a left JOIN tcount_tbl b
-> on a.tutorial_author = B.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | Tutorial_author | Tutorial_count |
+-------------+-----------------+----------------+
| 1 |              John Poul | 1 |
| 2 |           Abdul S | NULL |
| 3 |              Sanjay | 1 |
+-------------+-----------------+----------------+
3 Rows in Set (0.02 sec)

More practice is needed to become familiar with joins. This is a complex concept that will become clearer in the mysql/sql.

MySQL fully connected join full

To use a single SQL query from multiple tables. The join behavior in MySQL means that two or more tables into a table can use joins to join the MySQL table in the Select,update and DELETE statements. We'll see an example of a left join with a simple MySQL connection.

To use joins at the command prompt:
Suppose the complete list of our two-table tutorials Tcount_tbl and Tutorials_tbl is as follows:

Example:
Try the following example:

root@host# mysql-u root-p password;
Enter password:*******
mysql> use tutorials;
Database changed
Mysql> SELECT * from TCOUNT_TBL;
+-----------------+----------------+
| Tutorial_author | Tutorial_count |
+-----------------+----------------+
|             Mahran | 20 |
|           Mahnaz | NULL |
|           Jen | NULL |
|             Gill | 20 |
|              John Poul | 1 |
|              Sanjay | 1 |
+-----------------+----------------+
6 rows in Set (0.01 sec)
Mysql> SELECT * from TUTORIALS_TBL;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | Tutorial_title | Tutorial_author | Submission_date |
+-------------+----------------+-----------------+-----------------+
| 1 | Learn PHP | John Poul | 2007-05-24 |
| 2 | Learn MySQL | Abdul S | 2007-05-24 |
| 3 | JAVA Tutorial | Sanjay | 2007-05-06 |
+-------------+----------------+-----------------+-----------------+
3 Rows in Set (0.00 sec)
Mysql>
Now, by joining these two tables, we can write a SQL query. This query selects all the authors from the table TUTORIALS_TBL and will pick up the corresponding number of tutorials from TCOUNT_TBL.

Mysql> SELECT a.tutorial_id, A.tutorial_author, B.tutorial_count
-> from Tutorials_tbl A, tcount_tbl b
-> WHERE a.tutorial_author = B.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | Tutorial_author | Tutorial_count |
+-------------+-----------------+----------------+
| 1 |              John Poul | 1 |
| 3 |              Sanjay | 1 |
+-------------+-----------------+----------------+
2 rows in Set (0.01 sec)
Mysql>

====================================== added an article ================================

Here is an example analysis
Table A records as follows:
AID Anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

Table B is recorded as follows:
BID bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

Create the two tables SQL statements as follows:
CREATE TABLE A
AID Int (1) auto_increment PRIMARY KEY,
Anum Char (20)
)
CREATE TABLE B (
BID Int (1) not NULL auto_increment PRIMARY KEY,
Bname Char (20)
)

INSERT into a
VALUES (1, ' a20050111 '), (2, ' a20050112 '), (3, ' a20050113 '), (4, ' a20050114 '), (5, ' a20050115 ');

INSERT into B
VALUES (1, ' 2006032401 '), (2, ' 2006032402 '), (3, ' 2006032403 '), (4, ' 2006032404 '), (8, ' 2006032408 ');

The experiment is as follows:
1.left join (left join)

The SQL statement is as follows:
SELECT * from a
Left JOIN b
On A.aid =b.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
(The number of rows affected is 5 rows)

Results show:
The left join is based on the records of a table, and a can be viewed as the left-hand table, B can be viewed as the right table, and left table.
In other words, the records in the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).
b tables where the records are insufficient are null.

2.right join (right join)

The SQL statement is as follows:
SELECT * from a
Right joing b
On a.aid = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
(The number of rows affected is 5 rows)

Results show:
With a closer look, you will find that the result of the left join is just the opposite, this time based on the right table (B), where a table is deficient with null padding.

3.inner Join (equal join or inner join)

The SQL statement is as follows:
SELECT * from a
INNER JOIN b
On A.aid =b.bid

Equivalent to the following SQL sentence:
SELECT *
From A,b
WHERE A.aid = B.bid

The results are as follows:
AID Anum BID bname
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

Results show:
Obviously, only the record of A.aid = B.bid is shown here. This shows that inner join is not based on who, it only shows the records that match the criteria.
The left JOIN operation is used in any FROM clause,

The record of the combined source table. Use the left JOIN operation to create a left-hand outer join. The left outer join will contain all the records in the two tables starting at the first (left), i.e.
Make a record in the second (right) table that does not have a matching value.

Syntax: from table1 left JOIN table2 on table1.field1 compopr table2.field2
Description: table1, the table2 parameter is used to specify the name of the table to combine records with.
Field1, the Field2 parameter specifies the name of the field being joined. And these fields must have the same data type and contain the same type of data, but they do not need to have the same
Name.
The COMPOPR parameter specifies the relational comparison operator: "=", "<", ">", "<=", ">=" or "<>".
If you want to join a field that contains Memo data type or OLE Object data type data in a inner JOIN operation, an error occurs.