SQL中，用select语句时，为什么有时候要用inner join，有时候不用啊？希望好心人帮忙...... 在SQL语言中，join什么时候用，什么时候不用
sql select \u8bed\u53e5\u52a0inner join\u7684\u4e00\u4e9b\u6bd4\u8f83\u590d\u6742\u7684\u95ee\u9898\u521b\u5efa\u6d4b\u8bd5\u8868
create table t1(id int,score int,sex int)create table t2(id1 int,id2 int,dscore int)insert into t1 values (1,5,1)insert into t1 values (2,4,0)insert into t1 values (3,7,1)insert into t2 values (1,2,1)insert into t2 values (2,3,2)\u6267\u884c
select c.id1,a.sex sex1,c.id2,b.sex sex2from t1 a,t1 b,t2 c where a.id=c.id1and b.id=c.id2\u7ed3\u679c
\u5176\u5b9e\u5c31\u662ft1\u8868\u5206\u522b2\u6b21\u4e0et2\u8868\u5173\u8054
\u7b2c\u4e00\u6b21\u5173\u8054id1\uff0c\u7b2c\u4e8c\u6b21\u5173\u8054id2
\u4e00\u822c\u6765\u8bb2\uff0c\u5173\u7cfb\u6570\u636e\u5e93\u4e2d\u9700\u8981\u7528\u6307\u5b9a\u8fde\u63a5\u65b9\u5f0f(\u4f8b\u5982\u6307\u5b9a\u5185\u8fde\u63a5\u3001\u5de6\u53f3\u8fde\u63a5\u3001\u5168\u5916\u8fde\u63a5)\u6765\u7ec4\u5408\u3001\u7b5b\u9009\u6765\u81ea\u591a\u5f20\u8868(\u5305\u62ec1\u5f20\u8868\u81ea\u8fde\u63a5)\u6216\u67e5\u8be2\u6216\u89c6\u56fe\u7684\u4fe1\u606f\u65f6\u5c31\u8981\u4f7f\u7528join\u8fde\u63a5\u3002
\u4e0d\u9700\u8981\u8fde\u63a5\u3001\u6216\u8005\u9700\u8981\u8fde\u63a5\u591a\u8868\uff0c\u4f46\u662f\u4e0d\u6307\u5b9a\u8fde\u63a5\u65b9\u5f0f\u7684\u8fde\u63a5(\u5982\u5728where \u5b50\u53e5\u91cc\u89c4\u5b9a\u8fde\u63a5\u6761\u4ef6)\u3001\u5b50\u67e5\u8be2(exists\u5b50\u67e5\u8be2\u3001in\u5b50\u67e5\u8be2\u7b49)\u5c31\u4e0d\u7528join\u3002
可以改为等价的where条件
在有关联到多张表时使用,有时自身关联也可以
建议你再多去学习，了解LEFT JION 、RIGHT JOIN、INNER JOIN ，看看他们连接出来都是什么样的结果，你就知道什么时候该用什么连接了，是根据你的需求来的
绛旓細鐢╥nsert into 鎴栬卛nto璇彞銆傚鏋滄彃鍏ュ凡鏈夌殑琛:insert into A(col1,col2)select c1, c2 from B 鐢熸垚涓涓柊琛細select c1, c2 into A from B
绛旓細閫楀彿锛熼楀彿鏄敤浜庡垎鍓插瓧娈靛拰琛ㄧ瓑淇℃伅鐨锛岄楀彿鍒嗗壊鐨勬槸骞跺垪鐨勫叧绯 姣斿瑕佹煡璇㈠嚑涓瓧娈 select 瀛楁1,瀛楁2,瀛楁3鈥︹﹁繖閲岀敤閫楀彿 姣斿瑕佷粠鍑犱釜琛ㄩ噷鏌 鈥︹rom 琛1,琛2鈥︹︽瘮濡備竴涓潯浠惰婊¤冻鏌愪釜鑼冨洿 where id in (1,2,3,4,5...)姣斿瑕佹寜鍑犱釜鏉′欢鎺掑簭 order by id desc,hits desc...姣斿...
绛旓細select * from A 杩欓噷鏄熷彿琛ㄧず鏌ヨ鍑鸿〃閲岄潰鐨勫叏閮ㄥ瓧娈碉紝濡傛灉浣犱笉闇瑕佹煡璇㈠叏閮ㄥ瓧娈碉紝灏卞彲浠ヤ笉浣跨敤鏄熷彿锛岃屾槸鍒跺畾鍒楀嚭鐨勫瓧娈点傛瘮濡傦細select a1,a2,a3 from A
绛旓細SQL:select a.a,a.b,a.c,b.c,b.d,b.f from a LEFT OUT JOIN b ON a.a = b.c璇存槑锛氭棩绋嬪畨鎺掓彁鍓嶄簲鍒嗛挓鎻愰啋浠ヤ笅鏄唬鐮佺墖娈碉細SQL:select from 鏃ョ▼瀹夋帓 where datediff(鈥檓inute鈥,f寮濮嬫椂闂,getdate())>5 璇存槑锛氫袱寮犲叧鑱旇〃锛屽垹闄や富琛ㄤ腑宸茬粡鍦ㄥ壇琛ㄤ腑娌℃湁鐨勪俊鎭 SQL:浠ヤ笅鏄唬鐮佺墖娈碉細...
绛旓細select col col_new from 琛ㄥ悕select col as col_new from 琛ㄥ悕select col_new=col from 琛ㄥ悕select 'col_new'=col from 琛ㄥ悕select [col_new]=col from 琛ㄥ悕鍦∕SSQL涓紝涓婅堪鍑犲彞SQL閮芥槸绛夊悓鐨
绛旓細select ... from ...group by ...where ...having ...瀵逛簬鑳藉鐩存帴鍒ゆ柇鐨锛岀敤where灏卞彲浠ユ帓闄 瀵逛簬浣跨敤鑱氬拰鍑芥暟浜х敓鐨勫硷紝闇瑕佸湪having涓垽鏂緥濡 where 鎴愮哗 > 90 having avg(鎴愮哗) > 90 where 鍜 having 鍙互鍚屾椂浣跨敤
绛旓細insert into 琛ㄥ悕绉(瀛楁鍚嶇О1,瀛楁鍚嶇О2,鈥︹)select 瀛楁鍚嶇О1,瀛楁鍚嶇О2,鈥︹rom 琛ㄥ悕绉 杩欎釜鏄爣鍑嗙殑锛屼篃灏辨槸浣犲凡缁忓缓绔嬩簡缁撴灉琛ㄧ殑鎯呭喌銆傚鏋滄病寤虹珛缁撴灉琛紝鍙槸鏆傛椂瀛樺偍锛岀敤涓嬮潰鐨 select * into #琛ㄥ悕 from 琛ㄥ悕绉 select * from #琛ㄥ悕 娉ㄦ剰锛岃〃鍚嶅墠闈㈡湁涓#锛屼复鏃跺瓨鍌ㄧ殑锛屽綋鍏抽棴浜...
绛旓細select 鏄煡璇㈠懡浠ゅ憖锛屽彧瑕佹槸鏌ヨ閮借鐢紱select count鏄垎缁勬煡璇紝鐩殑鏄粺璁℃暟閲忥紱select count as 鍜宻elect count鏄竴鏍风殑锛孉S鍚庨潰鏄粰缁熻鏁伴噺鐢熸垚鐨勫垪缁欎釜鍚嶅瓧锛屼笉瑕乤s鐩存帴绌烘牸缁欎釜鍚嶅瓧涔熷彲浠ョ殑銆
绛旓細闂涓垪2鏈夌┖鍊硷紝绌哄间笉鑳界洿鎺ュ弬涓庢瘮杈冿紝鎵浠ユ瘮杈鐨勬椂鍊鍏堣浆鎹㈡垚涓涓壒娈婄殑鍊硷紝鐒跺悗鍐嶈繘琛屾瘮杈冦備笅鍒璇彞鏄紝褰撳垪2鐨勬槸绌哄兼椂锛岃浆鎹㈡垚涓涓┖鏍笺傛煡璇㈠垪1涓殑鍊间笉鍚屼簬鍒2鍊肩殑璁板綍锛select * from 琛 where 鍒1 not in (select nvl(鍒2,鈥 鈥) from 琛);鏌ヨ鍒2涓殑鍊间笉鍚屼簬鍒1鍊肩殑璁板綍锛...
绛旓細閲嶈褰浣跨敤 CUBE 鎴 ROLLUP 鏃讹紝涓嶆敮鎸佸尯鍒嗚仛鍚堬紝渚嬪 AVG(DISTINCT column_name)銆丆OUNT(DISTINCT column_name)銆丮AX(DISTINCT column_name)銆丮IN(DISTINCT column_name) 鍜 SUM(DISTINCT column_name)銆傚鏋滀娇鐢ㄤ簡锛孧icrosoft�0�3 SQL Server�6�4 灏嗚繑鍥為敊璇俊鎭...
