MySQL leftist right-side inline

In MySQL because of the performance of the relationship, often to the subquery (sub-queries) with the connection (join) but instead of it, Better use of indexes in tables to improve query efficiency.

The following describes the use of various joins , first:

Our MySQL is commonly used for leftjoin, right join,and inner join (inner Join) Other, the rest of the full join we MySQL does not support, available with left and right connections and UNION Alternative (examples below).

1, first set up the test table two:

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)

) ;

2. Insert test data:

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 two table data are as follows:

Mysql> select * from A;

+-----+-----------+

| AID | Anum |

+-----+-----------+

| 1 | a20050111 |

| 2 | a20050112 |

| 3 | a20050113 |

| 4 | a20050114 |

| 5 | a20050115 |

+-----+-----------+

Mysql> SELECT * from B;

+-----+------------+

| BID | bname |

+-----+------------+

| 1 | 2006032401 |

| 2 | 2006032402 |

| 3 | 2006032403 |

| 4 | 2006032404 |

| 8 | 2006032408 |

+-----+------------+

The experiment is as follows :

1.left Join ( left join )

The SQL statements are 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)

Result description :

The left join is based on the record of table a , which can be seen as the right table and B as the Join is based on left table .

In other words , the records of 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).

the low-record of table B is NULL.

2.right Join ( right join )

The SQL statements are 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)

Result description :

Looking closely , you will find that The result of the left join is exactly the opposite , this time with the right table (B) -based , A where the table is insufficient NULL Fill . 

3.inner Join ( equal join or inner join )

The SQL statements are as follows :

SELECT * from a

INNER JOIN b

On A.aid =b.bid

Equivalent to the following SQL sentence :

SELECT *

From a 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

Result description :

Obviously , only the records of A.aid = B.bid are shown here . This means INNER JOIN not based on who? , It only shows records that match the criteria to the direct where condition of the two-table joint investigation . 

4.full Join ( full join )

MySQL does not support the need to use the left and right connections and the UNION method substitution, the effect is to obtain a table and b table Data Union.

Mysql> SELECT * from a LEFT join B on a.aid = b.bid UNION ALL SELECT * from a RIGHT join B on a.aid = B.bid;

+------+-----------+------+------------+

| AID | Anum | BID | bname |

+------+-----------+------+------------+

| 1 |    a20050111 | 1 | 2006032401 |

| 2 |    a20050112 | 2 | 2006032402 |

| 3 |    a20050113 | 3 | 2006032403 |

| 4 |    a20050114 | 4 | 2006032404 |

| 5 | a20050115 | NULL | NULL |

| 1 |    a20050111 | 1 | 2006032401 |

| 2 |    a20050112 | 2 | 2006032402 |

| 3 |    a20050113 | 3 | 2006032403 |

| 4 |    a20050114 | 4 | 2006032404 |

| NULL |    NULL | 8 | 2006032408 |

+------+-----------+------+------------+

If you want to find the difference between a and b , the lower right corner of the table shows that you need to add the conditions separately.

Mysql> SELECT * from a LEFT join B on a.aid = b.bid where b.bid are null UNION ALL SELECT * from a RIGHT join B on A.A ID = b.bid where a.aid is null;

+------+-----------+------+------------+

| AID | Anum | BID | bname |

+------+-----------+------+------------+

| 5 | a20050115 | NULL | NULL |

| NULL |    NULL | 8 | 2006032408 |

+------+-----------+------+------------+

MySQL leftist right-side inline