Usage of SQL Join

Background: (The lesson of blood)

Very grateful to be fortunate enough to go to the vitality of the century interview, the interviewer is very peaceful, although the last not able to go into, but very grateful, is he let me understand that there is a lot of people need to learn, every interview is not to prove how strong they are, can take how much salary, but to test their own areas and deficiencies, Need to work hard, refueling, in order to better themselves ... Before in the company's code to see something about the left join, but did not pay much attention to these, just when the interview asked these, now just have time, by the way to learn, to strengthen themselves ....

About the connection (join) keyword in SQL statements, is a more common and not easy to understand the keyword, nonsense will not say much, directly into the topic, case:

You can refer to the blog: http://www.cnblogs.com/yiki/archive/2007/01/04/611452.html

I. Establishment of the table

new Table One table1 new Table Two table2

One, outer connection
1. Concept: Includes a LEFT outer join, a right outer join, or a full outer join

2. Left-side connection: outer JOIN
(1) 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 (NULL).
(2) SQL statements

Select *  from  Left Join  on table1.id=table2.id

-------------Results-------------

NOTE: All clauses that contain table1, return table2 corresponding fields according to specified criteria, non-conforming null to display filters that do not use where as criteria oh, or it will be an error.

3. Right Join
(1) A right outer join is a reverse join of a left outer join. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.
(2) SQL statements

Select *  from  Right Join  on table1.id=table2.id

-------------Results-------------

NOTE: All clauses that contain table2, return table1 corresponding fields according to specified criteria, non-conforming null display

4. Complete outer join: Full JOIN or outer join
(1) A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.
(2) SQL statements

Select *  from  Full Join  on table1.id=table2.id

Full join is not supported in my database 5.5 so I used another one instead.

Select *  from  Left Join  on table1.id=table2.idUNIONSelect* from rightJOIN on table1.id=table2.id

-------------Results-------------

Note: Returns the left and right connected and (see top

Second, internal connection
1. Concept: An inner join is a join that compares the values of the columns to be joined by comparison operators

2. Internal connection: Join or INNER JOIN

3.sql statements

Select *  from Join  on table1.id=table2.id

-------------Results-------------

Note: only table1 and table2 columns that match the criteria are returned

4. Equivalent (same as the following execution effect)
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * FROM table1 Cross join Table2 where table1.id=table2.id (note: The Add condition after cross join can only be used where, cannot be used)

Three, cross-connect (full)

1. Concept: A cross join without a WHERE clause will produce a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross-joins generate 3*3=9 Records)

2. Cross-Joins: crosses join (without conditions where ...)

3.sql statements

Select *  from  Cross Join Table2

-------------Results-------------

Note: Returns the 3*3=9 record, which is the Cartesian product

4. Equivalent (same as the following execution effect)
A:select * from Table1,table2

Usage of SQL Join