After a bit of digging, I can confirm both your scenarios: MySQL 5.1 does apply the ORDER BY inside the subquery. MariaDB 5.5.39 on Linux does not apply the ORDER BY inside the subquery when no LIMIT is supplied. It does however correctly apply the order when a corresponding LIMIT is given: SELECT t2.Code FROM … Read more
Problem The manual explains: The optional RETURNING clause causes UPDATE to compute and return value(s) based on each row actually updated. Any expression using the table’s columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table’s columns are used. The syntax of the RETURNING list is … Read more
Add an ALIAS onto the subquery, SELECT COUNT(made_only_recharge) AS made_only_recharge FROM ( SELECT DISTINCT (identifiant) AS made_only_recharge FROM cdr_data WHERE CALLEDNUMBER = ‘0130’ EXCEPT SELECT DISTINCT (identifiant) AS made_only_recharge FROM cdr_data WHERE CALLEDNUMBER != ‘0130’ ) AS derivedTable — <<== HERE
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 … Read more
Planned features: SPARK-23945 (Column.isin() should accept a single-column DataFrame as input). SPARK-18455 (General support for correlated subquery processing). Spark 2.0+ Spark SQL should support both correlated and uncorrelated subqueries. See SubquerySuite for details. Some examples include: select * from l where exists (select * from r where l.a = r.c) select * from l where … Read more
An Explain Plan would have shown you why exactly you should use Exists. Usually the question comes Exists vs Count(*). Exists is faster. Why? With regard to challenges present by NULL: when subquery returns Null, for IN the entire query becomes Null. So you need to handle that as well. But using Exist, it’s merely … Read more
Postgres allows: UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ …) AS subquery WHERE dummy.address_id=subquery.address_id; This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can’t tell you the answer in full). In general; A CTE can be used recursively; a sub-query … Read more
I would EXPECT the first query to be quicker, mainly because you have an equivalence and an explicit JOIN. In my experience IN is a very slow operator, since SQL normally evaluates it as a series of WHERE clauses separated by “OR” (WHERE x=Y OR x=Z OR…). As with ALL THINGS SQL though, your mileage … Read more
Your closest direct approximation is a join with a correlated sub-query as the predicate. SELECT ORD.ID ,ORD.NAME ,ORD.DATE ,ORD_HISTORY.VALUE FROM ORD INNER JOIN ORD_HISTORY ON ORD_HISTORY.<PRIMARY_KEY> = (SELECT ORD_HISTORY.<PRIMARY_KEY> FROM ORD_HISTORY WHERE ORD.ID = ORD_HISTORY.ID AND ORD.DATE <= ORD_HISTORY.DATE ORDER BY ORD_HISTORY.DATE DESC LIMIT 1 ) In your case, however, you only need one field … Read more