Employee couples – SQL Puzzle

Given a table schema of employees like the following. Employee id, employee name, and spouse id if their spouse is also working in the same company. spouse id will be null if only one person is working.

Id	Name	Spouse id
100	Sachin	101
101	Anjali	100
102	Virat	103
103	Anushka	102
104	Sanju

Given a table like the above, the question is to find the number of couples working in that office.

You might be overthinking about this problem if you are considering a self-join because spouse id is a reference to id column of the same table. In fact we don’t need a join at all for the given problem. We know that join will be a costly operation of the table is considerably big. We just need the following query to get the result.

SELECT 
  COUNT(1)/2 AS COUPLES
FROM
  employees
WHERE
  spouse_id IS NOT NULL

However if you need to print the names of the couples also, then you need a self join some thing like the following.

SELECT 
    a.name as employee, 
    b.name as spouse 
FROM 
    test.emp1 as a JOIN test.emp1 as b 
    ON a.id = b.spouse_id;

The above query prints the couple names twice. It is an interesting challenge to the readers if we want to see each couple row only once! If you are looking for a clue, we just need to add one more condition to the above query. Give it a try 🙂