CODEWITHSUNDEEP
sqloracle-databaseself-join
m1547
m1547

Reputation: 87

How to understand this query?

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.

Upvotes: 0

Views: 252

Answers (1)

ADyson
ADyson

Reputation: 61984

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.

Upvotes: 1

Related Questions