IN 和 NOT IN 是比较常用的关键字,为什么要尽量避免呢?
1、效率低
项目中遇到这么个情况:t1表 和 t2表 都是150w条数据,600M的样子,都不算大。
但是这样一句查询 ↓
select * from t1 where phone not in (select phone from t2)
直接就把我跑傻了。。。十几分钟,检查了一下 phone在两个表都建了索引,字段类型也是一样的。原来not in 是不能命中索引的。。。。
改成 NOT EXISTS 之后查询 20s ,效率真的差好多。
select * from t1
where not EXISTS (select phone from t2 where t1.phone =t2.phone)
2、容易出现问题,或查询结果有误 (不能更严重的缺点)
以 IN 为例。建两个表:test1 和 test2
create table test1 (id1 int)
create table test2 (id2 int)
insert into test1 (id1) values (1),(2),(3)
insert into test2 (id2) values (1),(2)
我想要查询,在test2中存在的 test1中的id 。使用IN的一般写法是:
select id1 from test1
where id1 in (select id2 from test2)
结果是:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAacAAACHCAYAAACyNJYaAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnQAABJ0Ad5mH3gAAABhaVRYdFNuaXBNZXRhZGF0YQAAAAAAeyJjbGlwUG9pbnRzIjpbeyJ4IjowLCJ5IjowfSx7IngiOjQyNCwieSI6MH0seyJ4Ijo0MjQsInkiOjEzNX0seyJ4IjowLCJ5IjoxMzV9XX0utNM7AAAg+0lEQVR4Xu2dabAeVZnHz12zESAbYGQPYQdBMCyJSJXABIFBkYoDgqJC8cUPVoka8cvEApnRD4IFZLBqkCSoNSSSEB2WsgQHiCQIpADBYpFFCcMWQsh29zvnd/r+b5rXN7lvh5dJS/9/1FN9+vTZum94/u9z+nR3y2AkGGOMMSWidWhrjDHGlAaLkzHGmNJhcTLGGFM6LE7GGGNKh8XJGGNM6bA4GWOMKR0WJ2OMMaXD4mSMMaZ0WJyMMcaUDouTMcaY0mFxMsYYUzosTsYYY0qHxckYY0zpsDgZY4wpHRYnY4wxpcPiZIwxpnRYnIwxxpQOi5MxxpjSYXEyxhhTOixOxhhjSofFyRhjTOmwOBljjCkdFidjjDGlw+JkjDGmdFicjDHGlA6LkzHGmNJhcTLGGFM6LE7GGGNKh8XJGGNM6WgZjAylTQMsWbIknHjiieEjH/lIaGlpGcrdCpezXv7/J/qTtra2hoGBgZQmr7e3N9kuu+wyXIZtf39/GnN7e3tK9/X1hba2ttDR0ZHyqUPe6NGjUx3SoPJAX6QpO2bMmOF69E+atthvDS2p7e39LhqMl6+rqyvVHTdubKw/dMAYUxkcOVUcRAWxYAukOzs7k/AgDhh57G/ZsiUJk+pI4ChDOl8OcQH2MaAMwrMtOLa948aY6mBxqigSFqIaxCYJRzRFTuSz393dnSIeCRjHVVblOMZWaY5J7ATtkFebb4wx9bCnqDASGUQFiIAQH7ZCYkUeU3OKkoAt+/n65FEOdByjHeHoyBgzEhaniqJoSaKhyIcpuFGjRqU8yowdOzaJDZEPIiVh4hhR0ubNm4cFii1RFuV1z4hybGmHvvIiZYwx28LiVFEU7SAWCI6m7RCXjRs3pmOAyCBKCBb3ohAZypJH3fHjxyfxkVEfWHSB0LGPcUx9GGPMSNhTVBSEBYHSVJ245557wo9+9KMUESFaiBJG2Z6enpR33XXXhXPOOSdcccUVw1N+iBhtUvbdd99NAiehAupzXH0xtVdrgtgsb8aY6mFxqiiIBEJDJINwkCbvL3/5S1i1alUSEiDaIV8LGuCggw5KURTlVJ/tE088EW6++ebhcuRTH6N99jFjjBkJi1NFkSAhQhITRGi//fYLM2bMSBGVjmOkmaZjO3v27HDGGWcMCxP21FNPhV/84hdhwYIFSbh4JkrtahpQ5Y0xZiQsThVFQsNUnUA4PvvZz4bvf//7aXoOsUJUEBcerJVQaXEEYqOpu6VLl6YHlMmnTUVcKkc9kCjWtXi8dhovZhtjKojFqaJILBAR4B4TICjcP0J0KIOYkMc9pLfffjsJE8JFZMVx0rQxd+7ccOWVV6Y2ECSOUQ8jTSRFvhZHyCRMtSjCqnfMGPPhx+JUURAUoidFOIjBNddckyKnefPmJVFAgASREyv1vvGNb4Szzz47zJ8/P5XhbRAIG6LDPm0iRLRLfYzIiWhK/YGipLyJgahLeTPGVA+LU0VRZIRwSIimT5+ehOqxxx5LUY6iFgRlzZo14aqrrgr7779/OPXUU9OiCB2jDeojPrRJdEVdjmG0qZV+ioiMMWZ7WJwqiqbU2CIoCAvLw2fNmjUcSSEopBGqF154IVx77bWpzLe//e1w+umnpzKIj55/YktZ6qhdTEKlfOrJQFtjjBEWp4rCvSIESeLDdty4ccNvHuc40RUCxT0opu8UJSEmbDVdh/hgCBHwAC5lqI+RZlowtLYMP9NEngzYehrPGCMsThUFQUJQmI5DOPTGcQQIodiwYcOwWLGdMGHC8PJwCRZiRN18Wwge03qAwBFNIVASN8wYY0bC4lRREBZEBJEhakJErr766nDTTTeFJ598MlxwwQXpPhNig/jsu+++YfHixem+EwsieNj25ZdfDqeddlraskDixz/+cXj22WfDueeeG5577rlUD9PqPkVdipZE7b4xxvhjgwX5sHxskHzS5CMeiNXy5cuTuFCPiOiiiy4KEydOHBYZop5FixaFN998M4kW5YiwLr300vR2iMcffzzlUW7OnDlh8uTJ6VooemrraN96bQb//ndRfkoP4VRk1tlq8TKmalicCvJhESfGSBoBQJwkQOwjLukeUUT1Ka805UjLWAxBHhAhkUc71Mlfi5a27D4VeYN1bi5ZnIwxwtN6BcHJb89EvWP/n6YxCNIDIbPB6OuVZpECRlTDNglIe1sq0zfQH3r7+0JPX29Kk491jo6R1uBA2LRlcxg3PgpdrNMxqjMZbXb1dKctZWmzu7cntHdmn3xHuBAmNCunW9n44jGlRT5tjKkOFqeC4EAVYeA4MSIQRRPs6zj7I1nmfPkzNMe0/BtIpyhlaJxY1JhMHGLZKElRZFqS9fQNRAEZHYVnbBSVUVFcWqIgEe1k94o4J9C5ESHxuQzaJ0/nTcTDAgogj+NMAQLj6u3pD33xeqFLmIIijvV194RW9DKarg1mjKkeeDRTAKbAdIOf6S8cN84X0z5b7Y9kLS0xuoji0BSLdEaBaW+PEUwUlt5elnYzHu71xL6iGDE+ZKEnigTHOjq438RzSCwrj83EdhCwTRt5C3n2UG1XT18SFayttSN0bekJPd19ob2tM/VLHm2n9luiEMW8zo7RqfzmTV2pPfL6+wZjnWw14JZN3aF7S29spz/090YhimWyKb9YNkJrEipjTPWwOBWEX/SKihQZvB+oj140x4amzeK4cPQIaPaevL6hfrbeg8rEMRMD5VFP59Y3FAWRh9DSFhEi0Zjahk2bslcXUR4o29OTlQPKUb47RkW6VvkxcIy6tEH6nXfWD9d9v9fWGPOPixdEFOSXv/xlOP7448PUqVPf41zl/KHIJc0ip6ze+6Zlq8gAY0KcFKX1RAHgBa4TJ2ar6BCJfu4NxXHnBWogRlLQP9AXxWUo2urPjlMOARquH8UMKEMe9emL55pYpUca4aEOIDdJjGLklZ6bisdpI4lbDJMQJvJpC2ijM47BGFMtLE4FYSn1cccdF/baa69hp6+oQc4Zk3MdCabRmilOrG7LRziMD+fPmBAnvlK7YcOm9FDtpk2bQldvT5g2bVp6LmmPPfZI0dLGeJx357308otht93Gpxe7blz/bjjiiCPSa4x23XXX1AdtHXXUUeGZZ55JeVwHlpkfe+yx4c9//vPwJ9yV9/zzz6e8wZj34gsvh0MOOSQdmzRpUhrr8y88Fz7+8Y+n56sQKMaDqFmcjKkebf8aGUqbBsAR77333snJSpyk72xxxhKnkQQqq0eZxoRsRFoGU6SkaTilERzuk42PAsJzSeec889pKfz9998fblmwIHziE58IX/7yl8Puu+8eVqxYEW7+z5vDzJkzw4UXXhDGjRsbHn300XDD9deHT3/60+GrX/1qOq/Vq1eHn/zkJ+Ezn/lMeh6K64DA8SDvmWeeGS677LIkanxZlwd3ybv88svD66+/Hv7617+Gf7vm31O9r3/96ymPz3HM/e7ccNJJJ6XPvyN8vOePvph+NMZUC0dOBeE5J6IA/apHBCRIwBZrRJyARQeNlGsUoiRNtTE+IhIiGvoYiONiWm/16sdTtER67TvrwmGHHZZEhAgGQduyuSvlPfLoH6OI7ZnOZ8M761M09fTTT6dz55zfeuutFP0g2FOmTEnlEJrDDz88/OlPf0oCSAT06quvhiOPPDI94EvERrlnn30+nHzyyeHFF19Mz0lR7o03Xgv77LNPyqOPAw88MJ1HR7vFyZiqYXEqCK/wOeaYY8Kee+6ZnD8RSR5EoMglzYSpuc5X94IYx6hRvCk8W+TQH23dunXh+utvTJEMAsSDr5Rj+TeRDunOjuwruERibFvillVziBznSyRGPufP9B5REyJIXUzHSHN+HBe6PqwkRAgRJuW1tWX37X72s5+lz3cwRnDgZEz18P/2OwjOVJAuiwHOXg6/f2ghAyAaCAwRkwRGZUnLKKPoizbYxzhW24+EKQ956l9tCu6F0RaREpCmLDAu6rCYAtEje+iQMaZiOHIqyLJly9IUFdNOTOmxmgwHmyfvvAV59fYzR97cG/6Mh/aJmlhGjnggEFtipMI9p1deeTVNubF4gsgpbeNxBAFRao3jIa+7p2v43BirpgiJntSmhEnTifTLNUFgKEcEpRV40LVpcyrHPudOmutIu+vWrU39Ml3I9N/UqXulOtYnY6qHI6eC6CWmmfMflZw2UQhbjum5HgkP+2xxvtoHHDtpyjUTnnWif4lGR0f2VnFgfGvXrg2XXHJJWgzBeDgHhERTeuwzLvIYm0xtIirsM36MtjGJLWUkOBxHmIApPKylPeuTMqwWpHxaDRijpiSI8Vp+85vfDAsXLkz1jDHVxJFTQX7zm9+EAw44IC0l5z6NnDYmR00aAWOLk8ZwxnLmgDBgcv7Ngj7ykRhixdQef2YWRCAQLIhg4UFa/j2YHZOgAM85JeEa3ZnEhTY5F/KA8ab2Yv72qPdPiz64NqzG474d/TIm2hoVhYvr89JLL6UFFgccsF+q48jJmOrhyKkg/LLnFz438nHYmRhknzTH0TKNhSmKIo+oAIcs4SKfLXWa/duAsdAnfeVFT2JClPLrX/86raCTKCE6jElTb4yLc/jhD38YHnvssZSXh3qNjLue6CJE9LnbbrultMbJGOiTcTzwwAPhqaeeGqphjKkiFqeCIE6AM0VgcLSAs5aTRYiIsFgSjfNVhKEytelmwpggLyCMUSLKluk0LYigPEIKlGefZ454E8Ytt9ySlpg3Os565Wrz6INrhMAjTpoSRZRUFnHkuhljqovFqSA4b+7H4FQlADh53atZv359WLlyZfjWt76VIgBg+o/jOGXK43gRDEShUcffKIxFEQh9xO5SfxIexsJXbpnWk2CRTxndZ3rnnXfCjTfeGN7dsCG9XihJXMxnK2OKML8vo1xtWeVho2J01h7HxuIMvboI0hgGs9cpnXXWWekVUcaY6mJxKgj3SfhYn6bvmN7TtB0wDcbzOURNCAFl5fwRCOogSuQROZDXTBiHxsJiCASIKIX+GCefVP/Sl76U3vBAHlEUgsq4eOsFosZqxN/+9rdpRZ8ELM/7GTPTivQJXDtN7yGITCti3/nOd1LUZoypLhangvAQK84Up40RQREV4ehx4rwK6K677gr77bffsFCQj/PPT68RRZFutjhJNIGHb2mffhkfjh/BufXWW8OMGTPSOBBQvbVBYyPNeWULKQh4ODf+qSBSHEeg3ms6Jqutk1lIAogoIVD0xfVhvFxDTfP94Ac/CBdffHEqb4ypJhanguDAcaQ4VNKIjO7ZIAJESry8lIhAwgA4YgSCPEwCxbaZ0Afjol/1yVaGKCxdujS9hFX3dbjHw1gU3RE90QZb8oXGXg+1X0ttnhaBcJ145opxci3VP/3ee++96fVHxpjqYnEqCA4bhyuBwbHmHTPOFtNxlcXpqqzKqK1mQj8SENrWc0+CMUggJJZELKTzQiRRE42Ok3K1ZZWH6dwZh66RxIl9bTkPY0x1sTgVBOeJM2c6Ss4dZ4uT1T4RgBZMkK8ycsI4XspIrJrFYGyqfzA6dcZCP7FfZAojDRMnTgxz5pwfDj74oBgBsqSdXKYos283QVf3ltAe0709TO0RXRExsbKPsapFzi11NXw8b/lyWy2KY0fssIW3VWwKY8fxlggaoLyepWoJ/zT79HDMsUen8saYamJxKoieASLqQXS0L0hzb4dpPVCUoigJYxqQMuQ3G40L8dSnJhBC9c2CCD5nsWrVqlSOPOowzsx49oiv1jK2KKL9PaG3Lwpt6A+do9qTuJBuaR1M+xj7A4NRxPjY4QiWj9AYI9eCLSsFOzuzqVI+mbFgwYJUxhhTTSxOBZGo4PAVMQFOF8jnXgor4MijPFvKcoy6OGAc8lao2wQbbA19vZkAIDh66StiwDgRThZq4PhZqk3e2FGjQzuLFwYG03vvePv48888G86afWZ61RHfbKI8K/44Lz4OSPsYi0M4D47RXyPo3Hu7e8Ju43dN14V7eIg540SkWBDBikJjTHXxxwYLwreL+CgfN/T1nBLLo3GsGMfnzZuXlmq/9tpraWUa3zxiIYKWkIMcfEYmcM2A8eDwNY2YaefW+168Nmj+/Pnpg4m8goloifEjEBofZXm10ckzZ4aZs2amT4TwoludL2UkdmwluqRHgnFRf2wUNKY+GRN51GfakDYW33ZbavPQQw5NdZp3dYwx/yjIO5oGyZxoJixyxmxxsnLSOPbPf/7z6eN87CMAqsO+7INADj7fB+NifBonxjFtEVXSElhEi6XcF154YXpg92Mf+1iKdjim57aohzixlbg0AmOgPnBd2KddYJxKG2OqjV/8WpDly5eHo48+Oj0vhCPFaRN14KBxtsBUHpGSpgApQwSVFwaQYDXrkxn5PyR9KHoiQpEIECU98sgjafxEQ4wdmIZkvJRnjIm21tCbFkRkYwWiJ9rmXBGT/HQefYwE7dA+03r0RX22wP0ujj0Xo0/GNu3AaSnfkZMx1cORU0FYqYdzzjt/RQPce8HIJ8KQ80WYECh9TA8HzzGt6Gsm9INoAG+IoH0EiPEhmCyI+NrXvpamHRkD5REmHsalHmnu+2T3rPr5GG4SLc4BYaMMdTCYsPvu7+lzJBA1xqR7TNTl3hbv89O1vOqqq9IXh40x1cWRU0HuuOOOsP/++6d303HvicvHr32cP84cZ0saQVDEghglRz8UWVCGfYQMmhk5MR76Yas+ZQgIixp4tdKkSZOSIDFWiRQilKKaoXRX79YXsCIkCBbHSVMPoeEeFkKjPkeCuowLoePNFLSneoOD2ecz/nfNmjB58uSwz977pHxHTsZUD0dOBcEJE0XIoeNocdQ4WUCI8k4cI41RDgPqYc2G8alt9SdxwoieeH3R3/72tzROiQXjQzA4J86NfUSDtCI9hEplgPo610aECShHnwij3hBBuwg5W0Tx7rvvTlOPxpjqYnEqiKIKIiMcLSZxwvnjxCVAOGGJA1uJmcqxbTa1IlG7QAIxoW/GBhKyvIgCQkG9/BipX1uP9oqcB3VoR9ePftjm29AYjTHVxeJUEKayJEQSHhysHDtpRRZy4HK+RAe6zyRxUp1mGeOjT8jGlE3vaUy8IeLss88OU6dOTXmUR3AQBESXNOUZG2kJCGWJoHhxq6YpdS7UlSBTT3BM10XXQe1Rl+iJY1wLTXHSz8yZM8Nhhx2W9o0x1cT3nApy2223hWOPPTatJsMRY3L8pOs56e2Bc27kN0IjfyZKyPkjLHL+LMRgHwHg0xRnnHFG+N73vhdOO+20VA+x4Rwoy5axcz+tuy9LS/iIdiRe7FOe/hA1xAbog/tGCBYLKyjHNZEYkaY+UCY7/4z+/mxK8eIvfjGccsop4crvXpnyG4/LjDEfFvwQbkF4WzbPAeFocdw4a8DpkiYPk+MeyajT1pZNBTbDJCC0DYwD8UAIGBf3y1gKj8jytgfyLrnkkvQsEx8Y5F4U5XnG6ZRPfjJcd+214cnHn0hvc/jCnDnhxBNOCP8xf3545OE/hsmTJoV/+cIXwkknnBhuXbgorHpoZdhj8pRw3uc+F06YMSMs/q/bwooHHwzTDzooXHThF8MxUdSXLFkSfve736UHez8Xyx166KHps/EsNDnkkIPDpZdeGi6+6KJw/vnnh/G7jE/nYHEypnpYnAry9NNPpykxHrTlVz6GGCiNIRL5/e0ZdVtbs3tT27NMxLZvvOwVsaG82kekiIw4TlTEggbeDsH0Hg8Jk/7oRz8ajjrqqCS6TKftu+++KTLk0x+s6jv44IPTa494Noo8VtKRx6pF8qhLHu2xipHrg9iRN23atNQHeUfGclOmTBluj2tIOT7gOH369GgHpSX4n5w1K5XnPMDiZEz18LReQfjlj4PGKUtciE4UMUGRS5qVbc6tP03rYYyFsTGdloQrOnrSPMeEQICm1DiuehLDLK3z0f2rbAVivo98PY4zZcd10RayKcHO0DM0nUc58jiuNqjPG8nT9GA8TqTX2ZF9J8viZEz1aI5XrBgSIhmOlW0eOe2RTGLQLGtvz4RIICYIBYKAGDBlB1nZLMojoqKcxIVTQZiyxRTZ9CTnh6BwXPebADGhDZ0/W8qQRz1AaEhqXNTH6JMx0BYLM4DxqQ9jTHWxOBUER5o3HDBbHKqcKs6edG3ZeiaRap5lwqjx8JYIBIO+AOcvwaKcxIQ0IpIdY1zZyjkWNSAciNWYMZmwISiZ4GQfKuQ4aYkU04j0q2lEBIzrwpZy3BejPvVUFxgHUFbjNcZUE4vTDqAoQci5slUaJ9+INRs5ewREzj5/z4kHXxEcQVnORw/EAsLAKVIPMWN/48ZNw8eAsrTHfSPERiLHcdpcv/7dVI7Ve4xFgsZxylEHMSPN2OiLNMKUj8SMMdXE4lQQOU5t80aeDAeNIx7JJGbNIi96OPvNm7ekfshDHBALhCB/HggH+UQ2iBvl1659O4kPx7UC8O2316XxSoRoh7rUoSzt0QfbvPggOBJNGZEYZSlHGfqmD41H52CMqSYWpx1EoqKtkFB9EKjt7RlOnTHh8HHyOPutgrD1PpPy1C5CwTHyWE7+05/+NH1o8LrrrgsPPPBAOqb2qY+xTz+gfbWpaI08HQOJTk9PJmZ8rTd/HNEzxhiL0w6AA8XkiPPwkOuDDz44bLxkVU6bLSZnvNURs/1741PpeatX5j029Cn0/oHe9Nl0PqE+ekxn+qQ6++Tz2fUoXcNlKM8+RvnXXn81/M/994WHVq4Id93932HZHbeHu++5Mzz19JNh9wm7ps+0Y61RY2gLGzW6I4pRFK/YT75dylBWn3Nvj//aWJGH9fbyoUEWPbDUvT1GUNk9rMEodghevWtrjKkOFqeCEEEgKogL0QYoQmDq6uGHHw7nnXdeesD03HPPDTfddFO6x8O0lQRNxpsbdPM/n4/tKNRljLqHo/YQREVVEkjQebBP+aVLl4Zf/epX4fbbb08Px/ISVp45uuKKK4brCtL0o7z2tiiGfPY9btuiMsla+Qx8/I/8jrjfGcc3fty40BHHE69c+kfYHvsf3dGZ1R+K0owx1cXiVBAERfdgEBzBgoKFCxcmp/773/8+rFq1Kjz00EPpYdfLL7883dORw5VY6JtGzYLgqlFri4LRHoWir6c3rH3zrTDQFyOn9ii2A4Ohp6s7Ckd7GDt6TBgTxYLyXZu3pHyOU5YtbXRv6YrRThRr+m/U8mPJ79ceH9o3xlQPi1NBeG0Ry6f59ARRD0IDiM+6devCmjVrwhFHHJHegoDxuqDXX389LakGRElRiqKpnQHjZhED4+CtDJwLdtZZZ6X37nGM/euvvz688cYbYd68ee+Jkhg3Rp6iMGOMaRb2KgXBOSvy0fQTDp4IaMaMGWkqj+M47jvvvDNFWnPmzEl1ydcxtbGzpq8kKIxFY2DLa4RmzZqVhIlIkPfvHXfcceHUU08dPl+dB2naYTqTa2CMMc3C4lQQoh2cMlN7GOCYsU996lPhsssuS2W493TPPfekqTteZsrzQtSTMJHWsuudAaIigSJKQlzZ5zwQJBZz8IJWzolPbFCGcXM8L0ycD3lsjTGmWVicCoJDZoqOqT05Zhw7UQUQcTC9xwKCk046KXzlK19JjpzjKkcbOPidOa0H6luf1uBcOLeVK1em+2RM7yFOjJ+xUwaRYvwSMs5HD9oaY0yzsDgVhAiID+6xxUHjtDFFFHxenEhj7ty5Yfbs2akc0144fcpgiBKGU6eNnYFEhvExJqI4xrlo0aLw85//PNx7773pjeKAOOneEufKeSJUEjTOha0xxjQLv5W8ICyz5jMPfFICZ43jRmRwznyT6L777gunn356um8zYcKEYectIQCcOpedutpieerlNUL+z0mafhkDW/pFJFkAgRCRR8SDUFEG4bnhhhvSg7cnnHBCOo6Rz+cwiAYlyuonfw7kN0J+jNRjXKD6LDahT10vY0z18PecCsL3nPhOEQ5eU3RyqiyAQKD4PtLq1avDihUrwh/+8IfwyiuvJEEDCQYmp1xPiOrl7Qi0IfFgixARBQmNhy2CwJg4N76Ey7ecuGdGmm8yHXnkkcMr9jCdNyivKBqX0qCozlOFxlQXR04F4Tmmww8/PDlr7tUAUQeXcfny5eHWW29Nv/zldHH4fPX16quvTvejyJM4kcZqnbr2a/MbgfZqQXBoiy0PBCM+9M94yENwqMd5qKweMCbCYl8RFvnqgzYoz77OaSRqx6dxgeo7cjLGWJwKsnjx4nD88cenjw3qzQpcQmxHxOSDQH9SxoYAaVyID2KTfyA4z7bymnle9f656fpJnJh6tDgZU21G/qlr3gNOlF/6OHpFHqRFUWeu8s0yqCcA+TISAYmCTFFQrTUTXS+MtrWvvvPX1RhTXSxOBck7+Tx5x066UVOdZtm2yB9nK5EayaDeuHfU8v0qTZSE1R4zxlQXi1NBcJr1HKzSOH454UbsgyDv3D/ovopSbyz5PKVryxhjqoXF6X0gJ5oXJznVfP62TGWbicZQr+18niKpkSxPvePv12qpl2eMqR4Wpx2AyCh/byR/zwQQAdLkbc8aKVPUNC62ap9t3vICNpLRDqv0MNpVWsYCiyLGeLYHfX5Qwm2M+cfBq/UKwlLyo48+Oj3rJEePw62NhkZywoLyzXTEeQHiPg7iwpaxSWhYAq97PLU0cyzbgvExFvpiXBozaUDEGJ+WsxtjqofFqSDLli1L4sR3mhAgHCyONi9MRS6pHHKzYAz0z1biRB8Y40Wc+IwH1I5T48+TL6N0vbx6deuh860dlwReY+a5qvzDwsaYamFxKghfiuVtD1OmTBkWgLyDJi1H2wiNOvVG0RjYYhJQpYlKECfK1P7pKVNLbRnt126hXv1a8uKk6I10XpyAtN8QYUx1sTgVZMmSJekh3L322is50vzUE5dShnNtxFl/EOT/pPk0AsB9o3oRybbGui3Bqu2j0XOlnOrrGiFOamNnXjdjTHlo7pxSReAXPaJU75c9jlURwc6CvmU4exnj1Ytbay1fJ2/1ID9fl/PN72/PVDd/jUgztp193Ywx5cGRkzHGmNLhyMkYY0zpsDgZY4wpHRYnY4wxpcPiZIwxpnRYnIwxxpQOi5MxxpjSYXEyxhhTOixOxhhjSofFyRhjTOmwOBljjCkdFidjjDGlw+JkjDGmdFicjDHGlA6LkzHGmNJhcTLGGFM6LE7GGGNKh8XJGGNM6bA4GWOMKR0WJ2OMMaXD4mSMMaZ0WJyMMcaUDouTMcaY0mFxMsYYUzJC+D9OUmmTTKwDYAAAAABJRU5ErkJggg==)
OK 木有问题!
但是如果我一时手滑,写成了:
select id1 from test1where id1 in (select id1 from test2)
不小心把id2写成id1了 ,会怎么样呢?
结果是:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAANwAAAB6CAYAAADUHz1yAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnQAABJ0Ad5mH3gAAABhaVRYdFNuaXBNZXRhZGF0YQAAAAAAeyJjbGlwUG9pbnRzIjpbeyJ4IjowLCJ5IjowfSx7IngiOjIyMCwieSI6MH0seyJ4IjoyMjAsInkiOjEyM30seyJ4IjowLCJ5IjoxMjN9XX3Rt7v7AAAJ6klEQVR4Xu2duasUSxTG67rvoiZqJkYGImaKiRoYmRmLmamRofhHiAuiGIriioooiGAkmCriLgZqpuK+v/cr3jcUzcydnmv3sefx/eDQVdWnqk+35+uq7un73sTvf0nGmBCm/bc1xgRgwRkTiAVnTCAWnDGBWHDGBGLBGROIBWdMIBacMYFYcMYEYsEZE4gFZ0wgFpwxgVhwxgRiwRkTiAVnTCAWnDGBWHDGBGLBGROIBWdMIBacMYFYcMYEYsEZE0in/zN5Z86cSXPnzk3fvn1LP378yOUvX77k8rRp09LExET6+fNnr1yHun7D4LJh06dP75V//fqVY5szZ0769OlTjmvmzJl5H6hc+tPGlvNgrHnz5qWPHz+m79+/5/6zZs3KMb9//743npgxY0becn3ov3Tp0vTmzZvsw1iMOwyOQyzEDPTheLpOO3bsyFvTDJ0W3M2bN9OKFSty0gGCI6GB5FPSklx1aEpswLEx4ijrxEe8nz9/7glGIApikBi4eZDopeAQ0devX7MfZbbaz3jluXIs9stHbRwTP+IZBv6MLcGpD2PCypUr89Y0Q6cFd/Xq1bR27dq0ZMmSnGwkIOEqESmPIjhQIv0p/S4bbSQwMwyCIy7KapMogCTHn3adh8RLmTglGmYwzWaAHzNT6UMftrTp+tSBcfoJTrGYZun0VWV5RDKQlCQAiUtCyJTAZdtkNorvMKuDfIkb0UgE1Imd8+L8QMLhPCnji4/EB5Txpz+mdh0HoemmhK/ah1lJtW6apdOC0wyBUQYlGYlHUpGg1QSKNCjLQnXiY4nHcxkiYUYh7tmzZ+fthw8f8hKS82GJST/KbNlPH/rTTyBenvVok5ARmsahDVNckxmUZdMuY7FuIBlIPpDwqGMkmxIm2oCtYqka+xAMQlq0aFH2J34EpBvGwoUL83IOwfCMimg0ywF+tOEr0eFPG+NwDLVT5nogZu0bZoxr4ui04Molk+7iJCNWwr5oI4Zh7SCBlUtKBILhS8IjKi0Tmc3Zp9kKH40hEdLGtVCdsXlm1KwpESmOyawar+qmHcZCcKDkAASoLUmpu3UdawqNpwTX2GW72oidMvHLWEoeOXIkPXv2LAuN/fiV5/zgwYN0+PDhdPDgwfT69essMPoyNmWJVs+46l/G9KdmmmUslpRKQpJISzGh+jBTAvXbNxUrk7I0KOv4EiMzFOeBMBDL27dv0/79+9Pjx497/Zi1EJ9mr1evXqVz586lffv29fzoz29yt27dymPgpyWpBAh1r0tpwFbxmObpvOB018ZIIpIKSDCSmASrayRnU9ZvfKy6jzpxEi+JzDmwBOSHcZ7rePmBD4muZaXYvHlzOnDgQJ7B6Kf99+/fT1u2bEkvXrzoCY3xdG36xTHM8NcNga1ph04LTjMEkARKIij31bkj49OGEYOSlDpbiaaauPgiHPxXr16dTp48mdavX5/Pq0x4xIMfQgOez3ihwjPa5cuX065du7JQJV76LFiwIM90+CqmOug8oLzWoHbTHJ2f4apUk3gU/mYCafZBWEAs69atS/Pnz891zot9mg0RDktHQDyKnQ8Bdu7cmYWGsHU9KEukCEeiN91i7AQ3ziAORMHz2PHjx7O9fPkyi0mCQlyI7vbt2+no0aPp/PnzuY9EtGrVqrR9+/be8xr9JDbqiLXu7Gbi8b9MyyAIxKI3iYjj+fPneTm5d+/e/CZSgpOwHj58mL8jvXDhQrp27VoWVyksgTDVj9kRsZXCM93DgmsZCULwnLV169b8k8Dy5cuzmNiPQPBlqbh79+60bNmydOPGjXTs2LHcB3iGwwT+9EN4mj0RpZabpntYcC3DbKO3jJqpEAltekZjVtPXJOxDYLwkoS/PchIkW/wAH71cYR/GLMf4iE/PdqZbWHAtg4gQGgJg5lEZEAvC0xtKiYYywgHqGJ+HMRb7mL0w+uLHmNTZ0sY4OobpFhZcAIii/DD5yZMn6dKlS1mA169fT3fv3s1+CAtRbdu2Lb179y6dOHEi/wyAeK5cuZJ/f3v06FF+tkNgLDmfPn3aEydjU8bfgusmFlzL6AUGyz+etajzUuTixYv5tzgEd+fOnZ5YmKX27NmTfQ8dOpS/NFmzZk1+iXLv3r1siJCfB/h7QV66IFSOwdj010xouken/wD19OnTadOmTfmvjglTSUliQRl6nQRr485PclfjIU6etVjeYVr+lctJZjzqPItJLAgHeG5TrIzPuGx5YYIP/RgT8KENo4yvyvQZhgTKD+lsdVwor7Vphs5fTSUb22oyAHWSQu3RcFzFV8ZRJqqeqdjK+MF78eLF+eUIYpTYQG8jMfYjBvzlRzttGPuZDWkvRavrMwxdX24Sip26xGeapdOCq/6DUx9kJNswawOOXVLWSWYlPgkMZRu+qpemMco21SXufpT+WL9rULXyWJOdi2mGzguujgEJU1Imnqwtqsco66PYKAzq3688mQ1C19U0y1gIriyXdSBpWA4NM80wTVFN2mq5pN/M8ifGuQ9rBy0NJzPQtRTVummOTguuKhpeFFTLJE35bITxHFNtUxK2iURHwv7tpOX4XIdhJqFCeaOw6Nqh028p+d5w48aN+RMoEqAMVWXaEZ7odzptnSJiJ2k5PsdQkvPmjzbV6zDVBB90voxXZ0zi002qjLVufzManRbcqVOnagmuX3sV2ptOII1ZHpO6lmuURzlmXd9B51hSxweY4RCalqKKYZS4TX06LbizZ8/2BCcIl2RQ2JTrJEcbp6lYVIayrtjqHrvOeVT50/Mq+yveqcRh6tHpZzjdectEUL16Ry4hifoZy7ymjGdI/WisOsZx1KaZ7m8YcZTXbZDpOg66lqZZOj/DbdiwIf//BUiGMlSVlTglg06pyYTiGCQ2NwXKOiaJO+5YeO3R+ewo//ElrqpV6eeDTbZvKqaXDJTLWaILNgqj+pupM1a342pSTcWaot/YWFfoF9sgq/qb9hj/9Y8xY4QFZ0wgFpwxgVhwxgRiwRkTiAVnTCAWnDGBWHDGBNJpwemTKX1GpW8EjRlXOj/DSXDG/B/wktKYQCw4YwKx4IwJxIIzJhALzphALDhjArHgjAnEgjMmEAvOmEAsOGMCseCMCcSCMyYQC86YQCw4YwKx4IwJxIIzJhALzphALDhjArHgjAnEgjMmEAvOmEAsOGMCseCMCcSCMyYQC86YQCw4YwKx4IwJxIIzJhALzphALDhjArHgjAnEgjMmEAvOmEAsOGMCseCMCcSCMyYQC86YQCw4YwKx4IwJxIIzJhALzphALDhjArHgjAnEgjMmEAvOmEAsOGMCseCMCcSCMyYQC86YQCw4YwKZ+P0v/5WNMS3jGc6YQCw4YwKx4IwJxIIzJhALzphALDhjwkjpH6tZNIBYbAEbAAAAAElFTkSuQmCC)
EXCUSE ME!为什么不报错?
单独查询 select id1 from test2 是一定会报错: 消息 207,级别 16,状态 1,第 11 行 列名 'id1' 无效。然而使用了IN的子查询就是这么敷衍,直接查出 1 2 3
这仅仅是容易出错的情况,自己不写错还没啥事儿,下面来看一下 NOT IN 直接查出错误结果的情况:
给test2插入一个空值:
insert into test2 (id2) values (NULL)
我想要查询,在test2中不存在的 test1中的id 。
select id1 from test1
where id1 not in (select id2 from test2)
结果是:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAPoAAABqCAYAAACVikx+AAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAAEnQAABJ0Ad5mH3gAAABhaVRYdFNuaXBNZXRhZGF0YQAAAAAAeyJjbGlwUG9pbnRzIjpbeyJ4IjowLCJ5IjowfSx7IngiOjI1MCwieSI6MH0seyJ4IjoyNTAsInkiOjEwNn0seyJ4IjowLCJ5IjoxMDZ9XX19gRUQAAAJwElEQVR4Xu2du88NTRzH53G/Cxp0olKI6IgGhUqnFp1WpRR/hLhEiFKIaxAhEYlKoiXiLgp0iPv93c/k+Z5MNuc8Z5/LHs97ft9P8svOzs7Ozo757Myu431H/lYkY8xQM2N0a4wZYiy6MQGw6MYEwKIbEwCLbkwALLoxAbDoxgTAohsTAItuTAAsujEBsOjGBMCiGxMA/6OWinPnzqX58+enHz9+pF+/fuX0t2/fcnrGjBlpZGQk/f79u5NuQtNy/eCPh5g5c2Yn/efPn9y2efPmpS9fvuR2zZ49Ox8Dpcvy5LHlPqhrwYIF6fPnz+nnz5/5/Dlz5uQ2f/z4sVOfmDVrVt7SP5y/fPny9O7du1yGuqi3H1yHttBm4Byup37atWtX3pp2sOgVt2/fTqtWrcqDHRAdkYBBL1kY1E2YKsmBaxO0o9ynfbT369evHVEFMtIGSchDC8FK0ZH3+/fvuRxptjpOfeW9ci2Oq4zyuCblaE8/KE/dEl3nUCesXr06b007WPSK69evp/Xr16dly5blQc7Ap1skAOnxiA4awJOl2x8PeYjDjIrotIu08iQjIBflydd96KFBmnZKVmZszd5AOWbisgznsCVP/dME6ukmutpi2sW9XMEylEGIDAw8hGEgKiROmTdWjKdsv2iCytJuZJV87NN27ov7AwnLfZKmLGUkPZCmPOcTytd1EFwPQ8oqv1+U1PdNu1j0Cs2IBGnQ4GbAM5gRoz5wBxlQpoX2aR9Lad67kZMZlHbPnTs3bz99+pSX6twPS3nOI82W45zD+ZwneGjwLk+eHiAIrnrII9SusQLKtBksFr2AQcigBwnPPsEg10AddABbtaUeHENUBF6yZEkuT/sRVw+qxYsX52UzovINAlk1qwPlyKOsZKc8edTDNZRPmv7gIaJj/YJ6zb/DoleUS1PNWkhAlHBs0EEb+uWDxC6X7ohJUBbRkFnLcVYvHNPsTBnVIfnJoy+0T918E9AqQfKqHWNFvb3aN4PBoleUA06DEhBfW2TQ7NQkpgrVJ7FUd5mvPNpOmvYrWLIfO3YsvXjxIgvOccqV9/zo0aN09OjRdPjw4fT27dssNudSN2k9LPQNQ+eXbZpsmHax6AUa/AxeLXmF9vuFBm63YxOJUoYyoNynLG1kRuY+EBJJ379/nw4ePJiePn3aOY9ZGuk1W7958yZduHAhHThwoFOO8/k79Tt37uQ6KKelv8SHpv1SBrBVe0z7WPRRNEsRDF4GMzCwkYeB3TSQYqqiW/1E/Rj7tJP2IhD3wFKbH9Tw3s5HNcogmJbvYuvWrenQoUN5xuY8HX/48GHatm1bevXqVUdw6lPfdGtHv6C8HkRszWCw6BWaEYHBp8EL5bEmMxBl2gjaIDnYZytZ68JQFmEpv3bt2nT69Om0cePGfF+laEhLOQQH3r/5UMc7+NWrV9OePXvyA0IPDc5ZtGhRntkpqzY1QfcBZV+D8k17WPQe1OUZD/9y4Gq2RWigLRs2bEgLFy7M+9wXxzT7IyxLdEBatZ0fEO3evTsLzgNF/UFaDweE1cPGTG8s+hCClMjI+/bJkydzvH79OksskZEa2e/evZuOHz+eLl68mM+RvGvWrEk7d+7svI9zniRnn4dE09nc/Hv8JzUkICKS6ss4Ur58+TIv2/fv35+/rEt0Cf348eP8O/9Lly6lGzduZKlLoQUPBJ3HagDJS+HN9MeiDwkSUfAevX379vxXaytXrswScxwxKcuSfO/evWnFihXp1q1b6cSJE/kc4B2dEJTnPITXaoGHgZb1Zvpj0YcEZld9NdfMjJzk6R2cWVy/fuMYYvPxjXN5V9eDgC3lgDL6aMcxglmd+pFe7+5memPRhwTkRXDEY6ZVGpAU4fXFXbKSRlhgn+BntNTFMWZrgnMpR53ssyWPenQNM72x6EMEMpb/YOXZs2fpypUrWfybN2+m+/fv53IIjcw7duxIHz58SKdOncp/nYa0165dy39//uTJk/zujtgs7Z8/f955KFA3acpb9P8HFn1I0Icxltm8S7PPx7bLly/nv0tH9Hv37nUkZVbet29fLnvkyJH8y7h169blj3MPHjzIgfz8NRv/Xp+PeTwguAZ1c75mfjP98X94ouLs2bNpy5Yt+b9yQndIBgY0lF3UZGC3MdMhVb09tJN3aZbRhJbZ5bKdGZ593rUlKcIC7+VqK/VTL1s+xFGG86gTKEMeQZqySnNOP/Rg4Ac4bHVdKPvatIN7dxQNcrb1QQjsMxiVP2i4rtpXtqMURO/MbBX8UGbp0qX5oxsPAUkO+rpOcBwJKa9y5JNHcJzZn/zyYaH+6Yf6l4eT2s6+pDftYtEr6gON/V7BIO8XbcC1S8p9JJJwiANlHmW1X4bqKPO0r4dKN8ryRLc+qEd5rbHuxbSDRa9goDUJYKCWlANe0Rb1a5T744nx0Ov8bumxohfqV9MuFr2iFFnpch8YrCw7+4Vm1KmiLks9XdJtJp1McO/98kFL8LEC1Jeivm/aw6JX1GXlA1Q9zWAt330J3lPreRr8bSLZEeVfy8L16Yd+oQcElA8oyz4Y/NW9gt+Db968Of9UlIFXdonS5CO86NZtbXUlDxlk4fpcQ3LxJZs87TdhomL1ul/qa1In7dPDsWxr0/PN5LDoFWfOnGkkerf8OuRP9cBVneU12deymPR4rtm0bK97LGlSBpjREVxLfrVhPO02E8eiV5w/f74juqBbGITqHtJNBmUb3am2KA3lvtrW9NpN7qPOZO+rPF/tnUg7zMTwO3qFZppyAGq/PgOVMHi7BcvpqQq+EejHJtonuI7yNLP/i6AdZb/1CvVjr7407eIZvYIZfdOmTfn/v8YgLLtEaQ3Ykl5dN5UDmWsgFA8j0romwvzfsfCDwzP6KOWgk9T1qNOtDDHWsYmEPl6RLmfF6RDjYbzlzdRh0btQH8wTiamiW93EdKFb23pFvbwZHBbdmABYdGMCYNGNCYBFNyYAFt2YAFh0YwJg0Y0JgEU3JgAWvUI/LdXPTfUbbmOGBYs+ikQ3Zhix6MYEwKIbEwCLbkwALLoxAbDoxgTAohsTAItuTAAsujEBsOjGBMCiGxMAi25MACy6MQGw6MYEwKIbEwCLbkwALLoxAbDoxgTAohsTAItuTAAsujEBsOjGBMCiGxMAi25MACy6MQGw6MYEwKIbEwCLbkwALLoxAbDoxgTAohsTAItuTAAsujEBsOjGBMCiGxMAi25MACy6MQGw6MYEwKIbEwCLbkwALLoxAbDoxgTAohsTAItuTAAsujEBsOjGBMCiGxMAi25MACy6MQGw6MYEwKIbE4CRvxWjaWPMUJLSf0UbNGCYDgw9AAAAAElFTkSuQmCC)
空白!显然这个结果不是我们想要的。我们想要3。为什么会这样呢?
原因是:NULL不等于任何非空的值啊!如果id2只有1和2, 那么3<>1 且 3<>2 所以3输出了,但是 id2包含空值,那么 3也不等于NULL 所以它不会输出。
(跑题一句:建表的时候最好不要允许含空值,否则问题多多。)
正确的姿势
1、用 EXISTS 或 NOT EXISTS 代替
select * from test1
where EXISTS (select * from test2 where id2 = id1 )
select * FROM test1
where NOT EXISTS (select * from test2 where id2 = id1 )
2、用JOIN 代替
select id1 from test1
INNER JOIN test2 ON id2 = id1
select id1 from test1
LEFT JOIN test2 ON id2 = id1
where id2 IS NULL
妥妥的没有问题了!
PS:那我们死活都不能用 IN 和 NOT IN 了么?并没有,一位大神曾经说过,如果是确定且有限的集合时,可以使用。如 IN (0,1,2)。