The long answer (From T-SQL Fundamentals, Third edition, by Itzik Ben-Gan) This is an example: Imagine there is a order with a NULL orderid inside Sales.Orders table, so the subquery returns some integers, and a NULL value.
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.
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")
I'm trying to perform some recursion inside a subquery, but I don't seem to be able to use WITH - Have I missed something or is there a workaround perhaps? WHERE swn.stocknode_id in ( WITH Hi...
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.
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.
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 ...
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.
In MySQL used filesort, used temporary are slow and should be avoided. As for the joined subquery, it requires retrieving all rows matching the meta_key value from the wp_postmeta table before joining on post/order id's. So it should be safe to assume that it would be faster to match on the order/post id's and meta_key.
