1.内连接、左连接、右连接、全连接介绍
<wbr><wbr><wbr>內连接仅选出两张表中互相匹配的记录.因此,这会导致有时我们需要的记录没有包含进来。内部连接是两个表中都必须有连接字段的对应值的记录,数据才能检索出来。<br><wbr><wbr><wbr>左连接和右连接都是外部连接,也就是区别于内部连接,它对不满足连接条件的行并不是象内部连接一样将数据完全过滤掉,而是保留一部分数据,行数不会减少。<br><wbr>左连接是只要左边表中有记录,数据就能检索出来,而右边有的记录必要在左边表中有的记录才能被检索出来<br><wbr>右连接是只要右边表中有记录,数据就能检索出来 ;<br><wbr>全连接则会回返回两个表中的所有记录。<br><wbr><br>
2.示例<br><wbr>有两个表<br><wbr>table1<br><wbr>user_id<wbr><wbr><wbr><wbr><wbr><wbr>user_name<wbr><wbr><wbr><wbr><wbr><wbr>user_pss<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>1<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>aaa<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>a<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>bbb<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>b<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>3<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ccc<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>c<br><wbr><br><wbr>table2<br><wbr>user_id<wbr><wbr><wbr><wbr><wbr><wbr>user_power<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>1<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>111000<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>000111</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>使用sql語句查询<br><wbr>正常状态:<br><wbr>select table1.user_id,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>table1.user_name,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>table2.user_power<br><wbr>from table1,table2<br><wbr>where table1.user_id = table2.user_id<br><wbr>但是这样检索到的数据只有两条:<br><wbr>user_id<wbr><wbr><wbr><wbr><wbr><wbr>user_name<wbr><wbr><wbr><wbr><wbr><wbr><wbr>user_power<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>1<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>aaa<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>000111<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>bbb<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>111000<br><wbr>因为第三条数据在table2中沒有关联.</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>这时使用左连接查询﹕<br><wbr>selecttable1.user_id,table1.user_name,table2.user_power<br><wbr>from table1,table2<br><wbr>where table1.user_id (+)= table2.user_id<br><wbr>就可以得到如下的结果<br><wbr>user_id<wbr><wbr><wbr><wbr><wbr><wbr>user_name<wbr><wbr><wbr><wbr><wbr><wbr><wbr>user_power<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>1<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>aaa<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>000111<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>bbb<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>111000<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>3<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ccc<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr>(null)<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr>说明﹐左或右连接查询实际上是指定以哪个表的数据为准﹐而默认(不指定左或右连接)是以两个表中都存在关键列的数据为准。<br><wbr>左连接就是以左边的表记录为主,右连接就是以右边的表记录为主,完全连接返回两个表中的所有记录。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
3. sql写法
<wbr>内连接inner join:<br><wbr><wbr> SELECT msp.name,party.name<br><wbr>FROM msp JOIN party ON party=code<br><wbr> 或<br><wbr><wbr> SELECT msp.name,party.name<br><wbr><wbr> FROM msp inner JOIN party ONparty=code<br><wbr><br><wbr> 左连接left join :<br><wbr><wbr>SELECT msp.name,party.name<br><wbr>FROM msp LEFT JOIN party ON party=code<br><wbr><br><wbr> 右连接right join :<br><wbr><wbr> SELECT msp.name,party.name<br><wbr>FROM msp RIGHT JOIN party ONmsp.party=party.code</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
全连接(full join):
<wbr>SELECT msp.name, party.name<br><wbr>FROM msp FULL JOIN party ONmsp.party=party.code</wbr></wbr>
分享到:
相关推荐
SQL 连接 JOIN 例解左连接,右连接,全连接,内连接,交叉连接,自连接
sql左连接,右连接,内连接,全连接详细讲解
SQL 左外连接,右外连接,全连接,内连接 。联接条件可在FROM或WHERE子句中指定,建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件,以进一步筛选联接条件所选的行。
SQL 左外连接,右外连接,全连接,内连接 四种连接的差异
等值连接 不等连接 自然连接 外连接 交叉连接 全连接 右连接 左连接 外连接
根据连接的方式和结果,SQL连接可以分为内连接、左连接、右连接、全连接和交叉连接等多种类型。 内连接 内连接是最基本的连接类型,它将两个表格中的数据根据某个条件连接起来。内连接可以分为等值连接、不等值...
SQL之左连接、右连接和全连接,全面讲述表与表见的链接关系、区别
SQL左外连接,右外连接,全连接,内连接四种连接的差异[参照].pdf
SQL连接JOIN例解 (左连接,右连接,全连接,内连接,交叉连接,自连接)pdf
sql左外连接、右外连接、内连接、全外连接的区别
SQL的四种连接-左外连接、右外连接、内连接、全连接 今天在看一个遗留系统的数据表的时候发现平时查找的视图是FULL OUT JOIN的,导致平时的数据记录要进行一些限制性处理,其实也可以设置视图各表为右外连接并在...
SQL的几种连接:内连接、左联接、右连接、全连接、交叉连接
SQL左外连接,右外连接,全连接,内连接四种连接的差异借鉴.pdf
SQL左外连接,右外连接,全连接,内连接4种连接的区别[归类].pdf
由浅入深sql语句(外键,左关联,右关联,全关联) 内连接,外连接
"SQL Server 连接 Oracle 方法" SQL Server 连接 Oracle 方法是数据库管理系统中的一个重要话题。本文将详细介绍如何在 SQL Server 中连接 Oracle 数据库,并解决在连接过程中可能出现的问题。 安装 Oracle 客户端...
图解SQL多表关联查询(内连接,左连接,全连接)[整理].pdf
SQL连接JOIN例解左连接右连接全连接内连接交叉连接自连接
sqlserver连接字符串大全,基本涵盖全了