PostgreSQL – Understanding the OR Operator in Code

join;operatorpostgresqlself-join

I ran into the following SQL code which (successfully) outputs a list of parents and the age of their youngest child:

SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON (parents.id = children.fatherId OR parents.id = children.motherId)
GROUP BY parents.id

The code self joins a table named "people" on itself. I just wanted to ask how does the OR operator work here? I know OR as a logical operator but here it seems it does something else. It takes two arguments and just joins on both of them. What does it have to do with logical OR?

Best Answer

Another way you can look at it, which might help you conceptualize, is the OR of an ON clause is like using a UNION ALL with the query repeated and only leveraging one predicate of each OR case like so:

-- Gets the fathers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.fatherId
GROUP BY parents.id

UNION ALL

-- Gets the mothers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.motherId
GROUP BY parents.id
Related Question