difference between 'natural full outer join' and 'full outer join' [closed]

Question

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 naturally on the matching column values where in full outer join we must explicitly state on clause. Should we? Or I am making some mistakes in my understanding?

Answer 1

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.

Answer 2

Well, the correct terms for these joins are: 1. INNER JOIN 2. LEFT OUTER JOIN 3. RIGHT OUTER JOIN 4. FULL OUTER JOIN 5. Natural JOIN

1. INNER JOIN: (the typical Join operation, which uses some comparison operator like = or ). These include equi-joins and natural joins. Inner Joins use a comparison operator to match rows from two tables based on the values in common columns from each table.

2. OUTER JOIN: Outer joins can be a Left, a Right, or Full Outer Join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

2.a. LEFT JOIN or LEFT OUTER JOIN: The result set of a Left Outer Join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

2.b. RIGHT JOIN or RIGHT OUTER JOIN: A Right Outer Join is the reverse of a Left Outer Join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

2.c. FULL JOIN or FULL OUTER JOIN: A Full Outer Join returns all rows in both the Left and Right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

3.Natural JOIN: A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

If the SELECT statement in which the NATURAL JOIN operation appears has an asterisk (*) in the select list, the asterisk will be expanded to the following list of columns (in this order):

All the common columns
Every column in the first (left) table that is not a common column
Every column in the second (right) table that is not a common column