The subquery inside the NOT IN statement can be evaluated at the beginning of the execution, and the temporary table can be checked against each value in the outer select, rather than re-running the subselect every time as would be required with the NOT EXISTS statement.
118 I'm trying to learn SQL and am having a hard time understanding EXISTS statements. I came across this quote about "exists" and don't understand something: Using the exists operator, your subquery can return zero, one, or many rows, and the condition simply checks whether the subquery returned any rows.
This subquery returns multiple values, SQL is complaining because it can't assign multiple values to cost in a single record. Some ideas: Fix the data such that the existing subquery returns only 1 record Fix the subquery such that it only returns one record Add a top 1 and order by to the subquery (nasty solution that DBAs hate - but it "works")
SQL is a declarative language, not a procedural language. That is, you construct a SQL statement to describe the results that you want. You are not telling the SQL engine how to do the work. As a general rule, it is a good idea to let the SQL engine and SQL optimizer find the best query plan. There are many person-years of effort that go into developing a SQL engine, so let the engineers do ...
I have serious doubt on this answer, since most DBMS, definitely SQL Server 2008 and later, translate the single ID subquery (not correlated, meaning: not referencing multiple outer query columns) into a relatively fast semi-join.
The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.
A Correlated subquery is a subquery that is evaluated once for each row processed by the outer query or main query. Execute the Inner query based on the value fetched by the Outer query all the values returned by the main query are matched.
We need to make an alias of the subquery because a query needs a table object which we will get from making an alias for the subquery. Conceptually, the subquery results are substituted into the outer query. As we need a table object in the outer query, we need to make an alias of the inner query.
