Search code examples
sqlcountdistinctsetoverlapping

SQL counting distinct elements of overlapping sets

I want to count the number of distinct elements from two sets. Precisely this is the union of set 1 and set 2.

How can I format the following pseudo SQL into functioning SQL

select COUNT(set1 union set2) from table1`

EDIT set1 and set2 are columns of table1

Here is a short example:

Set 1 of students in a class:

Bubba, Jody, Rex, Sandy, Jules

Set 2 of students in a class:

Jody, Rob, Horace, Thor, Rex

I want the distinct number of students in both classes:

Bubba, Jody, Rex, Sandy Jules, Rob, Horace, Thor

So, a union will do and I can just count.

Solution 
  • Select Count(column_alias) From
( Select set1 As column_alias From Table1 
Union 
Select set2 From Table1 )

    The Union will make the elements unique. This will only work if the column datatypes are compatible. You can modify the subqueries to include a Where condition if you need it.