0

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?

Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
3
  • Thanks. So this OR behaves differently than an OR from a programming language like python or Java, right? For 2 arguments, in these languages, if argument 1 is true then true. If false, the program moves to argument 2. If argument 2 is true then true. If false, the program doesn't execute the condition. The OR discussed here operates in a totally different manner right? It is similar to them only conceptually.
    – Friedman
    Feb 24, 2021 at 17:10
  • 1
    @Friedman It's a little hard to compare relational logic to procedural logic but really the right question is one of context. Even in Java (and likely Python, though I'm a C# guy with some Java experience so can't say for sure) there are contexts where even if the first OR is true, the entire logical expression needs to still be evaluated because the outcome may differ. Usually those contexts are in predicates, which is what the ON clause in a JOIN statement in SQL defines (the predicates). In C# this would be in a lambda expression, I think Java has that concept as well...
    – J.D.
    Feb 24, 2021 at 19:29
  • 1
    ...so it depends on where you're using the OR keyword in a procedural language like Java. In a non-predicate context, outside of a lambda expression, yes the logical expression is evaluated in order until TRUE is evaluated. But in a predicate for a lambda expression (basically C# / Java's equivalent of executing relational logic), the entire expression needs to be evaluated because relationally the outcome is dependent on each predicate.
    – J.D.
    Feb 24, 2021 at 19:30
1

Logically, you can think of an inner join as a cross join with the join condition as a filter: only the rows that match the condition end up in the result.

So each row in children will be joined to those rows in parents where either fatherid or motherid (or both) are equal to the child's id.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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