Difference Between Exists and in in Sql

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



Leave a reply



Submit