[ 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)