Continuing the series about joins, today it’s about inner joins. An inner join (aka simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. When you hear people talking about a “join”, usually they are referring to an “inner join”.

There is one additional join option called “natural join”, using the NATURAL JOIN clause. With natural joins, you don’t specify a join condition. A natural join joins on columns with the same names from each table. For example, because the column dept_id is named the same in both emp and dept tables, the following is a legal SQL that implicitly joins emp and dept on dept_id:

select emp.emp_name, dept.dept_name
from emp NATURAL JOIN dept

Similar to USING, if you want to return the join column in the select list, make sure not to qualify it. The following is legal:

Reader's Comments

I suppose they came up with natural joins for SQL implementations that don’t support foreign keys (surely the only natural way to generate a default join condition). Otherwise I can’t imagine why anyone would think that the “natural” way to join two tables would be to match columns that happen to have the same name in both tables.

If you have column names like “DESCRIPTION” or “COMMENTS” it will try to join on those and give wrong results. If you have column names like EMPLOYEES.EMP_DEP_ID as an FK column to DEPARTMENTS.DEP_ID, they won’t match and you will get wrong results. If the corresponding columns do happen to have matching names, your application could break when a new column is added, if you are unlucky with the name. The idea of anything depending on a naming standard is just bizarre.

an INNER join is one that isn’t an OUTER join. Or to be more specific: for a row to occur in the result relation, its component pieces must be present in BOTH of the source relations. An OUTER join, in contrast, can supply null values for “missing” rows (in either or both of the source relations, depending on whether this is a LEFT, RIGHT or FULL outer join).

an EQUI join is a special case join in which the predicates are based on equality conditions eg
WHERE EMP.DEPTNO = DEPT.DEPTNO. Foreign key joins are examples of equijoins.

Another Question
I have 2 table :
T1 have a1,b1,c1 fields
T2 have x2,x3,x4, a1 as FK from T1. These two table has a one to many relationship. How I want to display the two table as one record?
exp result :
a1,b1,c1,x2,x3,x4
a1,b1,c1,x3,x4
a1,b1,x2,x3,x4