Implementing EXCEPT functionality for social networking exercise

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?

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.

StackExchange Code Review Q#80104, answer score: 5