0
SELECT DISTINCT
    ...
    ...
    ...
FROM Reviews Rev
    INNER JOIN Reviews SubRev ON Subrev.W_ID=Rev.ID
WHERE Rev.Status='Approved'

This is a small part of a long query that I've been trying to understand for a day now. What is happening with the join? Reviews table appears to be joined with itself, under different aliases. Why is this done? What does it achieve? Also, ID field of the Reviews table is null for the entries that are nevertheless selected and returned. This is correct, but I don't understand how that can happen if the W_ID field is not null.

m1547
  • 77
  • 8
  • 1
    It's perfectly normal to join a table to itself. In this case there is a `W_ID` that acts as a "SubReview" of the main review. This query looks up that SubReview and returns its information as well. – JNevill Jul 10 '19 at 13:54
  • 1
    And _"ID field of the Reviews table is null for the entries"_... which instance of the Reviews table does this comment apply to? `Rev` or `SubRev`? We don't know which fields you are selecting in the query, or precisely what the source data looks like. – ADyson Jul 10 '19 at 13:54
  • @ADyson thanks, your comments explain a lot. – m1547 Jul 10 '19 at 14:08
  • Possible duplicate of [What is a self join for? (in english)](https://stackoverflow.com/questions/36352271/what-is-a-self-join-for-in-english) – philipxy Jul 11 '19 at 10:13
  • (Obviously--) This is a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS See my comment re problems with the accepted answer. Also see my answer at the linked duplicate. See also many other duplicates. PS To ask specifically re what you don't understand please see general duplicates then explain your expectations so we can address them. – philipxy Jul 11 '19 at 10:16

1 Answers1

1

It allows you to join one row from the table to a different row in the table.

I've both seen this done, and used it myself, in cases where you maybe have a relationship between those rows.

Real-world examples:

  • An old version of a record and a newer version
  • Some sort of hierarchical relationship (e.g. if the table contains records of people, you can record that someone is a parent of someone else). There are probably plenty of other possible use cases, too.

SQL allows you to create a foreign key which relates between two different columns in the same table.

ADyson
  • 57,178
  • 14
  • 51
  • 63
  • "allows you to join one row from the table to a different row in the table" is not a complete description or useful characterization. We might want a row paired with itself. Correcting this, we get "allows you to join a row from the table to a row in the table"... which, since we already knew we were joining a table to itself, is an uninformative tautology. Also, *tables* represent relationships, and relation operations on tables return tables representing relationships. (Built from the input relationships per logic operators corresponding to relation operators.) – philipxy Jul 11 '19 at 10:14
  • @philipxy "We might want a row paired with itself" ...possibly yes. can you think of a use case? Feel free to add a new answer if you think you can explain it better. Personally I can't make head or tail of your jargon-filled explanations (I've seen some of them before). No doubt they're academically correct but I'm not sure your comment above about the nature of tables and relationships provides any practical assistance to the OP. No-one I know who implements relational databases for real-world uses discusses their schemas using these terms, but it doesn't stop them producing useful work. – ADyson Jul 11 '19 at 10:39
  • Yes, it's almost always the case that people cannot justify that what they coded meets a specification and/or cannot write and/or read precise specifications, but that's unfortunately how programming culture is. However if you want to learn how then you could start with putting in the effort to understand my answer at the duplicate link. (The question is a duplicate among many so it should be closed not answered.) – philipxy Jul 11 '19 at 11:06
  • PS Cross join matches a row with itself & inner join is cross join rows satisfying a further condition, so every cross & inner join can be considered to pair a row with itself. But row difference is irrelevant & seeking use cases misses the point. If you read my answer to the duplicate link you will see that we self-join to use a table's membership criteria with different argument lists/sets. – philipxy Jul 11 '19 at 11:07
  • @philipxy "seeking use cases misses the point" ...does it? The OP specifically asked what it was achieving. What would joining a row to itself achieve, in a practical sense? I can't think of a case – ADyson Jul 11 '19 at 11:10
  • I meant seeking use cases to argue against my comment was to miss the point of my comment. PS It is not necessary, & indeed it is not possible, to explain something fully by examples that don't enumerate all cases. My answer at the linked duplicate explains the general case. – philipxy Jul 11 '19 at 11:11
  • @philipxy you're seeking to make an academic definition of the self-join. I'm seeking to solve the OP's specific query with an explanation which is meaningful to them. We're just approaching the question differently. I agree it might be a duplicate of that question in a general sense, but I don't think either of the answers there would have helped the OP. – ADyson Jul 11 '19 at 11:16