MySQL left join,right Join,inner join usage and Difference

Left JOIN

will have two tables left or right in the join relationship. The left table after the "join", regardless of whether there is the right table data corresponding to the data, will still be all listed, the relevant examples are as follows:
The legacy where"join syntax represents the" left join "as"*="in the WHERE clause.

Legacy WHERE Syntax

The code is as follows	Copy Code
SELECT C.first_name, C.last_name, o.order_id from Customer as C, order as O WHERE c.customer_id *= o.order_id

This syntax has been decided by MySQL's development team in MySQL without support. They hold two reasons: one is this kind of where"left coupling" less use, if you want to use, you can use the standard? " NSI-92 's new "left join" grammar; Another reason is that MySQL development funds and related personnel development time is limited, such a less used grammar, it seems that there is no need to waste money and manpower to do.

A new JOIN grammar

The code is as follows	Copy Code
SELECT C.first_name, C.last_name, o.order_id FORM Customer as C left JOIN order as O on c.customer_id = o.customer_id

All the records in the Customer table are listed, and after the join, a null value is substituted if its record does not find a corresponding record in the order.
ANSI-92 's left JOIN compatibility syntax also has another way of expressing using using. The previous example can be rewritten as:

New JOIN Grammar Two

The code is as follows	Copy Code
SELECT C.first_name, C.last_name, o.orders_id FORM Customers as C left JOIN Orders as O USING customer_id

Use customer_id is used here instead of c.customer_id = o.customer_id. This kind of writing also can refer to see.

NATURAL left JOIN

In fact, the left join, the bad? e is all fields that conform to the join relationship are automatically selected and do not need to be annotated with a clause such as using or on, so the following example of the left join can be rewritten as:

New JOIN syntax

The code is as follows	Copy Code
SELECT C.first_name, C.last_name, o.orders_id FORM Customers as C NATURAL left JOIN Orders as O

Since both customers and orders have customer_id, the left join is automatically left after the natural left JOIN keyword. This "connection" usage is not common, so use the same as the left JOIN, only the support label? NSI-92 syntax.

Is there a right JOIN?

Almost every RDMS has the right join syntax, but MySQL does not support right join syntax, and the reason for not supporting it is simple because the right join can be achieved by swapping the left join table and then using the Leave join For example:

The code is as follows	Copy Code
SELECT c.fiirst_name from CUSTOMERS as C-left JOIN ORDERS as O on c_customer_id=o.customer_id;

Now the effect of getting right JOIN is as long as the two tables are swapped:

The code is as follows	Copy Code
SELECT c.fiirst_name from ORDERS as O-left JOIN CUSTOMERS as C on c_customer_id=o.customer_id;

So it's really not necessary to do a right join in MySQL, and even if you do it, you can reduce the efficiency of your database.

Here is an example analysis

The code is as follows

Copy Code

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:

The code is as follows	Copy Code
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:

The code is as follows	Copy Code
SELECT * from a INNER JOIN b On A.aid =b.bid

Equivalent to the following SQL sentence:

The code is as follows	Copy Code
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 joined field. 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 "<>".
An error occurs if you want to join a field that contains Memo data type or OLE Object data type data in a inner JOIN operation