SQL — Excluding Tuples
I’m writing a query to select employee names from a database where the employees are married but have no children.
I have two different tables: Employee and Dependents
Employee has the following fields
fname, lname, ssn
And Dependents have the following fields
essn, dependents_name, relationship
Dependents.essn is a FK that references Employee.ssn
Some Employee.ssn have multiple tuples in Department, each with a different
relationship status (spouse, son, daughter), describing the type of dependent that employee has.
I’m looking to write a query that selects those employees, based on the ssn -> essn relationship, that have the relationship
spouse but not the relationships
So far, this is what I’ve tried:
select e.fname, d.relationship from (employee e left outer join dependents d on e.ssn = d.essn) where d.relationship = 'spouse'
It returns the tuples of employees with the
spouse value, but also with the
How can I filter my tables to include only those employees with the
3 Solutions collect form web for “SQL — Excluding Tuples”
you can use
NOT EXISTS clause
select e.fname, d.essn, d.relationship from employee e join dependents d on e.ssn = d.essn and d.relationship = 'spouse' and not exists ( select 1 from dependents d1 where d1.essn= e.ssn and d1.relationship <> 'spouse' )
Here’s another way with a common table expression and
with cte as ( select e.fname, d.relationship, count(d.relationship) over (partition by e.ssn) cnt from employee e join dependents d on e.ssn = d.essn ) select fname, relationship from cte where cnt = 1 and relationship = 'spouse'
- SQL Fiddle Demo
BTW, no need for an
OUTER JOIN — your
WHERE criteria negates it since you require a spouse to exist.
You can do this with aggregation and a
select e.fname from employee e left outer join dependents d on e.ssn = d.essn group by e.fname having sum(case when d.relationship = 'spouse' then 1 else 0 end) > 0 and sum(case when d.relationship in ('son', 'daughter') then 1 else 0 end) = 0;