sqloracle-database

difference between 'natural full outer join' and 'full outer join'


I have read W3 school notes and database system concepts book but the difference between natural full outer join and full outer join still seems vague. So far my understanding is that natural join will join on the matching column values whereas the full outer join requires the on clause. Am I understanding this correctly?


Solution

  • Natural join is basically an abomination. Don't use it.

    The problem is that natural join uses the names of columns in the tables to define the join relationship. This makes it hard to understand queries, because you don't know what the relationships are. Worse, natural join doesn't even use declared foreign key relationships.

    Use using or on instead.

    As for the difference between natural full outer join and full outer join. They both are full outer join. In the latter, you explicitly define the keys for the join condition. In the former, the database engine chooses the keys based on common names between the tables.