Implementing EXCEPT functionality for social networking exercise

Question

I am taking Stanford's Introduction to Databases Self-Paced online course. I have gone through the videos in the SQL mini-course, and I am having trouble completing the exercises.

The following is the question from the SQL Social-Networking Query Exercises, Question 5:

For every situation where student A likes student B, but we have no information about whom B likes (that is, B does not appear as an ID1 in the Likes table), return A and B's names and grades.

The database can be found here or download the schema + data.

My answer to this question is as follows:

SELECT H1.name, H1.grade, H2.name, H2.grade
FROM (SELECT * FROM Likes L1
WHERE L1.ID2 NOT IN (SELECT L2.ID1 FROM Likes L2)) F INNER JOIN
Highschooler H1 ON F.ID1 = H1.ID INNER JOIN Highschooler H2 ON F.ID2 =
H2.ID; 

I want to avoid using the WHERE L1.ID2 NOT IN... part of the query.

In particular, I'd like to use two result sets.

The first being the entire Likes relation: SELECT * FROM Likes L1;

Then I'd like to take the results from the following query: SELECT L2.ID FROM Likes L2

And then someone filter the first result set such that it only includes the tuples in result set 1 if L1.ID2 is not in result set 2.

I feel like there's a join or maybe set operator that can do this?

Answer 1

Formatting

Using indentation and line breaks would make your code easier to read. There is no real "standard" so go with what you feel is clearest. I usually go with something that resembles C indentation a bit. Others use a more Java-like indentation. Doesn't matter, as long as it makes it more readable.

SELECT     
    H1.name, 
    H1.grade, 
    H2.name, 
    H2.grade
FROM 
(
    SELECT * 
    FROM Likes L1
    WHERE L1.ID2 NOT IN 
    (
        SELECT L2.ID1 
        FROM Likes L2
    )
) F 
INNER JOIN Highschooler H1 
    ON F.ID1 = H1.ID 
INNER JOIN Highschooler H2 
    ON F.ID2 = H2.ID; 

---

F and other aliases

It's good practice for aliases to get meaningful names. Making the code shorter is all well and good, and in your case doing a self-JOIN they are necessary; but, your subquery being aliased as F doesn't say much about what it represents.

---

MySQL limitations

There are several things you could do in other RDBMS that MySQL doesn't allow. You could have made a Common Table Expression, declared a Table Variable, and even used EXCEPT; except, none of those are supported by MySQL. I'm afraid, given the tool, this is about as good as it will get. Perhaps someone else will know of a better way. Only other thing I could think of is using a temp table, but that's totally overkill for something simple like this.