How to display only the records where there is a match on at least one of several tables?

user_641666 0
Viewed 81

I have several tables. I'd like to join against. I only want to see records where there is a match on at least one of those tables

insert into foo values
  (1),
  (2),
  (3),
  (4),
  (5),
  (6),
  (7),
  (8);

insert into a values
  (1),
  (2),
  (3);

insert into b values
  (3),
  (4),
  (5),
  (6);

desired output:

id      id      id
1       1       (null)
2       2       (null)
3       3       3
4       (null)  4
5       (null)  5
6       (null)  6

Normally I would do this using WHERE EXISTS (eg: below) but that's not supported in SparkSQL. What is the most performant way to achieve this? I would prefer to rely on my join to determine the result rather than filtering the result set. Also, I am not limited to using SparkSQL, dataframe API is great also.

select * 
from foo
left join a on foo.id = a.id
left join b on foo.id = b.id
where exists (select 1 from a x where foo.id = x.id)
or exists (select 1 from b x where foo.id = x.id)
;
apache-spark-sql apache-spark sql
4 Answers

You need FULL OUTER JOIN OR LEFT JOIN with filter :

select f.*, a.*, b.*
from foo f full outer join
     a
     on a.id = f.id full outer join
     b
     b.id = f.id
where a.id is not null or b.id is not null;
user_55294450

You can try LEFT JOIN and add some condition in where

SELECT * 
FROM foo 
LEFT JOIN a on foo.id  = a.id 
LEFT JOIN b on foo.id  = b.id 
WHERE a.id IS NOT NULL OR  b.id IS NOT NULL
ORDER BY foo.id
user_51760710

You are close:

select * 
from foo left join
     a
     on foo.id = a.id left join
     b
     on foo.id = b.id
where a.id is not null or b.id is not null;
user_11440350

Here the solution with DataFrame API:

val foo = (1 to 8).toDF("id")
val a = Seq(1,2,3).toDF("id")
val b = Seq(3,4,5,6).toDF("id")


foo
.join(a,foo("id")===a("id"),"left")
.join(b,foo("id")===b("id"),"left")
.where(a("id").isNotNull or b("id").isNotNull)
.show()

+---+----+----+
| id|  id|  id|
+---+----+----+
|  1|   1|null|
|  2|   2|null|
|  3|   3|   3|
|  4|null|   4|
|  5|null|   5|
|  6|null|   6|
+---+----+----+
user_11385230
Related