A SQL case: The INTERSECT statement

I decided to explore another SQL case that also is inspired by Facebook. When you visit a friends profile you can see what friends you and he/she have in common.

 

I have extended the database from my previous post to include a FriendRelations table with the following columns and relations to the Persons table;

As in the previous post this is a many-to-many relationship, but it’s a bit special since the foreign keys of the relation table points to the same primary key. This means that a person can be friend with a person in two different ways, either via the PersonId1 column or the PersonId2 column. Let’s have a look at the test data before we continue;

(In my previous post i accidentally named two persons “Spiderman”, one of them is now changed to “Superman”)

So, for example, the first row to the right means that “Spiderman” and “Superman” are friends with each other. Another example is that “Superman” is friends with “Spiderman”, “Catwoman” and “Tintin”.

In this case there will be two given PersonIds, one that represents the currently logged in user (this might come from a session variable) and one that represents the person profile the user is currently visiting (this might come from a querystring variable). In the following SQL commands these IDs will be hardcoded, I will use 4 (“Spiderman”) as the logged in user and 1 (“Superman”) as the ID of the visited profile. If you study the data above you can see that both these persons are friends with “Catwoman”, so this is the final result set we want from our SQL question;

PersonId	PersonName
5	Catwoman

Let’s start with getting all the friends that “Superman” (PersonId = 1) has;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 
WHERE
    fr.PersonId2 = 1

This will give the following result set;

But wait a minute, “Superman” is also friends with “Tintin”, where did he go? In the above SQL we only get friends where “Superman” is connected through the PersonId2 column of the FriendRelations table. We need to work some more SQL magic to include friends that are connected through PersonId1 as well. Let’s give it a shot;

 SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    fr.PersonId1 = 1 OR fr.PersonId2 = 1

So we add some OR statements to the JOIN and the WHERE, but this resultset still looks a bit odd. It does include “Tintin” but “Superman” isn’t friends with himself and he certainly isn’t friends with himself three times over! However there’s nothing strange with this result, since the SQL above will give us all rows that has any connection with PersonId 1 in the FriendRelations table. So, let’s just filter “Superman” out!

 

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1 
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)

And now we get all the persons that are friends with “Superman” and nothing else;

Friends with “Superman”

And if we run the very same SQL statement but exchange the 1 for a 4, we get all persons that are friends with “Spiderman”;

Friends with “Spiderman”

As you can see if you compare these result sets, the only friend they have in common is “Catwoman”. To get only that row in the result set we can separate both our SELECT statements (the one with a 1 and the one with a 4) with INTERSECT and only get those rows that both of the SELECT statements have in common;

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 1
    AND (fr.PersonId1 = 1 OR fr.PersonId2 = 1)
    
INTERSECT

SELECT
    PersonId, PersonName
FROM
    Persons AS p INNER JOIN FriendRelations AS fr
    ON p.PersonId = fr.PersonId1 OR p.PersonId = fr.PersonId2
WHERE
    p.PersonId <> 4 
    AND (fr.PersonId1 = 4 OR fr.PersonId2 = 4)

And finally we get the expected resultset;

Download the database here, this database also includes the final SQL command as a stored procedure.

Note, for an INTERSECT to work, the two SELECT statements has to have identical column lists, at least when it comes to type. In this case it is PersonId (int) and PersonName (varchar).