Use of NATURAL JOIN

February 18, 2023

According by the task I’m doing I need to change INNER JOIN to NATURAL JOIN and get the same result as this result of inner join

I got that result with this query:

SELECT person_order.order_date,
    person.name || ' (' || 'age:' || person.age || ')' AS person_information
FROM person_order
    INNER JOIN person ON person_order.person_id = person.id
ORDER BY 1,
    2;

Here is what my tables look like my tables

I’m trying to use subquery in a FROM statement but now result is differ with previous query different rusult

SELECT pers_ord.order_date,
    person.name || ' (' || 'age:' || person.age || ')' AS person_information
FROM (
        SELECT order_date
        FROM person_order
    ) AS pers_ord
    NATURAL JOIN person
ORDER BY 1,
    2;

>Solution :

Could you try this:

SELECT pers_ord.order_date,
    person.name || ' (' || 'age:' || person.age || ')' AS person_information
FROM (
        SELECT order_date, person_id AS id
        FROM person_order
    ) AS pers_ord
    NATURAL JOIN person
ORDER BY 1,
    2;

Looking at examples here you can see the columns on which the join is performed need to be the same. And in your person_order table the person_id column is not matching the id in the person table.