sql 里面 JOIN 的用法 sql中join的几种常见用法总结

\u6c42SQL\u8bed\u53e5\u91cc\u9762join\u7684\u7528\u6cd5,\u6c42\u4f8b\u5b50\u53ca\u8bb2\u89e3\u3002

join \u7528\u4e8e\u6839\u636e\u4e24\u4e2a\u6216\u591a\u4e2a\u8868\u4e2d\u7684\u5217\u4e4b\u95f4\u7684\u5173\u7cfb\uff0c\u4ece\u8fd9\u4e9b\u8868\u4e2d\u67e5\u8be2\u6570\u636e\u3002

JOIN: \u5982\u679c\u8868\u4e2d\u6709\u81f3\u5c11\u4e00\u4e2a\u5339\u914d\uff0c\u5219\u8fd4\u56de\u884c
LEFT JOIN: \u5373\u4f7f\u53f3\u8868\u4e2d\u6ca1\u6709\u5339\u914d\uff0c\u4e5f\u4ece\u5de6\u8868\u8fd4\u56de\u6240\u6709\u7684\u884c
RIGHT JOIN: \u5373\u4f7f\u5de6\u8868\u4e2d\u6ca1\u6709\u5339\u914d\uff0c\u4e5f\u4ece\u53f3\u8868\u8fd4\u56de\u6240\u6709\u7684\u884c
FULL JOIN: \u53ea\u8981\u5176\u4e2d\u4e00\u4e2a\u8868\u4e2d\u5b58\u5728\u5339\u914d\uff0c\u5c31\u8fd4\u56de\u884c

SQL INNER JOIN \u5173\u952e\u5b57
\u5728\u8868\u4e2d\u5b58\u5728\u81f3\u5c11\u4e00\u4e2a\u5339\u914d\u65f6\uff0cINNER JOIN \u5173\u952e\u5b57\u8fd4\u56de\u884c\u3002
INNER JOIN \u5173\u952e\u5b57\u8bed\u6cd5
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
\u6ce8\u91ca\uff1aINNER JOIN \u4e0e JOIN \u662f\u76f8\u540c\u7684\u3002

SQL LEFT JOIN \u5173\u952e\u5b57
LEFT JOIN \u5173\u952e\u5b57\u4f1a\u4ece\u5de6\u8868 (table_name1) \u90a3\u91cc\u8fd4\u56de\u6240\u6709\u7684\u884c\uff0c\u5373\u4f7f\u5728\u53f3\u8868 (table_name2) \u4e2d\u6ca1\u6709\u5339\u914d\u7684\u884c\u3002

LEFT JOIN \u5173\u952e\u5b57\u8bed\u6cd5
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN \u5173\u952e\u5b57
RIGHT JOIN \u5173\u952e\u5b57\u4f1a\u53f3\u8868 (table_name2) \u90a3\u91cc\u8fd4\u56de\u6240\u6709\u7684\u884c\uff0c\u5373\u4f7f\u5728\u5de6\u8868 (table_name1) \u4e2d\u6ca1\u6709\u5339\u914d\u7684\u884c\u3002

RIGHT JOIN \u5173\u952e\u5b57\u8bed\u6cd5
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
\u6ce8\u91ca\uff1a\u5728\u67d0\u4e9b\u6570\u636e\u5e93\u4e2d\uff0c RIGHT JOIN \u79f0\u4e3a RIGHT OUTER JOIN\u3002


SQL FULL JOIN \u5173\u952e\u5b57
\u53ea\u8981\u5176\u4e2d\u67d0\u4e2a\u8868\u5b58\u5728\u5339\u914d\uff0cFULL JOIN \u5173\u952e\u5b57\u5c31\u4f1a\u8fd4\u56de\u884c\u3002

FULL JOIN \u5173\u952e\u5b57\u8bed\u6cd5
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
\u6ce8\u91ca\uff1a\u5728\u67d0\u4e9b\u6570\u636e\u5e93\u4e2d\uff0c FULL JOIN \u79f0\u4e3a FULL OUTER JOIN\u3002

http://www.w3school.com.cn/sql/sql_join.asp

JOIN\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5305\u62ec\u4e09\u79cd\uff1a INNER JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\u3002 LEFT JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5e76\u5c06\u5305\u542b\u4e86LEFT JOIN\u5de6\u8fb9\u8868\u4e2d\u7684\u5168\u90e8\u8bb0\u5f55\u3002 RIGHT JOIN\u8fd0\u7b97\u5f0f\uff1a\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\uff0c\u5e76\u5c06\u5305\u542b\u4e86RIGHT JOIN\u53f3\u8fb9\u8868\u4e2d\u7684\u5168\u90e8\u8bb0\u5f55\u3002 INNER JOIN\u8bbe\u5b9a\u4e24\u4e2a\u8868\u76f8\u5173\u8fde\u7684\u8fd0\u7b97\u5f0f\uff0c\u4ee5\u8fde\u63a5\u7ec4\u5408\u4e24\u4e2a\u8868\u4e2d\u7684\u5b57\u6bb5\u8bb0\u5f55\u3002 INNER JOIN\u8bed\u6cd5\u5982\u4e0b\uff1a FROM \u88681 INNER JOIN \u88682 ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52 \u4e24\u4e2a\u8868\u8fde\u63a5\u7684\u5b57\u6bb5\uff0c\u8b6c\u5982 [\u88681.\u5b57\u6bb51=\u88682.\u5b57\u6bb52]\uff0c\u5fc5\u987b\u5177\u6709\u76f8\u540c\u7684\u5b57\u6bb5\u7c7b\u578b\uff0c\u4f46\u662f\u5b57\u6bb5\u540d\u79f0\u4e0d\u9700\u8981\u76f8\u540c\u3002 \u4f8b\u5982\uff0c\u81ea\u52a8\u7f16\u53f7\u5b57\u6bb5\u7c7b\u578b\u53ef\u4ee5\u8fde\u63a5Long \u7684\u5b57\u6bb5\u7c7b\u578b\uff0c\u4f46\u662f\u5355\u7cbe\u6574\u6570\u5b57\u6bb5\u7c7b\u578b\u4e0d\u80fd\u8fde\u63a5\u53cc\u7cbe\u6574\u6570\u7684\u5b57\u6bb5\u7c7b\u578b\u3002 \u6bd4\u8f83\u8fd0\u7b97\u5b50\u53ef\u4e3a=\u3001\u3001=\u3001\u6216\u3002 JOIN\u8fde\u63a5\u7684\u5b57\u6bb5\uff0c\u4e0d\u53ef\u4ee5\u5305\u542bMEMO\u5b57\u6bb5\u7c7b\u578b\u6216OLE\u5bf9\u8c61\u7c7b\u578b\uff0c\u5426\u5219\u4f1a\u53d1\u751f\u9519\u8bef\u3002 \u5728\u4e00\u4e2aJOIN\u8868\u8fbe\u5f0f\u4e2d\uff0c\u53ef\u4ee5\u8fde\u7ed3\u591a\u4e2aON\u5b50\u53e5\uff1a SELECT fields FROM \u88681 INNER JOIN \u88682 ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb51 AND ON \u88681.\u5b57\u6bb52 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52) OR ON \u88681.\u5b57\u6bb53 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb53) JOIN\u8868\u8fbe\u5f0f\u4e2d\uff0c\u53ef\u4ee5\u4e3a\u5de2\u72b6\u5f0f\uff1a SELECT fields FROM \u88681 INNER JOIN (\u88682 INNER JOIN [( ]\u88683 [INNER JOIN [( ] \u8868x [INNER JOIN ...)] ON \u88683.\u5b57\u6bb53 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u8868x.\u5b57\u6bb5x)] ON \u88682.\u5b57\u6bb52 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88683.\u5b57\u6bb53) ON \u88681.\u5b57\u6bb51 \u6bd4\u8f83\u8fd0\u7b97\u5b50 \u88682.\u5b57\u6bb52 \u5728\u4e00\u4e2aINNER JOIN\u4e2d\uff0c\u53ef\u4ee5\u5305\u62ec\u5de2\u72b6\u5f0f\u7684LEFT JOIN\u6216RIGHT JOIN\uff0c\u4f46\u662f\u5728\u4e00\u4e2aLEFT JOIN\u6216RIGHT JOIN\u4e2d\u4e0d\u80fd\u5305\u62ec\u5de2\u72b6\u5f0f\u7684INNER JOIN\u3002

select name,shopname from brand_categories join brand_shopitems on brand_categories . catid=brand_shopitems.region这个是表连接语句，用这个代替你的查询语句即可

将'SELECT * FROM brand_shopitems' 改为 'SELECT brand_categories.name,brand_shopitems.shopname FROM brand_categories join brand_shopitems on brand_categories.catid=brand_shopitems.region' 试试

select brand_shopitems.region,shopname,name from brand_shopitems join brand_categories on brand_shopitems.region = brand_categories.catid

直接在里边改动就可以了,至于下面如果改,我想你懂的!

扩展阅读：xbox series x ... sql中的join on用法 ... vb6 sql inner jion ... java windowbuilder ... sql join 一对多 ... sql join on 三表连接 ... full join mysql ... sql join on 多表链接 ... sql join on 三个条件 ...