Connection of database tables (left JOIN, right join, Inner join) usage

Left JOIN, Inner join related content, very practical, for understanding the principle and specific applications are very helpful! The LEFT join is a shorthand for the left outer join, and the left join defaults to the outer property. The Inner join Inner join logical operator returns each row that satisfies the first (top) input and the second (bottom) input join. This is the same effect as querying multiple tables with Select, so it is seldom used; outer join returns the row for each join that satisfies the first (top) input and the second (bottom) input. It also returns any row in the first input that does not have a matching row in the second input. The key is the latter sentence, return more.    So the usual left join is the left outer join. First look at some of the simplest examples of table a aid adate 1 A1 2 A2 3 A3tablebbid bdate 1 B1 2 B2 4 B4 Two tables A a B is connected, to remove the field with the same ID select * from a inner joins B on a.aid = B.bid This is only the matching data is taken out.  At this time, the removal is: 1 A1 B1 2 A2 B2 so left join means: SELECT * from a LEFT join B on a.aid = B.bid First Remove all the data in the a table, and then add the data that matches a A, then take out is:  1 A1 B1 2 A2 B2 3 A3 null character Similarly, right join refers to the first to remove all the data from table B, and then add the data that matches a a, and then take out the following: 1 A1 B1 2 A2 B2 4 null character B4 left JOIN or Left OUTER JOIN. The result set of the left outer join includes all rows of the left table specified in the OUTER clause, not just the rows that match the joined columns. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values of two. The left Join/right Join/inner join Operation demo Table A is recorded as follows: AID anum 1 a20050111 2 a20050112 3 a20050 113 4 a20050114 5 a20050115 Table B is recorded as follows: BID bname 1 2006032401 2 2006032402 3 2006032403 4    2006032404 8 2006032408 The experiment is as follows: 1.             The LEFT JOIN SQL statement is as follows: SELECT * from Aleft join B on a.aid = B.bid 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) The result shows that the left join is based on the records of a table, a can be regarded as the right table, B can be regarded as the, and the left join is based on the left table. In other words, the Records of table (A) All are represented, and the right table (B) displays only records that match the search criteria (in the example: A.aid = b.bid). The low-record of table B is null.           2. Right join SQL statement is as follows: SELECT * from aright join B on a.aid = B.bid 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: Look closely, you will find that the result of the left join is just the opposite, this time is based on the right table (B), a table is not enough to fill with null. The 3.inner join SQL statement is as follows: SELECT * from A innerjoin B on a.aid = B.bid results are as follows: AID anum BID bNa             Me 1 a20050111 1 2006032401 2 a20050112 2 2006032402 3 a20050113 3 2006032403 4 a20050114 4 2006032404 result Description: It is clear that  This shows only the records of A.aid = B.bid. This shows that inner join is not based on who, it only shows records that match the criteria.

Connection to a database table (left JOIN, right join, Inner join) usage explained