6
\$\begingroup\$

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?

\$\endgroup\$
5
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.