What is the difference between exists and in sql

Remove From My Forums. Answered by:. Archived Forums. Getting started with SQL Server. Beginners just getting started - here's the place for help with simple questions. For installation or setup questions, refer to the Setup Forum. Sign in to vote. Thursday, April 3, AM. Proposed as answer by Ch. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Collectives on Stack Overflow. Learn more. Ask Question. Asked 13 years, 2 months ago. Active 8 days ago. Viewed k times. Community Bot 1 1 1 silver badge.

Krantz Krantz 5, 3 3 gold badges 23 23 silver badges 18 18 bronze badges. Add a comment. Active Oldest Votes. Keith Keith k 69 69 gold badges silver badges bronze badges. Could you elaborate on "When you have a table in an in statement it makes more sense to use a join, but it doesn't really matter. The query optimiser will return the same plan either way. Sander it doesn't: the first query returns all the columns from table , while the second returns everything from table and table2.

In some mostly older SQL databases the in query will get implemented as a nested join, while the join query can be nested, merged, hashed, etc - whatever's quickest.

Okay, I should have specified columns in the select clause, but you should update your answer because it clearly states that the queries "will return the same plan either way". Show 1 more comment. Matt Hamilton Matt Hamilton k 60 60 gold badges silver badges bronze badges. Last query is dangerous because it might fail in the case subquery doesn't return any results. Based on cost optimizer : There is no difference.

Michael Currie Proof of your argument? Badly implemented query optimizer? I've seem something like this though not exactly this situation happen in a certain RDBMs IN may or may not be a Boolean comparison. Why was this upvoted so many times? There's absolutely no reason why this assumption-based statement should be generally true. Show 3 more comments. Is that a fair assumption? Alvin Thompson 5, 3 3 gold badges 24 24 silver badges 39 39 bronze badges.

Lasse V. Karlsen Lasse V. Karlsen k 94 94 gold badges silver badges bronze badges. I don't know about the OP, but I would like the answer to this question! Engineering is a compromise — Caius Jard. CaiusJard Right, agreed. Alireza Masali Alireza Masali 1 1 gold badge 10 10 silver badges 19 19 bronze badges. This answer is reasonable synopsis of Tom Kite's sentiment asktom. I think this answer is based on intuition, which is fair enough. But it cannot be universally true. True is returned if one or more rows are returned by executing the sub-query, otherwise False when no rows are returned.

Skip to content. Change Language. Related Articles. Table of Contents. Save Article. Improve Article. Like Article. Last Updated : 15 May, Customer Table.