Difference between exists, and in in SQL
EXISTS: Specifies a subquery to test for the existence of rows.
In other words... Does this subquery return any rows?
IN: Determines whether a specified value matches any value in a subquery or a list.
In other words... Hey, get me the the first and last name of employees whose Job Title is 'Design Engineer', 'Tool Designer', 'Marketing Assistant'
code snippet from link below.
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p
JOIN HumanResources.Employee AS e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');
GO
Further reading
MS DOCS EXISTS
MS DOCS IN
What's the difference between 'ANY' and 'EXISTS' in sql-server
The two queries are quite different.
The first query returns all rows or no rows depending on whether the subquery returns any rows at all or no rows.
You intend a correlated subquery:
select code from account where exists (select 1 from store where store.account = account.code)
These should be equivalent.
SQL Server : usage of In vs Exists
The EXISTS clause is much faster than IN when the subquery results are very large. Conversely, the IN clause is faster than EXISTS when the subquery results are very small.
The IN clause can't compare anything with NULL values, but the EXISTS clause can compare everything with NULLs.
MYSQL - Difference between IN and EXIST
EXISTS
EXISTS literally is for checking for the existence of specified criteria. In current standard SQL, it will allow you to specify more than one criteria for comparison - IE if you want to know when col_a and col_b both match - which makes it a little stronger than the IN clause. MySQL IN supports tuples, but the syntax is not portable, so EXISTS is a better choice both for readability and portability.
The other thing to be aware of with EXISTS is how it operates - EXISTS returns a boolean, and will return a boolean on the first match. So if you're dealing with duplicates/multiples, EXISTS will be faster to execute than IN or JOINs depending on the data and the needs.
IN
IN is syntactic sugar for OR clauses. While it's very accommodating, there are issues with dealing with lots of values for that comparison (north of 1,000).
NOT
The NOT operator just reverses the logic.
Subqueries vs JOINs
The mantra "always use joins" is flawed, because JOINs risks inflating the result set if there is more than one child record against a parent. Yes, you can use DISTINCT or GROUP BY to deal with this, but it's very likely this renders the performance benefit of using a JOIN moot. Know your data, and what you want for a result set - these are key to writing SQL that performs well.
To reiterate knowing when and why to know what to use - LEFT JOIN IS NULL is the fastest exclusion list on MySQL if the columns compared are NOT nullable, otherwise NOT IN/NOT EXISTS are better choices.
Reference:
- MySQL: LEFT JOIN/IS NULL, NOT IN, NOT EXISTS on nullable columns
- MySQL: LEFT JOIN/IS NULL, NOT IN, NOT EXISTS on NOT nullable columns
EXISTS vs JOIN and use of EXISTS clause
EXISTS
is used to return a boolean value, JOIN
returns a whole other table
EXISTS
is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN
is used to extend a result set by combining it with additional fields from another table to which there is a relation.
In your example, the queries are semantically equivalent.
In general, use EXISTS
when:
- You don't need to return data from the related table
- You have dupes in the related table (
JOIN
can cause duplicate rows if values are repeated) - You want to check existence (use instead of
LEFT OUTER JOIN...NULL
condition)
If you have proper indexes, most of the time the EXISTS
will perform identically to the JOIN
. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS
.
If your JOIN
key is not indexed, it may be quicker to use EXISTS
but you will need to test for your specific circumstance.
JOIN
syntax is easier to read and clearer normally as well.
What's the difference between 'not in' and 'not exists'?
I think it serves the same purpose.
not in
can also take literal values whereas not exists
need a query to compare the results with.
EDIT: not exists
could be good to use because it can join
with the outer query & can lead to usage of index, if the criteria uses column that is indexed.
EDIT2: See this question as well.
EDIT3: Let me take the above things back.
See this link. I think, it all depends on how the DB translates this & on database/indexes etc.
Related Topics
Condition Within Join or Where
How to Do a Batch Insert in MySQL
How to Temporarily Disable a Foreign Key Constraint in MySQL
Create Unique Constraint With Null Columns
Reset Identity Seed After Deleting Records in SQL Server
How to Spool to a CSV Formatted File Using Sqlplus
Simulate Lag Function in MySQL
Does the Join Order Matter in Sql
Postgresql: Running Count of Rows For a Query 'By Minute'
Group by Clause in MySQL and Postgresql, Why the Error in Postgresql
Inner Join VS Left Join Performance in SQL Server
SQL Counting All Rows Instead of Counting Individual Rows
Is There Any Difference Between Group by and Distinct