SQL multi-table join queries

inner join, full outer join, left join, right jion
A combination of internally connected inner join two tables are met
full outer whole company, the same combination of the two tables together in Table A, Table B data (null), the same table B
A table does not display (null)
Table A left join B table left with Table A, some combination of all the data of Table A, B table. Not to null
A table right join table B right Lian, Table B, some combination of all the data of Table B, A table. Not to null

Query Analyzer to perform:
– Built table table1, table2:
create table table1 (id int, name varchar (10))
create table table2 (id int, score int)
insert into table1 select 1, ‘lee’
insert into table1 select 2, ‘zhang’
insert into table1 select 4, ‘wang’
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
As shown in Table
————————————————-
table1 | table2 |
————————————————-
idName | idscore |
1lee | 190 |
2zhang | 2100 |
4wang | 370 |
————————————————-

The following are executed in Query Analyzer

First, the outer join
1 concept: left outer join, right outer join or full outer join
2 left join: LEFT join or left outer join (When you need to know that the connection table information)
(1) left outer join result set includes all rows of the left table specified in the LEFT OUTER clause, not just the matching join column row. If there is no match in the left table in a row in the table on the right line, the right table, the associated result set row select list columns are null (null).
(2) sql statement
select * from table1 left join table2 on table1.id = table2.id
————- ————- Results
idnameidscore
——————————
1lee190
2zhang2100
4wangNULLNULL
——————————
Note: contains table1 clause, according to the specified conditions return table2 appropriate fields, does not comply with the null

The right connection: Right join or right outer join (When you need to know all the information in the table is connected)
(1) The right outer join is a the reverse join left outer join. Will return all the rows in the table on the right. If the right table in a row left the table without matching rows, the left table will return a null value.
(2) sql statement
select * from table1 right join table2 on table1.id = table2.id
————- ————- Results
idnameidscore
——————————
1lee190
2zhang2100
NULLNULL370
——————————
Note: contains table2 clause, according to the specified conditions return table1 appropriate fields, does not comply with the null

4 full outer join: full join or Full outer join
(1) full outer join returns the left and all the rows in the table on the right. When a bank does not match rows in another table, another table select list column contains a null value. If there is a match between the table line, the entire result set row contains data values ??of the base table.
(2) sql statement
select * from table1 full join table2 on table1.id = table2.id
————- ————- Results
idnameidscore
——————————
1lee190
2zhang2100
4wangNULLNULL
NULLNULL370
——————————
Note: Back around and connected (see above left, right)

Second, the inner connection
Join concept: inner join with comparison operators to join column values

2 inner join: join or inner join

The 3.sql statement
select * from table1 join table2 on table1.id = table2.id
————- ————- Results
idnameidscore
——————————
1lee190
2zhang2100
——————————
Note: returns only qualified table1 and table2 column

Equivalent (with the same effect as the following executive)
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: cross join plus conditions can only be used where, can not be used on)

Third, cross-connect (full)

1 concept: Cross joins in the WHERE clause involved in the join table Cartesian product. The number of rows of the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian product of the result set. (Table1 and table2 cross-connect a 3 * 3 = 9)

2 cross-connect: Cross join (without conditions where …)

The 3.sql statement
select * from table1 cross join table2
————- ————- Results
idnameidscore
——————————
1lee190
2zhang190
4wang190
1lee2100
2zhang2100
4wang2100
1lee370
2zhang370
4wang370
——————————
Note: return 3 * 3 = 9, ie, the Cartesian product

Equivalent (with the same effect as the following executive)
A: select * from table1, table2