[ 
https://issues.apache.org/jira/browse/DRILL-4092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16797211#comment-16797211
 ] 

benj commented on DRILL-4092:
-----------------------------

Until the feature is implemented, there are queries to achieve the same result.

with file1.csvh and file2.csvh each containing only column a

without INTERSECT we can do
{code:java}
/* Simulate INTERSECT */
SELECT DISTINCT a FROM 
(
 SELECT a
 , min(idfile) over(PARTITION BY a) AS minc
 , max(idfile) over(PARTITION BY a) AS maxc
 FROM
 (
  SELECT a, 0 idfile FROM ....`file1.csvh`
  UNION ALL 
  SELECT a, 1 idfile FROM ....`file2.csvh`
 ) AS lvl_union
) AS lvl_over WHERE minc = 0 and maxc = 1{code}
But it's a little bit more complicated for INTERSECT ALL (as defined in 7.12 of 
ansi-iso-9075-2-1999.pdf)
{code:java}
/* Simulate INTERSECT ALL */
SELECT a FROM 
(
 SELECT a, c
 , min(idfile) over(PARTITION BY a) AS minc
 , max(idfile) over(PARTITION BY a) AS maxc
 , count(a) OVER(PARTITION BY a, c) AS nb_elt_by_table
 ,LEAST(count(CASE WHEN c = 0 THEN 1 END) OVER (PARTITION BY a)
      , count(CASE WHEN c = 1 THEN 1 END) OVER (PARTITION BY a)) min_elt
 FROM
 (
  SELECT a, 0 idfile FROM ....`file1.csvh`
  UNION ALL 
  SELECT a, 1 idfile FROM ....`file2.csvh`
 ) AS lvl_union
) AS lvl_over 
WHERE minc = 0 and maxc = 1 AND nb_elt_by_table = min_elt
{code}
Hoping that this can help until we can use the standard SQL syntax.

> Support for INTERSECT 
> ----------------------
>
>                 Key: DRILL-4092
>                 URL: https://issues.apache.org/jira/browse/DRILL-4092
>             Project: Apache Drill
>          Issue Type: New Feature
>            Reporter: Victoria Markman
>            Assignee: Prasad Nagaraj Subramanya
>            Priority: Major
>




--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Reply via email to