在SQL语言中，join什么时候用，什么时候不用 数据库设计的时候，什么时候用关联,什么时候不用
sql\u4e2d\u4f7f\u7528JOIN\u4e0e\u4e0d\u4f7f\u7528 \u7684\u533a\u522b\uff0c\u9700\u8981\u8be6\u7ec6\u89e3\u7b54\uff01\u4f60\u5199\u7684\u8fd92\u4e2a\u662f\u76f8\u7b49\u7684\u5173\u7cfb\uff0c\uff0c\uff0c
\u9ed8\u8ba4\u4e0d\u5199\u5c31\u662finner join
\u4f46\u662f\u8fd8\u6709\u5176\u4ed6\u7684\uff0c\u4f8b\u5982left join,right join\u7b49
\u9996\u5148\u4f60\u8981\u660e\u767d\u5173\u8054\u7684\u5b9a\u4e49\uff0c\u662f\u6307\u7684\u8868\u4e2d\u7684\u5c5e\u6027\u96c6\u7684\u5173\u8054\u3002\u8bf4\u901a\u4fd7\u70b9\u5c31\u662f\u6570\u636e\u5e93\u4e2d\u5404\u4e2a\u8868\u4e2d\u7684\u5217\u6bb5\u662f\u4e0d\u662f\u4e00\u6837\u7684\u3002\u6bd4\u5982A\u8868\u7684\u5f20\u4e09\u548cB\u8868\u7684\u5f20\u4e09\uff0c\u662f\u6307\u7684\u540c\u4e00\u4e2a\u4eba\uff0c\u90a3\u4e48\u8fd9\u4e24\u4e2a\u5f20\u4e09\u5c5e\u6027\u9700\u8981\u5173\u8054\u8d77\u6765\uff0c\u8fdb\u800c\u4e24\u4e2a\u6570\u636e\u8868\u4e5f\u5173\u8054\u8d77\u6765\u4e86\u3002
一般来讲，关系数据库中需要用指定连接方式(例如指定内连接、左右连接、全外连接)来组合、筛选来自多张表(包括1张表自连接)或查询或视图的信息时就要使用join连接。不需要连接、或者需要连接多表，但是不指定连接方式的连接(如在where 子句里规定连接条件)、子查询(exists子查询、in子查询等)就不用join。
绛旓細涓鑸琽n鏄叧鑱2涓〃瀛楁锛寃here鍙槸閫氳繃鏌愪釜琛ㄦ潯浠舵潵闄愬埗缁撴灉銆
绛旓細on 1=1 灏辨槸涓よ〃涓嶇浠涔鏉′欢閮芥垚绔 绛夊悓浜 select *form 琛 where 1=1 灏辨槸鏌ヨ鎵鏈夌殑select *form 琛
绛旓細INNER JOIN 绛変环浜 JOIN 浣犲彲浠ョ悊瑙ｄ负 JOIN 鏄 INNER JOIN 鐨勭缉鍐欍侺EFT JOIN 绛変环浜 LEFT OUTER JOIN RIGHT JOIN 绛変环浜 RIGHT OUTER JOIN
绛旓細Join鏄繛鎺ワ紝鐢ㄤ簬澶氫釜鏁版嵁琛ㄥ叧鑱旈夊彇鏁版嵁銆備綘鏃㈢劧鐢鐨勬槸SQL Server锛屼负浠涔涓嶅鐪嬩竴涓嬪畠鐨勫府鍔锛孲QL Server鐨勫府鍔╂槸鍏ㄤ腑鏂囩殑锛屽苟涓旈潪甯歌缁
绛旓細left join :宸﹁繛鎺ワ紝杩斿洖宸﹁〃涓墍鏈夌殑璁板綍浠ュ強鍙宠〃涓繛鎺ュ瓧娈电浉绛夌殑璁板綍銆俽ight join :鍙宠繛鎺ワ紝杩斿洖鍙宠〃涓墍鏈夌殑璁板綍浠ュ強宸﹁〃涓繛鎺ュ瓧娈电浉绛夌殑璁板綍銆俰nner join:鍐呰繛鎺ワ紝鍙堝彨绛夊艰繛鎺ワ紝鍙繑鍥炰袱涓〃涓繛鎺ュ瓧娈电浉绛夌殑琛屻俧ull join:澶栬繛鎺ワ紝杩斿洖涓や釜琛ㄤ腑鐨勮锛歭eft join + right join cross join:...
绛旓細浣嗘槸骞朵笉鍖呭惈鍦ㄦ煡璇㈣緭鍑涓紝鍥犲苟闈炶鍖呭惈鍦 SELECT 璇彞涔嬩腑銆傚湪杩欎釜绀轰緥涓紝鑻ヨ鍖呭惈鑱旀帴瀛楁锛屽皢瀛楁鍚嶅寘鍚湪 SELECT 璇彞涓銆2銆乮nner join锛氬彲浠ヤ娇鐢 INNER JOIN 涓庨儴闂ㄨ〃鍙婂憳宸ヨ〃閫夋嫨姣忎竴涓儴闂ㄤ腑鐨勫叏閮ㄥ憳宸ャ傚弬鑰冭祫鏂欐潵婧愶細鐧惧害鐧剧-INNER JOIN 鍙傝冭祫鏂欐潵婧愶細鐧惧害鐧剧-SQL LEFT JOIN ...
绛旓細1 a1 2 a2 3 a3 琛╞ bid bdate 1 b1 2 b2 4 b4 涓や釜琛╝,b鐩歌繛鎺,瑕佸彇鍑篿d鐩稿悓鐨勫瓧娈 select * from a ,b where a.aid = b.bid杩欐槸浠呭彇鍑哄尮閰嶇殑鏁版嵁.姝ゆ椂鐨勫彇鍑虹殑鏄:1 a1 b1 2 a2 b2 閭ｄ箞left join 鎸:select * from a left join b on a.aid = b.bid 棣栧厛鍙栧嚭a琛ㄤ腑...
绛旓細鍦ㄦ煡璇㈢殑涓や釜琛ㄥぇ灏忕浉褰撶殑鎯呭喌涓嬶紝3绉嶆煡璇㈡柟寮忕殑鎵ц鏃堕棿閫氬父鏄細EXISTS <= IN <= JOIN NOT EXISTS <= NOT IN <= LEFT JOIN 鍙湁褰撹〃涓瓧娈靛厑璁窷ULL鏃讹紝NOT IN鐨勬柟寮忔渶鎱細NOT EXISTS <= LEFT JOIN <= NOT IN 浣嗘槸濡傛灉涓や釜琛ㄤ腑涓涓緝灏忥紝涓涓緝澶э紝鍒欏瓙鏌ヨ琛ㄥぇ鐨鐢exists锛屽瓙鏌ヨ琛ㄥ皬...
绛旓細鍦⊿QL涓紝LEFT JOIN鍏抽敭瀛楁槸涓绉嶇壒娈婄殑杩炴帴鎿嶄綔锛屽畠纭繚杩斿洖宸﹁〃锛堣〃1锛変腑鐨勬墍鏈夎锛屽嵆浣垮湪鍙宠〃锛堣〃2锛変腑娌℃湁鐩稿簲鐨勫尮閰嶃傚綋鍙宠〃涓殑鏁版嵁涓嶅瓨鍦ㄦ椂锛岀粨鏋滀細鏄剧ず涓篘ULL銆傚叾璇硶缁撴瀯濡備笅锛歋QL鐨凩EFT JOIN璇硶濡備笅:SELECT column_name(s)FROM table1 LEFT JOIN table2 ON table1.column_name = table...
绛旓細sql 涓殑鍐欐硶涓璲oin榛樿绛夊悓浜嶪NNER JOIN ,浣嗚繕鏈塴eft join,right join full join绛,閫夋嫨閫傚綋鐨勫氨琛屼簡,鍙﹀鍦ㄩ」鐩腑涓鑸鐢left join 鐢ㄧ殑澶氫竴鐐圭偣
