SQL Self-join, Not Exists, Or Something Else?

Question

many-to-many name and role table --

create table t (name varchar, role varchar) ; 

insert into t (name, role) values ('joe', 'husband'), ('joe', 'father'),
    ('tom', 'husband'), ('neo', 'bachelor') ; 

> select * from t;
 name |   role   
------+----------
 joe  | husband
 joe  | father
 tom  | husband
 neo  | bachelor

need to convert into mapping of name and the role(s) he does not have --

not_a    | name
---------+-----------
husband  | neo
father   | tom
father   | neo
bachelor | joe
bachelor | tom

How to achieve that in true SQL without iterating through each role/name?

Something like joining the table on itself and select T1.Name and T2.Role where the role is not in the roles of the name in T1 should do the trick. What DBMS are you on? SQL Server, Oracle, ...? — Krumelur, Jun 05 '14 at 12:23

score 2 · Answer 1 · edited Jul 29 '20 at 18:26

Assuming you only have this table you can use:

SELECT  r.role AS not_a, n.Name
FROM    (SELECT DISTINCT Name FROM T) AS n
        CROSS JOIN (SELECT DISTINCT Role FROM T) AS r
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    t 
            WHERE   t.Name = n.Name
            AND     t.Role = r.Role
        );

Example on SQL Fiddle

The main query will generate all pairs of names/roles, then the not exists will exlcude all the pairs that already exist.

If you actually have a name and role table, then you can replace the subqueries with the actual tables:

SELECT  r.role AS not_a, n.Name
FROM    Names AS n
        CROSS JOIN Roles AS r
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    t 
            WHERE   t.Name = n.Name
            AND     t.Role = r.Role
        );

You haven't specified a DBMS, so if you are using MySQL, using LEFT JOIN\IS NULL will perform better than NOT EXISTS

SELECT  r.role AS not_a, n.Name
FROM    (SELECT DISTINCT Name FROM T) AS n
        CROSS JOIN (SELECT DISTINCT Role FROM T) AS r
        LEFT JOIN t
            ON t.Name = n.Name
            AND t.Role = r.Role
WHERE   t.Name IS NULL;

I am also assuming it was just a demo, but in your table DDL you have used VARCHAR without a length which is not a good idea at all

your solution is effectively the same as Gordon Linoff's. I wish I could accept both of your answers. Thank you for your brilliant solution and explanation. — Jerry, Jun 05 '14 at 12:44

score 2 · Accepted Answer · edited Jun 05 '14 at 12:35

To get roles that someone doesn't have is a little complicated. You have to generate all pairs of names and roles and then pick out the ones that don't exist. This uses a left outer join.

The following is standard SQL for doing this:

select r.role as not_a, n.name
from (select distinct name from t) n cross join
     (select distinct role from t) r left outer join
     t
     on t.name = n.name and t.role = r.role
where t.name is null;

As a note: never use varchar() without a length when defining variables and columns. The default values may not do what you expect.

SQL Self-join, Not Exists, Or Something Else?

2 Answers2