{"id":1074,"date":"2023-03-12T10:55:58","date_gmt":"2023-03-12T02:55:58","guid":{"rendered":"https:\/\/www.appblog.cn\/?p=1074"},"modified":"2023-04-29T11:44:27","modified_gmt":"2023-04-29T03:44:27","slug":"difference-between-left-join-right-join-inner-join-and-full-join-in-sql","status":"publish","type":"post","link":"https:\/\/www.appblog.cn\/index.php\/2023\/03\/12\/difference-between-left-join-right-join-inner-join-and-full-join-in-sql\/","title":{"rendered":"sql\u4e2dleft join,right join,inner join,full join\u4e4b\u95f4\u7684\u533a\u522b"},"content":{"rendered":"

sql\u4e2d\u7684\u8fde\u63a5\u67e5\u8be2\u6709inner join<\/code>(\u5185\u8fde\u63a5\uff09\u3001left join<\/code>(\u5de6\u8fde\u63a5)\u3001right join<\/code>(\u53f3\u8fde\u63a5)\u3001full join<\/code>(\u5168\u8fde\u63a5)\u56db\u79cd\u65b9\u5f0f\uff0c\u5b83\u4eec\u4e4b\u95f4\u5176\u5b9e\u5e76\u6ca1\u6709\u592a\u5927\u533a\u522b\uff0c\u4ec5\u4ec5\u662f\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\u6709\u6240\u4e0d\u540c\u3002<\/p>\n

\u4f8b\u5982\u6211\u4eec\u6709\u4e24\u5f20\u8868\uff1a<\/p>\n

<\/p>\n

users\u8868<\/p>\n\n\n\n\n\n\n\n
id<\/th>\nname<\/th>\nage<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\nJoe.Ye<\/td>\n25<\/td>\n<\/tr>\n
2<\/td>\nTest<\/td>\n28<\/td>\n<\/tr>\n
3<\/td>\nXiong<\/td>\n24<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

orders\u8868<\/p>\n\n\n\n\n\n\n\n\n\n
id<\/th>\norder_no<\/th>\nuser_id<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n100001<\/td>\n3<\/td>\n<\/tr>\n
2<\/td>\n100002<\/td>\n3<\/td>\n<\/tr>\n
3<\/td>\n100003<\/td>\n1<\/td>\n<\/tr>\n
4<\/td>\n100004<\/td>\n1<\/td>\n<\/tr>\n
5<\/td>\n100005<\/td>\n10<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

orders\u8868\u901a\u8fc7\u5916\u952euser_id\u548cusers\u8868\u8fdb\u884c\u5173\u8054\u3002<\/p>\n

inner join\uff08\u5185\u8fde\u63a5\uff09<\/h2>\n

\u5728\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u53ea\u4fdd\u7559\u4e24\u5f20\u8868\u4e2d\u5b8c\u5168\u5339\u914d\u7684\u7ed3\u679c\u96c6\u3002\u4f7f\u7528inner join<\/code>\u5bf9\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\uff0csql\u5982\u4e0b\uff1a<\/p>\n

SELECT u.name, u.age, o.order_no\nFROM users u\nINNER JOIN orders o\nON u.id=o.user_id and 1=1\u3000\u3000--\u7528and\u8fde\u63a5\u591a\u4e2a\u6761\u4ef6\nORDER BY u.name<\/code><\/pre>\n
\u67e5\u8be2\u7ed3\u679c\u96c6\uff1a<\/p>\n\n\n\n\n\n\n\n\n
name<\/th>\nage<\/th>\norder_no<\/th>\n<\/tr>\n<\/thead>\n
Joe.Ye<\/td>\n25<\/td>\n100003<\/td>\n<\/tr>\n
Joe.Ye<\/td>\n25<\/td>\n100004<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100001<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u6b64\u79cd\u8fde\u63a5\u65b9\u5f0forders\u8868\u4e2duser_id\u5b57\u6bb5\u5728users\u8868\u4e2d\u627e\u4e0d\u5230\u5339\u914d\u7684\uff0c\u5219\u4e0d\u4f1a\u5217\u51fa\u6765\u3002<\/p>\n
\u6ce8\u610f\uff1a\u5355\u7eaf\u7684select * from a,b<\/code>\u662f\u7b1b\u5361\u5c14\u4e58\u79ef\u3002\u6bd4\u5982a\u8868\u67095\u6761\u6570\u636e\uff0cb\u8868\u67093\u6761\u6570\u636e\uff0c\u90a3\u4e48\u6700\u540e\u7684\u7ed3\u679c\u67095*3=15\u6761\u6570\u636e\u3002<\/p>\n
\u4f46\u662f\u5982\u679c\u5bf9\u4e24\u4e2a\u8868\u8fdb\u884c\u5173\u8054\uff1aselect * from a,b where a.id = b.id<\/code>\u610f\u601d\u5c31\u53d8\u4e86\uff0c\u6b64\u65f6\u5c31\u7b49\u4ef7\u4e8e\uff1a<\/p>\n
select * from a inner join b on a.id = b.id<\/code>\uff0c\u5373\u5c31\u662f\u5185\u8fde\u63a5\u3002<\/p>\n
\u4f46\u662f\u8fd9\u79cd\u5199\u6cd5\u5e76\u4e0d\u7b26\u5408\u89c4\u8303\uff0c\u53ef\u80fd\u53ea\u5bf9\u67d0\u4e9b\u6570\u636e\u5e93\u7ba1\u7528\uff0c\u5982Sql Server\u3002\u63a8\u8350\u6700\u597d\u4e0d\u8981\u8fd9\u6837\u5199\u3002\u6700\u597d\u5199\u6210inner join<\/code>\u7684\u5199\u6cd5\u3002<\/p>\n
left join\uff08\u5de6\u8fde\u63a5\uff09<\/h2>\n
\u5728\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u4f1a\u8fd4\u56de\u5de6\u8868\u6240\u6709\u7684\u884c\uff0c\u5373\u4f7f\u5728\u53f3\u8868\u4e2d\u6ca1\u6709\u5339\u914d\u7684\u8bb0\u5f55\u3002\u6211\u4eec\u4f7f\u7528left join<\/code>\u5bf9\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\uff0csql\u5982\u4e0b\uff1a<\/p>\n
SELECT u.name, u.age, o.order_no\nFROM users u\nLEFT JOIN orders o\nON u.id=o.user_id\nORDER BY u.name<\/code><\/pre>\n
\u67e5\u8be2\u7ed3\u679c\u96c6\uff1a <\/p>\n\n\n\n\n\n\n\n\n\n
name<\/th>\nage<\/th>\norder_no<\/th>\n<\/tr>\n<\/thead>\n
Joe.Ye<\/td>\n25<\/td>\n100003<\/td>\n<\/tr>\n
Joe.Ye<\/td>\n25<\/td>\n100004<\/td>\n<\/tr>\n
Test<\/td>\n28<\/td>\n<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100001<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100002<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u53ef\u4ee5\u770b\u5230\uff0c\u5de6\u8868\uff08users\u8868\uff09\u4e2dname\u4e3aTest\u7684\u884c\u7684id\u5b57\u6bb5\u5728\u53f3\u8868\uff08orders\u8868\uff09\u4e2d\u6ca1\u6709\u5339\u914d\uff0c\u4f46\u67e5\u8be2\u7ed3\u679c\u4ecd\u7136\u4fdd\u7559\u8be5\u884c\u3002<\/p>\n
right join\uff08\u5de6\u8fde\u63a5\uff09<\/h2>\n
\u5728\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u4f1a\u8fd4\u56de\u53f3\u8868\u6240\u6709\u7684\u884c\uff0c\u5373\u4f7f\u5728\u5de6\u8868\u4e2d\u6ca1\u6709\u5339\u914d\u7684\u8bb0\u5f55\u3002\u6211\u4eec\u4f7f\u7528right join<\/code>\u5bf9\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\uff0csql\u5982\u4e0b\uff1a<\/p>\n
SELECT u.name, u.age, o.order_no\nFROM users u\nRIGHT JOIN orders o\nON u.id=o.user_id\nORDER BY u.name<\/code><\/pre>\n
\u67e5\u8be2\u7ed3\u679c\u96c6\uff1a<\/p>\n\n\n\n\n\n\n\n\n\n
name<\/th>\nage<\/th>\norder_no<\/th>\n<\/tr>\n<\/thead>\n
Joe.Ye<\/td>\n25<\/td>\n100003<\/td>\n<\/tr>\n
Joe.Ye<\/td>\n25<\/td>\n100004<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100001<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100002<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n100005<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
orders\u8868\u4e2d\u6700\u540e\u4e00\u6761\u8bb0\u5f55user_id\u5b57\u6bb5\u503c\u4e3a10\uff0c\u5728\u5de6\u8868\u4e2d\u6ca1\u6709\u8bb0\u5f55\u4e0e\u4e4b\u5339\u914d\uff0c\u4f46\u4f9d\u7136\u4fdd\u7559\u3002<\/p>\n
full join\uff08\u5de6\u8fde\u63a5\uff09<\/h2>\n
\u5728\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\u65f6\uff0c\u8fd4\u56de\u5de6\u8868\u548c\u53f3\u8868\u4e2d\u6240\u6709\u6ca1\u6709\u5339\u914d\u7684\u884c\u3002\u6211\u4eec\u4f7f\u7528full join<\/code>\u5bf9\u4e24\u5f20\u8868\u8fdb\u884c\u8fde\u63a5\u67e5\u8be2\uff0csql\u5982\u4e0b\uff1a<\/p>\n
SELECT u.name, u.age, o.order_no\nFROM users u\nFULL JOIN orders o\nON u.id=o.user_id\nORDER BY u.name<\/code><\/pre>\n
\u67e5\u8be2\u7ed3\u679c\u96c6\uff1a<\/p>\n\n\n\n\n\n\n\n\n\n\n
name<\/th>\nage<\/th>\norder_no<\/th>\n<\/tr>\n<\/thead>\n
Joe.Ye<\/td>\n25<\/td>\n100003<\/td>\n<\/tr>\n
Joe.Ye<\/td>\n25<\/td>\n100004<\/td>\n<\/tr>\n
Test<\/td>\n28<\/td>\n<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100001<\/td>\n<\/tr>\n
Xiong<\/td>\n24<\/td>\n100002<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n100005<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\u67e5\u8be2\u7ed3\u679c\u662fleft join<\/code>\u548cright join<\/code>\u7684\u5e76\u96c6\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
sql\u4e2d\u7684\u8fde\u63a5\u67e5\u8be2\u6709inner join(\u5185\u8fde\u63a5\uff09\u3001left join(\u5de6\u8fde\u63a5)\u3001right join(\u53f3\u8fde\u63a5 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[296],"class_list":["post-1074","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-join"],"_links":{"self":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/1074","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/comments?post=1074"}],"version-history":[{"count":0,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/1074\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/media?parent=1074"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/categories?post=1074"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/tags?post=1074"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}