\u4e00 Equijoins \u7b49\u503c\u8fde\u63a5<\/strong><\/div>\n
\u7b49\u503c\u8fde\u63a5\u662f\u6761\u4ef6\u8fde\u63a5\u5728\u8fde\u63a5\u8fd0\u7b97\u7b26\u4e3a\u201c=\u201d\u53f7\u65f6\u7684\u7279\u4f8b\u3002\uff08\u76f8\u5bf9\u4e8e\u975e\u7b49\u503c\u8fde\u63a5\uff0c\u5982\uff1abetween...and\u6761\u4ef6\uff09\u3002\u5b83\u662f\u4ece\u5173\u7cfbR\u4e0eS\u7684\u5e7f\u4e49\u7b1b\u5361\u5c14\u79ef\u4e2d\u9009\u53d6A\uff0cB\u5c5e\u6027\u503c\u76f8\u7b49\u7684\u90a3\u4e9b\u5143\u7ec4\u3002<\/p>\n
\r\nSQL> select e.employee_id, e.last_name,\r\n 2 e.department_id, d.department_id,\r\n 3 d.location_id\r\n 4 from employees e, departments d\r\n 5 where e.department_id = d.department_id;\r\nEMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID\r\n----------- ------------------------- ------------- ------------- -----------\r\n 198 OConnell 50 50 1500\r\n 199 Grant 50 50 1500\r\n 200 Whalen 10 10 1700\r\n 201 Hartstein 20 20 1800\r\n......\r\n<\/pre>\n
\u4e8c Self joins \u00a0\u81ea\u8fde\u63a5<\/strong><\/div>\n
\u81ea\u8fde\u63a5(self join)\u662fSQL\u8bed\u53e5\u4e2d\u7ecf\u5e38\u8981\u7528\u7684\u8fde\u63a5\u65b9\u5f0f\uff0c\u4f7f\u7528\u81ea\u8fde\u63a5\u53ef\u4ee5\u5c06\u81ea\u8eab\u8868\u7684\u4e00\u4e2a\u955c\u50cf\u5f53\u4f5c\u53e6\u4e00\u4e2a\u8868\u6765\u5bf9\u5f85\uff0c\u4ece\u800c\u80fd\u591f\u5f97\u5230\u4e00\u4e9b\u7279\u6b8a\u7684\u6570\u636e\u3002<\/p>\n
\r\nSELECT e1.last_name||' works for '||e2.last_name \r\n \"Employees and Their Managers\"\r\n FROM employees e1, employees e2 \r\n WHERE e1.manager_id = e2.employee_id\r\n AND e1.last_name LIKE 'R%'\r\n ORDER BY e1.last_name;\r\n \r\nEmployees and Their Managers \r\n-------------------------------\r\nRajs works for Mourgos\r\nRaphaely works for King\r\nRogers works for Kaufling\r\nRussell works for King\r\n<\/pre>\n
\u4e09 Cartesian Products\u00a0\u7b1b\u5361\u5c14\u79ef<\/strong><\/div>\n
\u00a0 \u00a0
\n\u5982\u679c2\u4e2a\u8868\u8fde\u63a5\u67e5\u8be2\u800c\u6ca1\u6709\u8fde\u63a5\u6761\u4ef6\uff0c\u5219oracle\u8fd4\u56de\u4ed6\u4eec\u7684\u7b1b\u5361\u5c14\u79ef\u3002\u5373oracle\u8fd4\u56de\u4e00\u4e2a\u8868\u91cc\u6bcf\u4e00\u884c\u4e0e\u53e6\u4e00\u4e2a\u8868\u6bcf\u4e00\u884c\u7684\u7ec4\u5408\uff0815 X 4\uff09\u3002<\/p>\n
\r\nSCOTT@orcl> select count(1) from emp;\r\n \r\n COUNT(1)\r\n----------\r\n 15\r\nSCOTT@orcl> select count(1) from dept;\r\n \r\n COUNT(1)\r\n----------\r\n 4\r\nSCOTT@orcl> select count(1) from emp, dept;\r\n \r\n COUNT(1)\r\n----------\r\n 60\r\n<\/pre>\n
\u56db Inner Joins \u5185\u8fde\u63a5<\/strong><\/div>\n
\u5185\u8fde\u63a5\u4e5f\u53eb\u7b80\u5355\u8fde\u63a5\uff0c\u662f2\u4e2a\u6216\u66f4\u591a\u8868\u7684\u5173\u8054\u5e76\u4e14\u4ec5\u8fd4\u56de\u90a3\u4e9b\u6ee1\u8db3\u8fde\u63a5\u6761\u4ef6\u7684\u884c\u3002<\/p>\n
\r\nselect e.employee_id, e.last_name,\r\n e.department_id, d.department_id,\r\n d.location_id\r\nFROM employees e JOIN departments d\r\n ON e.department_id = d.department_id;\r\n<\/pre>\n
\u4e94 Outer joins \u5916\u8fde\u63a5<\/strong><\/div>\n
\u7b80\u5355\u8fde\u63a5\u7684\u6269\u5c55\u3002\u5206\u4e3a\u5de6\u5916\u8fde\u63a5\uff08Left \u00a0outer joins\uff09\u3001\u53f3\u5916\u8fde\u63a5\uff08Right \u00a0outer joins\uff09\u3001\u5168\u5916\u8fde\u63a5\uff08Full \u00a0outer \u00a0joins\uff09\u548cPartitioned Outer Joins\uff08\u5206\u533a\u5916\u8fde\u63a5\uff0c\u7528\u4e8e\u6570\u636e\u4ed3\u5e93\uff09\u3002<\/p>\n
\r\nSELECT d.department_id, e.last_name\r\n FROM departments d LEFT OUTER JOIN employees e\r\n ON d.department_id = e.department_id\r\n ORDER BY d.department_id, e.last_name;\r\n<\/pre>\n
\u53e6\u5916\u4e00\u79cd\u5199\u6cd5\uff1a<\/p>\n
\r\nSELECT d.department_id, e.last_name\r\n FROM departments d, employees e\r\n WHERE d.department_id = e.department_id(+)\r\n ORDER BY d.department_id, e.last_name;\r\n<\/pre>\n
Oracle \u5b98\u65b9\u63a8\u8350\u4f7f\u7528\u7b2c\u4e00\u79cd\u5199\u6cd5\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u7b49\u503c\u8fde\u63a5\u662f\u6761\u4ef6\u8fde\u63a5\u5728\u8fde\u63a5\u8fd0\u7b97\u7b26\u4e3a\u201c=\u201d\u53f7\u65f6\u7684\u7279\u4f8b\u3002\uff08\u76f8\u5bf9\u4e8e\u975e\u7b49\u503c\u8fde\u63a5\uff0c\u5982\uff1abetween…and\u6761\u4ef6\uff09\u3002\u5b83 […]<\/p>\n","protected":false},"author":1903,"featured_media":214990,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[55],"tags":[],"class_list":["post-215091","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-thread"],"acf":[],"_links":{"self":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/215091","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/users\/1903"}],"replies":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/comments?post=215091"}],"version-history":[{"count":2,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/215091\/revisions"}],"predecessor-version":[{"id":215098,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/posts\/215091\/revisions\/215098"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media\/214990"}],"wp:attachment":[{"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/media?parent=215091"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/categories?post=215091"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/lrxjmw.cn\/wp-json\/wp\/v2\/tags?post=215091"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}