Does SparkSQL support subquery?

by

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 not exists (select * from r where l.a = r.c)

select * from l where l.a in (select c from r)
select * from l where a not in (select c from r)

Unfortunately as for now (Spark 2.0) it is impossible to express the same logic using DataFrame DSL.

Spark < 2.0

Spark supports subqueries in the FROM clause (same as Hive <= 0.12). 

SELECT col FROM (SELECT *  FROM t1 WHERE bar) t2

It simply doesn’t support subqueries in the WHERE clause.Generally speaking arbitrary subqueries (in particular correlated subqueries) couldn’t be expressed using Spark without promoting to Cartesian join.

Since subquery performance is usually a significant issue in a typical relational system and every subquery can be expressed using JOIN there is no loss-of-function here.

More Related Contents:

  1. How to select the first row of each group?
  2. Querying Spark SQL DataFrame with complex types
  3. Concatenate columns in Apache Spark DataFrame
  4. Spark Window Functions – rangeBetween dates
  5. Spark SQL window function with complex condition
  6. Including null values in an Apache Spark Join
  7. Spark sql queries vs dataframe functions
  8. Explode (transpose?) multiple columns in Spark SQL table
  9. How to get other columns when using Spark DataFrame groupby?
  10. get TopN of all groups after group by using Spark DataFrame
  11. Why using a UDF in a SQL query leads to cartesian product?
  12. How to aggregate over rolling time window with groups in Spark
  13. What is the difference between cube, rollup and groupBy operators?
  14. Difference between === null and isNull in Spark DataDrame
  15. Join vs. sub-query
  16. Difference between CTE and SubQuery?
  17. updating table rows in postgres using subquery
  18. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  19. Return pre-UPDATE column values using SQL only
  20. How can I insert values into a table, using a subquery with more than one result?
  21. SPARK SQL – case when then
  22. How do I get a SQL row_number equivalent for a Spark RDD?
  23. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  24. Nested select statement in SQL Server
  25. Postgres Error: More than one row returned by a subquery used as an expression
  26. Oracle: Combine multiple results in a subquery into a single comma-separated value [duplicate]
  27. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  28. Spark replacement for EXISTS and IN
  29. How do I perform update query with subquery in Access?
  30. Procedurally transform subquery into join

Leave a Comment