JOIN & WHERE | Explanation | Details |
left join left outer join (左联接) | 返回包括左表中的所有记录和右表中联结字段相等的记录 | 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。 |
right join right outer join (右联接) | 返回包括右表中的所有记录和左表中联结字段相等的记录 | 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。 |
inner join (等值连接) | 只返回两个表中联结字段相等的行 | WHERE 和INNER JOIN产生的连接关系,本质区别不详,结果一样。 (1)在效率上,Where可能具有和Inner join一样的效率。但基本可以肯定的(通过SQLServer帮助和其它资料,以及本测试)是Join的效率不比Where差。 (2)使用Join可以帮助检查语句中的无效或者误写的关联条件。 (3)从可读性角度来看,Where更直观。 |
full join full outer join (全连接) | 完整外部联接返回左表和右表中的所有行 | 当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。 |
Join & where总结: where是两个表中的数据满足共同条件才会显示。 Jion 显示的内容 >= where 条件显示的内容。 |
举例说明
Table : PERSONS
PERSON_ID | NAME | CITY |
1 | Jhon | London |
2 | Danny | New York |
3 | Tom | Beijing |
4 | Candy | Dalian |
5 | Venus | Chengdu |
6 | Lobna | Shanghai |
Table : ORDERS
ORDER_ID | BOOK_NUMBER | PERSON_ID |
1 | 111111 | 1 |
2 | 222222 | 1 |
3 | 333333 | 2 |
4 | 444444 | 2 |
5 | 555555 | 3 |
6 | 666666 | 4 |
7 | 777777 | 7 |
8 | 888888 | 9 |
9 | 999999 | 9 |
1) select * from cid.orders left join cid.persons on cid.orders.person_id = cid.persons.person_id;
ORDER_ID | BOOK_NUMBER | PERSON_ID | PERSON_ID | NAME | CITY |
1 | 111111 | 1 | 1 | Jhon | London |
2 | 222222 | 1 | 1 | Jhon | London |
3 | 333333 | 2 | 2 | Danny | New York |
4 | 444444 | 2 | 2 | Danny | New York |
5 | 555555 | 3 | 3 | Tom | Beijing |
6 | 666666 | 4 | 4 | Candy | Dalian |
7 | 777777 | 7 | (null) | (null) | (null) |
8 | 888888 | 9 | (null) | (null) | (null) |
9 | 999999 | 9 | (null) | (null) | (null) |
2) select * from cid.orders right join cid.persons on cid.orders.person_id = cid.persons.person_id;
ORDER_ID | BOOK_NUMBER | PERSON_ID | PERSON_ID | NAME | CITY |
1 | 111111 | 1 | 1 | Jhon | London |
2 | 222222 | 1 | 1 | Jhon | London |
3 | 333333 | 2 | 2 | Danny | New York |
4 | 444444 | 2 | 2 | Danny | New York |
5 | 555555 | 3 | 3 | Tom | Beijing |
6 | 666666 | 4 | 4 | Candy | Dalian |
(null) | (null) | (null) | 5 | Venus | Chengdu |
(null) | (null) | (null) | 6 | Lobna | Shanghai |
3) select * from cid.orders inner join cid.persons on cid.orders.person_id = cid.persons.person_id;
ORDER_ID | BOOK_NUMBER | PERSON_ID | PERSON_ID | NAME | CITY |
1 | 111111 | 1 | 1 | Jhon | London |
2 | 222222 | 1 | 1 | Jhon | London |
3 | 333333 | 2 | 2 | Danny | New York |
4 | 444444 | 2 | 2 | Danny | New York |
5 | 555555 | 3 | 3 | Tom | Beijing |
6 | 666666 | 4 | 4 | Candy | Dalian |
4) select * from cid.orders full outer join cid.persons on cid.orders.person_id = cid.persons.person_id;
ORDER_ID | BOOK_NUMBER | PERSON_ID | PERSON_ID | NAME | CITY |
1 | 111111 | 1 | 1 | Jhon | London |
2 | 222222 | 1 | 1 | Jhon | London |
3 | 333333 | 2 | 2 | Danny | New York |
4 | 444444 | 2 | 2 | Danny | New York |
5 | 555555 | 3 | 3 | Tom | Beijing |
6 | 666666 | 4 | 4 | Candy | Dalian |
(null) | (null) | (null) | 5 | Venus | Chengdu |
(null) | (null) | (null) | 6 | Lobna | Shanghai |
7 | 777777 | 7 | (null) | (null) | (null) |
8 | 888888 | 9 | (null) | (null) | (null) |
9 | 999999 | 9 | (null) | (null) | (null) |
5) select * from cid.orders, cid.persons where cid.orders.person_id = cid.persons.person_id;
ORDER_ID | BOOK_NUMBER | PERSON_ID | PERSON_ID | NAME | CITY |
1 | 111111 | 1 | 1 | Jhon | London |
2 | 222222 | 1 | 1 | Jhon | London |
3 | 333333 | 2 | 2 | Danny | New York |
4 | 444444 | 2 | 2 | Danny | New York |
5 | 555555 | 3 | 3 | Tom | Beijing |
6 | 666666 | 4 | 4 | Candy | Dalian |