oracle中的连接查询与合并查询总结

连接查询：

连接查询是指基于多张表或视图的查询。使用连接查询时，应指定有效的查询条件，不然可能会导致生成笛卡尔积。如现有部门表dept，员工表emp,以下查询因查询条件无效，而产生笛卡尔积： (各语句中字段不做解释，主要显示逻辑关系）

select dept.dname,emp.ename from dept,emp where dept.name = 'sales';

有效查询条件应该指定dept表与emp表之间的连接关系。表之间连接关系多如下：

1.相等连接（=）

相等连接主要用于查询主从表之间的相关数据，如下：

select table1.col1,table2.col2 from table1,table2 where table1.col1 = table2.col2  ;

同时可以用and指定其他查询条件，如修改第一句：

select dept.dname,emp.ename from dept,emp where dept.deptno = emp.deptno and dept.name = 'sales';

2.不等连接

不等连接指连接条件中使用除相等比较的其他比较操作，多用于在不同表之间显示特定范围信息。如下：

select a.enma, a.sal .b.grade from emp a, salgrade b where a.sal between b.losal and b.hisal;

3.自连接

指在同一张表中的连接查询，主要用在自参照表上显示上下级关系或者层次关系。自参照表是指在不同列之间具有参照关系或者主从关系的表。如下，emp表中包含具有主从关系的两个字段，empno（雇员号），mgr（管理者号），为显示雇员与管理者之间的对应关系，可以用自连接：

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOoAAABoCAIAAABwjvvKAAAIWklEQVR4nO2dyXLcOAyG9SA55u3zHOMth/HSqRy95ZqqeQPMoR2aTQIgSAFcFPzV5ZIgLgD5iaJEdXv7599n//hn0c823AP/+Kf54/j6Z+HPB75wRP38+XO0CybyuIKOjO+PHz9Gu2AijyvoA99vLtf0IvFVOHfm0+l0Gu2CiTyuIMd3PXlcQY7vevK4ghzf9eRxBTm+68njCrrAd8uU2KltYfr8qKlCc7T5eTYmu1Xt0C0u1H8m3j7tXyuF0Zfqv6SZ5OmTNExedcXNUesnY6lqBwslcRX9GdX+tdLEFx14YB++Ej4UheJLxYUeYtJT7dCBiSK+zLnk+ErxhYyAOfHN/cwPxcbl8I11fHyDLtIR3bxdik8/Ft+quCRGqh3UA0mUn5bFdo7Td/OzVi34Xt0/X90/f+5XjlJUK0yIL1qjIr5o+RbaiS+fYKCmwxdKF7VJ8AVsMiC/qjDlW6gK3ySEnn7WShPfNN1R8KUcQDGltuXtYKHa0XfUaVarNnxfru5fPvfpwQl24yvMqyXhg7OwoYhv59OS92dU+9dqL75bptgOUTPFG+iYygy6VC51VS1bQIZyHmOxHfqElsSV+4NGh8bboRfkUhh9jyRfXF1Lju+FvJvXUju+J5drepH4apw80wkN+ADyuIIc3/XkcQUNxvf3l6/Nn2Lh3s1raWp82xDcU6B381qaDl8tUttq9G5eS7PgK0GWedRPPf8v2ik3qGWI3I28QLl73eT4BinjKx9oEyDQ7XhXYk/ShOZIvMqxQy3FKhxfXY3Et3aGYIFvsnu6fDcgjMS8M8GSV8HU1VOOb5AOvnumtjwTRdrk+MIfiIs+xOmBxneUHN+g7fr+5Xofvjtvy6iRD51cohNf6miCbziaO+z47teeR5xnNeH78HL90I6vOrsgwCUnOL7TCnZ09EXddnzbJGRUyPER8BXOLyX2HN+wIcQX2Hu4UQ8fZsC37WEon+vI+DZgbYov44O1xuKrtd6U2wfgC6r3bblFgg6VBsUXdViIL1VvZ43CV3f5SWWVVAFf2EEwNfdFL81Fe1JOaI7kvi0ZUPNi811Jdd00BF+jddO42GH4gvapqaKkOSb0sE398TVtt1B4G76v1w+viq7Mgwi16ra6OuPboenOVbTjq/tWfPzSzEBN4sbS6tZ6kopIfBXOIEz+xpmFusXV+ZLVUJ0tvrF2Lsk0lOb47lH/6dZ5DK7K0g9fSsxi4851SMe3WUNuFc5x1b34NRxfOzm+bRp1m3uKbrWFWRzf9eT4Bg3Dd8vE25OjVGmxMelmqljegm7zvlkvapjiS6HDxC5pENSeVBHHJf3Sw0B8i9tUlrw5UDv+qEWw+AzsmzpVLlnIDl8GGmF/VfUR318SgpfBt9YOAnzzvHEr8y3O8+r4NpznLfjePLzeDJ37ovGgVyhJ9lg78U3+ogkkbqjLCF/p9br+vJXgDlhcRZdmxJfBhcIanU4Z4UtN3VCjhabCFyrbhJqhQSO+j683j8PwLfY3ejWX2KHv6BsnsIZ4IL784Cq8Qsas/0X4VtlBA18oXQ2KblvIAt89Qy+fQG5H4+Idmw7fWkxH4St020JL4NuAteNbhy/QNAvxre0tFanjW7FMoIEvlYaKi3seMhu+QN8K1Nol+MbZKUuCMlodZbfQVPjCjv5KylkPX1OF5mCGzxU1EF9TOb4XivHtMy72keMb9IFv9cvxrqNoiW+jUF8+2G4e324e39TOoJmEBnwA6cY1ydALbFzki0SO73JyfIMc3/WkGNc87ILjm8jxLWoVfIFw1fFdT45v0DB8mSUAIJ5sUw+/issWVF3Fp2nFp/Rb9r5bhyd0Q/Bl+qvYicJ+XAxfdDvepdJI8gL9E31UFbyTba5aaBS+xW2gG1bSUC343j6+3c6EL9X3tc0Eu/ENoynjW16ONbtwIHyB7i+hq8Pw/fSg6Y27PfgKR0oJvkxiO42d+/L9hbZAw9Uy10r4ohOseFsy90rwpeZhvGNyfNH5nIWmwhfYNXm+wZOjTfg+vd0+DcO3GDCKaW5HS/PRt6jqH2ViB1f5lRPtx4PgyydI7A2TByZ7nCAfVIr48mm0NBW+tf3F2B1fTXwlifNDji9vZ7IfHF+0aXTxLVZRu22h1fGVTPaErk6HL7C3WbmRSS9ftkgOUduUG3x6dU2FLwj6S9iP6+FrKl80Lmr5RWPHdzn5C5NBH/jqvhvvWkhrf9vCR9/lpBvXEUZfM5dGyvGVyPGdVI6vRKvj+3779G7m0kg5vkJNQjAVF/dFecd3OTm+QcPwlS8loFmoomJ70hz88gTlZNFzWHnZ4izhT6Nq9QvVRIvh27wd71J28G9b1Kj8S7pK/cLkReMq/MLk3dP73dHxZSgsDr08l3k51uzCgfAFor/kjg3D99MD2St2/C5qZ/AtlgMyfJnEdhqCb1BDv0hwh7ZfV18FX6DnUlSWzvgyjunK6JZ0yf9tcXd6vzsNw1fObmwsnuJn+ehbpSX/t8Uq+DZcs/bgu11KXs6ic9+zqmbAqKVqPAIWX9HpdAB8qUL23Lqh5UvKcXx5O5P9b8RXeCtA4S4pds+2hUyXYwp3+hr9QqWJ45JOxFfBF4hbhC1TOEQtWzB5w4QM3S6Wgx5Vl/VqovBfw8ZGNOqiPSknxFXxGGQsvqbyReM2jVpDdnwv5Pg2awjB57iqqv7AV/fdeNcBNORbGHylJL5qZ9BMQgM+gLrF1XkMbvnFKsd3OfWMqxvB4dts32jluRzf9dQ5rg4En6sI+P5HKM/o+K6n/nGZEhwKj/EV5j0yvi5F/f7y1QLinWU6vq4KKRKscj5s30+/vp9+qTjk+hu0HzvFgdzxdbWoDUH1Gcj/ijhzScBrq+kAAAAASUVORK5CYII=" alt="" />

select manager.ename  from emp manager, emp worker where manager.empno = worker.mgr and worker.ename = 'KING';

4.内连接与外连接

select table1.column, table2.column from table1 [inner | left | right | full] join table2 on table1.column1 = table2.column2;

以上分别为内连接、左连接，有链接，完全连接，on子句用于指定连接条件。

内连接：用于返回满足查询条件的所有记录，默认情况下，在指定连接查询时如果没有指定任何连接操作，那其就为内连接，目前以上查询语句皆为内连接，举例，以下两句相同：

select dept.dname,emp.ename from dept,emp where dept.deptno = emp.deptno and dept.name = 'sales';

select dept.dname,emp.ename from dept inner join emp on dept.deptno = emp.deptno and dept.name = 'sales';

左外链接：返回满足连接条件的所有记录，同时返回不满足连接条件的连接操作符左边表的其他行，即左边返回所有记录，右表只返回满足查询条件的记录。为方便，引用他人的例子说明查询效果（参考：http://www.blogjava.net/hello-yun/archive/2011/04/08/347890.html），如下：

SELECT * FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A；

查询效果如下：

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAZQAAABFCAIAAADisofJAAAFQUlEQVR4nO2d25WjMBAFnREREZCzcTDOh/1g7JGEHlc0O7bUVV9rYOeYOs0FhGjftoDbi2ThFnPcIFyS/Dv7348LAQC6aATTFiRaSLhK/AvXfWcAgEN4ZbYQ4klfCwBwCQQNAAwJ4QUAQ0J4AcCQEF4AMCSZSQ8AAN9GO7z+b1SODHJEECWCKB3CywRyRBAlgiida8Lrsd5ut9tyf172vQahLOd5Xw7XuA4FvaCKRKqidkMx6+PvvtyXcUl4Pdbbsq6LQ5GN8Ip8PO+L30OTKhJphldcQY/VcX5dEF4/h+nxaHVAT3jtlebN0A9UkUhneG3P+8IZMVrY3CLgXW4e647wEqGKRAgvHXN4BcXm8L6o97bR7VFJFYn03zbiKl7Y3OJNdM/tr+76BuydyQmhikS6B+wdX6cawys9FWSua6em67bxsfqNL6pIpPPKaz9HOo0vW3jlzwSOXPaNeTk+Kqkikd4xL8+DhJbwyl7f+3p2S3iJUEUihJeOIbwK1lzV3Ynw8qImhioSORVePk+IhvAqJr6nAdfuMS8nXg5QRSJnxrw8hvy2GcKrciQ6qru+p40+nGShikS6nzZ6Ta6NF7ONIEcEUSKI0iG8TCBHBFEiiNIhvEwgRwRRIojSIbxMIEcEUSKI0pHCCwDgC2mH17V5ORPIEUGUCKJ0CC8TyBFBlAiidKzhFU1nyr0zml0VbDH2NBWp1DJtS6IJOxkFP+7GlhNCFYmI4ZXZ6ZKpeSeH2cIrmmMYv9FRWRVsMbrGdqntJRUdddGs6OxczOd9ua3rTBPyqSIR9XSY7nTZ1Ly9o03hlb6qF1xiVFa9Vs9wDlAvKMJ9T19GO55Ef8ptpteJqCKRZnjld7ohcc72q5bwOij4XVBZ9dvXaoL3siql9u60lOtWdThppoW43J9TvQxJFYnUw6u00zVThFeOw1H4q7Syqr5sMJSL/KR4joWTbBB8nOX6niqS0ca80p2umZq3dzThZeL68IrHwKZJL6pI5P+E15xD9oSXicvDK3c/MLijbduoIpm/uPKapXc04WXi6vDK/2zHBBf5VJHIX4TXLNI+MWAfLhvc4Inwqg3Y5+4S5+hrRRWJnAuvzgH7SaRdPVXi9bmy6s0EBs+EV26qxP45/3RxivSiikTOhVdL4py9o+2TVKPJcPH0wsKqFxOU3ZnwitMo+FCcGTHBsD1VJHIyvCqm5u0dbQuv5FFGZh5vbdRmAoenwisd2toV1GyMn15UkcjZ8Cqb4vUgyIIcEUSJIEqH8DKBHBFEiSBKh/AygRwRRIkgSofwMoEcEUSJIEpHCi8AgC+kHV7X5uVMIEcEUSKI0iG8TCBHBFEiiNKxhtepBr7RmqGnnFTlhBNskhlKtIGGPIjSsYXXmQa+U/V8KcuJdizeZ9pAQxFE6ZjC60wD38NbVUO3Cy3KSfc/CCzaQEMZROlYwsvaD6C1ZgD0UotbR9AGGvIgSscSXtZOTIW/MhJyqVVy3XkbaIhAlM6nw2vw12rVUkv6DdIGGgogSuez4TV4dKmldkgn2kBDAUTpfDK8JhjSEUqt2fTSeRtoiECUzscG7Oe4IWqVWm4iBG2goQyidK6eKiE18H39dOb5r/0t1Eqt9BsttIGGMojSsU9S7W3gu08uH/6aa6coZ0+u8uQQ2kBDFkTp2MKrv4FvYVBn1COzJCf/Q5/xI8Vk5z23gYY3iNKxhpdzkCOCKBFE6RBeJpAjgigRROkQXiaQI4IoEUTpEF4mkCOCKBFE6UjhBQDwhTTCCwBgFAgvABgSwgsAhoTwAoAh+QcV9ZFahZrQAwAAAABJRU5ErkJggg==" alt="" />

多表左外连接：

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTA.A=TESTC.A

右外连接：如左外连接，即右表返回所有记录，坐标只返回满足查询条件的记录，如：

SELECT * FROM TESTA RIGHT JOIN TESTB ON TESTA.A=TESTB.A;

查询效果如下：　

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAY0AAABACAIAAABpxLxYAAAFU0lEQVR4nO2dwZXqMBAEnREROSCyIRjymX8AFkuW5LZYvtvaqht4D1a9USOEPDtFnWmaNt/RrwIA9EGyAIA75BQAuENOAYA7z5yaAAAsSXLqsKi0BzkiiBJBlA45pYIcEUSJIEqHnFJBjgiiRBCl45pTt3mapulyvR99Iz+4yLlfL6uv7k6ebES98SumiLAS9TCUMt+OvqsFpjl1m6fLPF+cZLnIuV9zLffrxWkWuoj6wbCYIsJK1G3OK+g2W0WVZU49p+J6Rh6JmZzkvduMqBqWxRQRVqLWORX368Xuw88rp35Kyqq2TOSQU/vwLKaIsBJFTu1nUVBO32g85NS+9xlNQBdREWFbTBFhJar4vc/PlVNOJV+MjWrLQk6U99FdHEWEj6gI32KKCCtRpX10q9WnXU7lyV5YkR6Dg5yI4veX2+yUVC6iwriYIsJa1Ovj0Cap3HKqHOwOvgzkRERln8VpArqIci6miHASVawfqw09r5wqrsxNfiE9XM4TckrEuZgiwkdUkFP7qJjxqK2j5byo5tThhp6YiLIupoiwERVRzymXzz6rnKoGuMUGqEtVVfanjtbzxkOUdzFFhIuoiKjuT3nkeYRVTjVmm0NtuVQVz80omBdTRJiIesBzM8OAHBFEiSBKh5xSQY4IokQQpUNOqSBHBFEiiNLJcwoAwJBgPaWAHBFEiSBKh5xSQY4IokQQpUNOqSBHBFEiiNLZkVPJwZ3SQ4vFS4u/sDqQsRupqgrdMJKjKQUFT3fnlrOEKhIRc6ow6Jop+2NQ3cg5lRycSx8+aFxa/MXZjW1X1aN6kgmWnOotHjC8Xy/TPFsdKP8QqkhE/eTLB103Zd8+uBs1p/IHyBYLh8al1+URkl1dJizHnj8itf5ofFaW15Mvn0EViWzmVHnQGxKt23J2I+bUarTvNxqX3p2RzJ4W6qFRVT+9ekr9jlYfhXnNXa53t0f0PoIqEmnnVG3QLVN/PqdWE+5tr3Gp/d7JUFbpWZ2sayT7g8XLURboVJGMtj+VD7plyr59cDfklMrv51S6XzVMUFFFIt/JqTF30skplV/PqdKC/uSOIoIqkvkf6ymz9sHdkFMqv51T5f/HMMAqnSoS+R85NYq0L++jL987uayOnGrto5e+5tl0RvoIqkikL6d27qMPIu2Dcwmv141LPwwgqyenSucSHq/Lv+8NEVRUkUhfTm1JtG4f3M2uc57JebL0hF7l0osBKqwnp9LgWbyoHkMYYDedKhLpzKmGKfv2wd3IOZX9mFA49draYRlAV1dO5dtQDwUtG+cPKqpIpDen6qZ4bgaQI4IoEUTpkFMqyBFBlAiidMgpFeSIIEoEUTp5TgEAGBKspxSQI4IoEUTpkFMqyBFBlAiidMgpFeSIIEoEUTrklApyRNqi+voOLw4Gnf9s9YutiqoPmr7Dh96MNcgRaYnq6ju8POk4xJNFT5oV1Rg0fYeZinWQI9IQ1dV3eLMJxVmRA33d3ZS+w1ABOSJ1UZ/2S4iIkTo076iorB8QfYcPvRlrkCNSF/Vp/6n626dErqhk0PQdZiq2QI7It3LqtUk8xLyL0J9sTwdN32GmYgvkiHx1PTXSimpHRWVZRN/hQ2/GGuSIfDmnxtmh2lVRyX9Qo+/woTdjDXJEvryP/tdzir7DTMUWyBHZdy5hs+9weaPq9BMvmqJag6bvMFOxAXJEto4F7e47nE7MMeZdxEZFNQZN32GmYh3kiLRF9fUdXl4aYNY92Kqo+qDpO3zozViDHBFEiSBKh5xSQY4IokQQpUNOqSBHBFEiiNLJcwoAwJB3TgEA2EJOAYA7/wDcMf0yP3ZHiwAAAABJRU5ErkJggg==" alt="" />

完全外连接：即返回满足查询条件的记录，也返回不满足查询条件的其他记录，举例如下：

SELECT * FROM TESTA FULL JOIN TESTB ON TESTA.A=TESTB.A;

查询效果如下：

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAZAAAABUCAIAAAAj0/aNAAAGbklEQVR4nO2dUZKqMBBF3ZErckHshsW4n34fjhICwduKesk750+qpmo41dyJodNzCgCAg3D69S8AAKBCYAHAYZgC6wQAYMl6YH01Kg8FckQQJYIoHQIrDXJEECWCKB0CKw1yRBAlgigd/8AaL6fTZfz1b1FgI2e81N/urTz5iJqwK6aIsBJ1Hc5VTZ2H669/qRL7wLoOw+ViJc1ITlyH8+TG7Wl0EnXDr5giwk7UeJnK6DqcvSLLPbCuwzDOnsrf4yOnCqxA1DaGxRQRdqLKwKo//Rz3wBqH4RrX4WzkzEfOcoVlpMlLVERYFlNE2ImqVlhe+W4eWOPlMobZo2gjJ6otBx9FN5xERXgWU0TYiSr3Rr3SKtwD6zoMt9IajXYeXOREsIeVwbKYIsJOFHtYrzJbP9hoM5ETEYsVu9fiwUmUazFFhJuoqorMvhRaB9Y4OO7PmMiJiLXAstHkJcq1mCLCTdQysJzy3Tmw/vYc7h9cisxDzo1lYJnW1s8xLaaIMBO1FlhGsowD67Hn8PfJZWVqIeePxR6WiaOIcBPlWUwR4SUqlntYTnnlG1i3VxX3srpvQDiUmYGcG3S6q/gWU0Q4iaLTvUOQI4IoEUTpEFhpkCOCKBFE6RBYaZAjgigRROkQWGmQI4IoEUTpNAMLAMCQ9cD6Tl4eEeSIIEoEUToEVhrkiCBKBFE6BFYa5IggSgRROi8F1qNjsWpUbF2PqFuNj8zz8lq717YbrwOAO0IViUiBVZyFvPPo8qxbYifM2j7fJx9Y02HI+bHI1vVHAfbyUG7Jadxr003Yju3dAapIZDuwWo350x+62Z886+Ew75MOrNn52uJgZOv69GO9lNrTv4eLe91y4zq2dweoIhFhhbUokbmcwpv1gOP3yQZWVUQPUa3rxYVexKUDa9ON69jeHaCKRF4JrLmbeeb7Djh+n2xgVTX0ENW6XvxcL6WWDqwtN7Zje3eAKhJ5IbBqNZM56wHH70NgpdkzsHzH9u4AVSSyd2CxhzVBqe0ZWMZje3eAKhL5WGB1+KWQPaw0O+5hGY/t3QGqSORje1gdrrF4S5hmv7eEzmN7d4AqEvngW0KzAcfv84U+rIhwmzb+FkpgVfe67sZ6bO8OUEUiWmCtNdgKfVh95dU3Ot1nk3w7qDelcXR5rws35mN7d4AqEtEaR5dS6HRfuwoVyBFBlAiidAisNMgRQZQIonQIrDTIEUGUCKJ0CKw0yBFBlAiidJqBBQBgyHpgfScvjwhyRBAlgigdAisNckQQJYIoHQIrDXJEECWCKJ2XAis53LZoZ+uh63ZTTvNeGZEMLRClkw+s5KGK8sRJHycx23Ka98qIZNgAUTrpwEoeW61OyPVwerUpp3mvjEiGLRClkw2s7GCQ6vhl74G1eq+MSIZNEKWTDaxXR6/df7qDlYRYXtO9MiIZNkGUzjcDq4u4EsurvFdGJMMmiNL5WmD1s4Z4Xl7VvTIiGTZBlM6n97AeHztJq1B6Plb+jSojkqEJonQ+/Zbw9qF8XJf/cftgPOn5WLlXRiTDFojS+XgfVrU5M3tGj8nW9+XGvTIiGTZAlM6nO91nk2372Klpytm8V0YkQwtE6bwUWP83yBFBlAiidAisNMgRQZQIonQIrDTIEUGUCKJ0CKw0yBFBlAiidJqBBQBgyHpgfScvjwhyRBAlgigdAisNckQQJYIoHQIrDXJEECWCKB0CKw1yRJ6Iyg3aLs6J99RcGxG6qObs8br9eKJrVwSWBHJEtkSlD3iVI8R6m4H/RFRjxPjkYWakVNXHTPIZBFYa5IhsiMoeoZ9d7+6Y+LNkLwOoXEzNTsAV4yJ7PlJPYKVBjkhb1GtDih5XO1s0yBVVzgKZu5ln+1rA9QKBlQY5Im1Rr42B/Nuh6WvFECGOhJxvSNUTMidz5RH83tIqCKwXQI7I3oG1/sMdkFhhlbNqm4HFHhYUIEfkQ4HV38aMXlGP73haYHX4pZDASoMckQ/tYfX3FCYq6hFH0h5Wh2ssAisNckT2e0tYPXb/0wpr3sQxRbX4lrC1SD0sBFYa5Ijs14dVv9vv6xl8FliNfxgn9mF17IrAkkCOyKc63ft6AiPR6V59uaPTfe0qVCBHBFEiiNIhsNIgRwRRIojSIbDSIEcEUSKI0iGw0iBHBFEiiNJpBhYAgCErgQUAYA6BBQCHgcACgMNAYAHAYfgHyFEDWHeRUwMAAAAASUVORK5CYII=" alt="" />

（+）操作：(+)执行外连接，将其放在显示较少行（完全满足连接条件行）的一端，需要注意的是(+)操作只能用于列，而不能用在表达式上，不能与or和in操作同时使用。

用（+）实现左外连接：将上面左连接语句改为用(+)实现。

SELECT * FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A；

==

SELECT * FROM TESTA ，TESTB WHERE TESTA.A=TESTB.A(+)；

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTA.A=TESTC.A
==
SELECT * FROM TESTA, TESTB,TESTC where TESTA.A=TESTB.A(+) and TESTA.A=TESTC.A(+);

SELECT * FROM TESTA  LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A LEFT OUTER JOIN TESTC ON TESTB.A=TESTC.A
==
SELECT * FROM TESTA, TESTB,TESTC where TESTA.A=TESTB.A(+) and TESTB.A=TESTC.A(+);

select a.dname, b.ename from dept a left join emp b on a.deptno = b.deptno and a.deptno =10;
==
select a.dname, b.ename from dept a, emp b where a.deptno = b.deptno(+) and a.deptno(+) =10;

用（+）实现右外连接方式同左连接。

用（+）实现完全连接：

SELECT * FROM TESTA FULL JOIN TESTB ON TESTA.A=TESTB.A;

==

SELECT  TESTA.*,TESTB.* FROM TESTA LEFT OUTER JOIN TESTB ON TESTA.A=TESTB.A

UNION

SELECT TESTA.*,TESTB.* FROM TESTB LEFT OUTER JOIN TESTA ON TESTA.A=TESTB.A

合并查询

可通过UNION,UNION ALL, INTERSECT, MINUS集合操作符合并多个SELECT语句的查询结果，语法：

Select 语句1 [UNION|UNION ALL| INTERSECT|MINUS| select 语句2

….

操作符具有相同优先级，多个操作符按从左往右执行。使用集合操作符时需保证不同查询的列个数和数据类型都要匹配，如果选择的列表包含了表达式，则必须为其制定列别名。

UNION

UNION用于获取两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行，并会以第一列的结果进行排序。

select areaid as puid ,domainid from domain where areaid = '01'

union

select puid,domainid from puinfo  where domainid = '120000' and rownum < 5

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAcgAAADqCAIAAACp0EI0AAATrklEQVR4nO2dvZKzOBZA9Rj7uH6UL5/aaOOpcehows062vqqJqT8At4ANy3r90pc4ArOKaoLywKuhDiW1SC7FwAAqOKODgAA4GwgVgAAZRArAIAyiBUAQBnECgCgDGIFAFAGsQIAKINYAQCUQawAIMV1CaNvq6FpKLG73eZlu2hUcKnT6JxLpu+AYjwqpZDsoTvm7RrJbmWv7GGES+C12VUQb+3ceylkQ6yCDbpalYW2eJRYc/TFc2wpykff+iwbOYMWGnM3a+owadV4PfCsjZO2N4j1MBCr7tF3w0Jj7iZXh87d/EW8t/r6NfmpgOVLnN9u4i8+catK5vEThV8P/e8py3rw5SX+LlPNsCRWpRAXvBC28yjE3B1P4YhBSi6eZADJlyp1GNRYvOJnS25ioexxoYK/fthx8YP16tWUK2mwXj1BKmewA8Sa40Os4XteQ8lli/NItkrE4TWmZSVuPdWVYD2XJzx6MfimmFXi6ThW/HdNPH0xF1wTZIiFVajwncseBxyLVbJSvVKEJd3tKugAseb4rH1Bry3XOS23G6Gngo/rXIZkv8NPCbbK7TBZkCClHHB1pTue1mMl5fLqrR8tseYy7CDWl1LbKFwCha537oudpFEdchUwFKBLogKaFFm4eKqJHxmKbqqmrL94cnGuueC74+k4VlIu3fGcQKy6baMq1lxJCymJYx16FXSAWHN4ZyI/xuoPGJXz5FKWxFIoKXJ5klslM8R50kdPDRGWY5Ycqy+eZJch3ipYD/5K4onf6o85U4HJbMt68DfHbmVPlssPLy5jLr3QaRWWNFfhcXq5pLk6VMFFt1vFKRfk2qVfh24D/dxzvXcDAGZBrJ1s8eH/vWesCjA2iBUAQBnECgCgDGIFAFCmWaySu0YAAK4MPVYAAGUQKwCAMt83S2fmzpA85Zm8TZoRAwC4LB9PXlVXXtEYq3ArAIDrgFgBAJRBrAAAynxO0/A5zUph4DXeKthDkA4AcB24KwAAQBnECgCgDGIFAFAGsQIAKINYAexy7Dz81/wVAJUJkRtqzsIjVcmJpSUTTqtMSi3ZQ3+Ex1WvsGbU5/beobz2z3ttt/VEyY+jdKcocrg9JOSsulSO8FdndprdaofrRyXPdpSPPkKD26T2dii45fNe2zadWP4Jvy1StDDezgt91dbaQKw7gVjTu72qWPt+bvp729K6VsoWWG7n5bPQL1bhrCvlSViCXSVfpoIOfz8y+fOZwl+sLOw/SMkdPXm45MtyHmGE5ccxgmzJTcqlbqrVQrkkhS3g/9xpuW3EJS3XRrUGkjEfft67KY8P7C9WScssN1dhm4//JlUTr1RbS/WzrWkc4PVKPdIap6x/pFVyDVRXgvVcntY9x3/XHL0jwleqVsv1nJRUYrd6tdpXrrgIsVglK9VWlzjcCOe9j3iXW/RhpcE0nsrCfjrafHVFeKW8BG71cgryfLxIfVzEifEm8QdLeJjRxDqvxx2T6tG1xJrLMK5YX15r8VPilWqLEnbYyyvHnnetoYDyyz17rOvFmkuJ20Y5j78iF+tL7NZmseZCTL6by3MOsR7eY81lGF2sQcCSa0/YxsI8I5z3PsoSHLfHKtzzdmJ95d3aWj9ey8iPsS5hVfPkUpbEfHnCboKfuLR+QZ6U1j/zLCnLevBXcvT4LWGEifBSY6y5btqy/hI0l45aLZcrrp/coXMl9QOOS51LL3RaS4ezfd77cJ8jfS51G5BTut1KGlJt8qYgT2EPyXT/5aul/fjv+htWipN3q7x+9D9O+9BqfK3fqs6N7iUNAEJMXHhaH+xY1WeL7hIASOCqAwBQBrECACiDWAEAlEGsAADKIFYAAGUQKwCAMogVAEAZxAoAoAxiBQBQBrECACiDWAEAlEGsAADKIFYAAGXcH//+DwsLCwuL4uJ+AQCAKm4CAABV3NFmBwA4G/RYAQCU4a4AAABl6LECACiDWAEAlEGsAADKIFYAAGUQKwCAMogVAEAZxAoAoAxiBQBQxopYf/369Xq9jo7iDFCTZrnyqdmh7Fsfomn/iPVsUJNmufKpQazHcOU2pws1aZYrnxrEegxXbnO6UJPH8ng8nm8ewVtXPjWI1Xuvhe9N/tohaChATR6Ib1XE6oNYvfdcOPdVIXGaJuf+mpetg4ZHdNEuUJNHEVj18fgKMlz51Kwpu3M3f9niEBKE+3fO3e/3ulj9lNvtlkv83mSPHmuys3wdHs/H4+vx+Eq7tVqTDxnSYFT3thFxU1FvPI/H459//vGt+vW1Vqxnat4rxfozHV+vWFu/fHcUYTZhvWHZFOv8geBzmsYnYbFq3CGaqdbk19fX23bP1PJ4zFYQxqO7t42YG20g1iBlDe/iv936tuoasc7X5jyr5zmad7dYnXMqPVbn3J9//ss599+//04u31+7+8W67GG8HuvtdrvdbrNMZ+73+5zYd9yx+LHqCrFOBRt2eVB3bwv+5/9yfvsss2mPdS7486fIb6vG/XTJqflW6uv7p+k1PwAOpE+sTQVfL9aVPdaPBlbOZ02s70+DzytkfafVyDfWMhKrTuKaTNhwhQd19zZlvrj5La17V8mUbj6s+j41j6RVJ9l3Vd+na9y69RBN65kVX+CJzuluPdY5Q3cRGsQ6fXcSFwqJazAg1qdlsQqtOrV0DT5suPo7u+Lelr7q3FH9GLrqEussJl+sQUqB5/OZfevxeM6l9cZnHhmrTsVT46JeqhdgT9nn0/E95pv4u+YEzTtv2lwuVn84dflIVRlj3VqsH4crFrKBv7xFeOyOoK8pVrlVp1P888q36ozv1o5dVSnswRfrx/rcVX0+326tWXUSXPlxL9W//UbRreut2roT8TDIbdwe60dBym+/PikkVg5Ta8ry4ae+yyPJ57X/7BZBam+dWonvo2qy6nSKf14FZ7P7LM/5/b7fMjofdwhzO5nvn1pW3uueVX/6rUWrToIea+DWWKytYwJJt6pYtXVXTd+j+7Ag1ndjut12GmOtNmJJvd9rtJ6bgg+a9qO1t7dDPbe2WnU6xT+v+j4mc/tREavPNE1vnz7fVn0+nl+PilUnWY91cWsQZxCwvBK6VVjdVdMO47LLO6eKPdZZnUmWd/v2Px/C3W7zMPl+/7wqtwlJ0MGo7syaoV5rPdYfhz4fU5dVp1P882o+levv9Mg5dKVYPZ/+WPVLcJaFPdYlHhd9O1y6rk310PflPbeTvoGFpFi9Ei1X9G320uvlthhjrS7jiXVafY9Y0DmdUbmhdRFr3+ZaPB5fvlt/1jcQ62T4n1dTapi1eyexQHPpSZJe/fr6arXq1NZjTXdUXftowEzHv5vizZNWFbrVfo/VadwV4L4b7jC3Wzmvc7podF5Zf3OCEbFOgVvbrTo1/u/yx4Yat/Er7i24MaCPQExN7y6ke6zP53yr6rwI4zmqxzqz5owUvo0Jv5kZGWNNilXxdit3u7m56ZbzTS23W206V4D77Jwu3xb1eqwN39k3JXRri1Wn9juxV/ZlNt3byjGBTcW6jLfKObDHejhr7gpQOcROYp2p9ljlfG+y4QMC7rvTGohV615aO/y4tdGqU9cjLis9uKYjsynJJip8d2GxZyDTVqtOsitzux7rsfTdx6p4iJ3EKumxJum7TbWK/D5Wl5orYMSmVmbur3ZsuP8USl819gxGnWfmPtYOhL0Hv8da7scMxJonr7QOUa7Sat2q3W41pXqgsVi7e6k+zG6lxZXnpjNOU+9hh3j25FLzsVbO3+fjVO/B078+h1PXjKv2BQ1lqEmzXPnUXEqsU/mR1slqjxUKUJNmufKpQazHcOU2pws1aZYrnxrEegxXbnO6UJNmufKpQazHcOU2pws1aZYrnxrECgAAq0CsAADKIFYAAGXeYv3j3/9hYWFhYVFZfsSafCoZAABa6RHr7XarZzpob1WmaZJn3jQ2O5EAgC6hWCXDB/NFrjUYobs3CcLD/fr1a+vYWiMBgCFArFkQKwD0YUus8ZxV8Xo8tVXrTFfC4AOxJo+SizbOltykNRIAGAJbYp08BxVWgvVcnhzC4OMea3CUOB6JWP2U1kgAYAgQaxZdsSYDbo0EAIbAolhz3b1BxRpnnhArwKmxKNYgw1hijd9FrABXA7FmURRrrhveGgkADAFizaLbY03mb40EAIbAlljjnp2fHuQJBjSTGyYRBt93u1X8txB5ayQAMAS2xLoPwsPxgAAA9IFYsyBWAOjjQ6w38Jh1ZgHECjAWx89u5ZxT3Jsu9/v96BDe2IkEAKqUhgJy/wuaxfo/JRCrBDuRAECViliTbj2BWKvDmnM2OzqzEwkAVKmLNXZrn1idcxKxLgcNEnWL3S3WarRaKQGIFWAgRGIN3Noh1nknVbEm18vG6aNPrLENg3WtlBjECjAQUrH6bt2uxypZV0Glx4pYASBJg1gXtyLWXCJiBYDXxXusLv84/5xNItYt+rAxiBVgIHYaY7Up1vU91sJLxApwWWzdFTCWWCW9V8QKcEF2uo91cXRZrK+uW5E66BNrEEkwVCKPv6OMiBVgIHjyqoQdndmJBACq9M9uhVj3xE4kAFDl+NmtnHN3EHB0UwEAKSZmt/r9+m1zud/vh8ewRKJY5wCwKSaGAg7XFmIFAEUuKtZqGRErAHSzk1jLt1v5BllyBolGxOpHEtxulYtfklItI2IFGIg9xOr7NHZrYJx4PXbQUWKN/VjWrjBFUkbECjAQA4i12pvbTazVqLrFWi0jYgUYiF3HWKtDAcOJNR4HQKwAsJ9Yk1Y9VqwuP7uVRKxxOmIFgNee/7wqvHWUWFf2WBErACTZe4z1TGLt0yhiBTg9O4nVpyDW3+Kbkw4RqyQ2rRTECjAuF31AQLjwgAAAdIBYESsAKMPsVsNwdFMBACkmZrdS3JsudnRmJxIAqGJiKODQGihhR2d2IgGAKhcVa7WMczY7OrMTCQBUMTG71UdA3wSJusXuEGtw01gu2r6UahkRK8BAmJiE5Sea1HrsoPX0iTWsO70fu66WEbECDMTeQwEdYn2Z6bGGdacn1uT+fRArwEDsOglLdSjAuFjjcYAgQsQKAK+L91hdfnarOVtOZ2s0ilgBTs+lxdrUY/2oNcQKAHkG+OfVy4ZYtTSKWAFOzwC3W8Up6+kQqzA2rZQAxAowEBd9QECIHZ3ZiQQAqiDWEnZ0ZicSAKjC7FbDcHRTAQApJma3Ony2U+ZjBQBFTAwFHK4txAoAilxUrNUyIlYA6GZXseZut/INstx4FCRaEGscm1ZKtYyIFWAgTMwVkJTLsh476BCx+vHkou1LkZQRsQIMxH4PCEh6rJL1w8VaiLBbrNUyIlaAgUCsDWINvsIjVgBIsutcAdbE6vKzWyXFqqJRxApwevaeKyB260A9VsQKABJs3RWAWBErwAnYW6zlHutv8c1J+4tVGJtWCmIFGJeLPiAgXHhAAAA6QKyIFQCUYXarYTi6qQCAFBOzWynuTRc7OrMTCQBUMTEUcGgNlLCjMzuRAECVi4q1WsY5mx2d2YkEAKrs9ORV+QGBMKb2nxeUpwiLOWeLdbZpbIXPGMQKMBB7//x1tccaGydYX58iKeac7Z76ldZNY8u5FbECDIQ5sb7yojlcrDvEhlgBTsDeQwGtYo0T7Yh1i9gQK8AJ2PufV7FbJWLV7ScuxXT52a3mbBKxbtGHjUGsAAMxgFgLLw/vsarHhlgBTsDeY6ytYpW8a2GMVSs2xApwAvaejzX51kdA3wQvk4m5rQopwmLO2ZJ3BWwXW86qcSQAYJmLPiAgxI7O7EQCAFUQawk7OrMTCQBUYXarYTi6qQCAFBOzWx0+2ynzsQKAIiaGAg7XFmIFAEUuKtZqGRErAHRj4narQCKu/ecF5SkrxbppbIXPGMQKMBC7PiAgEWtsnGB9fUq3WHeILedWxAowECZmtyr0Ck2JdYfYECvACTAxu1VBXnGiHbFuERtiBTgBJuYKqMpLt5+4iNXlZ7eSi3WLPixiBRiaAcRaeHl4j1U9NsQKcAKsi1XyroUxVsQKAAvmbrdacgYvk4m5rQopa8S6dWw5qyJWgLG46AMCwoUHBACgA8SKWAFAGWa3GoajmwoASDExu5Xi3nSxozM7kQBAFRNDAYfWQAk7OrMTCQBUuahYq2Wcs9nRmZ1IAKDK3o+0xndcxWJ1xZ8XXJkiLOacLdbZprEVPmMQK8BAmJjd6iOgyDjB+voUSTHnbPfUr7RuGlvOrYgVYCB2HQqQiPWVF83hYt0hNsQKcAL2E2vSqlWxxol2xLpFbIgV4ASMIVbdfuJSTJef3WrOJhHrFn3YGMQKMBADiLXw8vAeq3psiBXgBFgXq+RdC2OsWrEhVoATYE6s7pvgZTIxt1UhRVjMOVvyroDtYstZNY4EACxz0QcEhNjRmZ1IAKAKYi1hR2d2IgGAKsxuNQxHNxUAkGJidqvDZztlPlYAUMTEUMDh2kKsAKDIRcVaLSNiBYBuzP2Y4O/Ub0rPqKSsFOumsRU+YxArwECY+/nr2DjB+vqUbrHuEFvOrYgVYCDMibUgmsPFukNsiBXgBAwwFBAk2hHrFrEhVoATYL3HukU/cRGry89uJRfrFn1YxAowNAOItfDy8B6remyIFeAEWBer5F0LY6yIFQAWzI2xLjmDl8nE3FaFlDVi3Tq2nFURK8BYXPQBAeHCAwIA0AFiRawAoAyzWw3D0U0FAKT0zG4FAAAFECsAgDKIFQBAGcQKAKDM/wEAOqKT0KaQjgAAAABJRU5ErkJggg==" alt="" />

UNION ALL

通UNION操作，不同之处该操作符不会取消重复值，且不会按某列排序。

select areaid as puid ,domainid from domain where areaid = '01'

union all

select puid,domainid from puinfo  where domainid = '120000' and rownum < 5

INTERSECT

用于取两个结果集的交集，即只会显示同时存在于两个结果集中的数据，并以第一列进行排序。

MINUS

用于获取两个结果集的差集，也就是只会显示在第一个结果集中存在，而再第二个结果集中不存在的数据，并会以第一列进行排序。（相当于union结果集再去掉第二个结果集）

巴特西

oracle中的连接查询与合并查询总结

最新文章

热门文章