msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Back to basics: inner joins

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”.

For example:

create table dept (
    dept_id number primary key, 
    dept_name varchar2(100))
/
create table emp (
    emp_id number primary key, 
    emp_name varchar2(100), 
    dept_id number,
    foreign key (dept_id) 
    references dept(dept_id)
)
/
insert into dept values (1, 'HR')
/
insert into dept values (2, 'IT')
/
insert into emp values (1, 'King', null)
/
insert into emp values (2, 'Eddie', 2)
/

To join the two tables using the old syntax:

select emp.emp_name, dept.dept_name
from emp, dept
where emp.dept_id = dept.dept_id

Using the new syntax:

select emp.emp_name, dept.dept_name
from emp INNER JOIN dept
ON emp.dept_id = dept.dept_id

The JOIN keyword is by default INNER, so the INNER keyword in optional in the example above.

Since the join column is named identically in both emp and dept tables, you can use the USING keyword:

select emp.emp_name, dept.dept_name
from emp INNER JOIN dept
USING (dept_id)

But be careful, if you want to return the join column in the select list, make sure not to qualify it. The following:

select dept.dept_id, emp.emp_name, dept.dept_name
from emp INNER JOIN dept
USING (dept_id)

Returns this error:

ORA-25154: column part of USING clause cannot have qualifier

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:

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

But, if you use dept.dept_id instead of just dept_id, you get this error:

ORA-25155: column used in NATURAL join cannot have qualifier

Finally, my preference is to use INNER JOIN…ON instead of USING or NATURAL JOIN. INNER JOIN…ON is very clear in showing on what columns you are joining.

Outer joins next…


Filed in Joins, Oracle on 20 Mar 06 | Tags: ,


Reader's Comments

  1. |

    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.

  2. |

    Never mind DESCRIPTION or COMMENTS, how about standard auditing columns like INSERT_USER. I’ve never seen a Natural Join in the wild, and never hope to,

  3. |

    Right. The advice is to never use NATURAL JOIN, and to explicitly specify join conditions using the ON clause.

  4. |

    [...] Back to basics: self joins by Eddie Awad — published on July 11th, 2006 This article is part of the Oracle Joins series. Read the whole series » 1, 2, 3, 4, 5 [...]

  5. |

    Hi eddie, How to if the table more then 2

    tq

  6. |

    Fariza, for example, if you have three tables, you would write a query like this:

    SELECT emp.emp_name, dept.dept_name FROM emp INNER JOIN dept ON emp.dept_id = dept.dept_id INNER JOIN addresses ON emp.address_id = addresses.address_id

  7. |

    What is the difference between Inner(Simple) and Equi Joins? Could some explian me please?

  8. |

    What is the difference between Equi and Inner Joins?

  9. |

    sreenivasulu

    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.

    EQUI joins can be INNER or OUTER joins: SELECT EMP.NAME, … , DEPT.DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO — inner join, equi join

    or WHERE EMP.DEPTNO = DEPT.DEPTNO (+) — outer join, equi join

    In spite of being an old Oracle hacker, I actually prefer to use ANSI syntax as it is (I think) much more readable/maintainable, eg:

    SELECT EMP.NAME, … , DEPT.DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO — outer join, equi join

    HTH

    Nigel

  10. |

    Eddie, how to list all infromation in table A that ni in table B, tq

  11. |

    FARIZA, your question is too general. You may want to take a look at the SQL MINUS operator as well as (NOT) EXISTS and (NOT) IN.

  12. |

    Thanks Eddie for SQL MINUS Solution.

    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

    thanks

  13. |

    FARIZA, you join the two tables on the column a1. Then select columns from both tables.

  14. |

    eDDIE , what i mean is like the senario bellow

    emp ( name, passport PK) with 3 DATA : (‘FORD’,’900000′ ), (‘CHANG’,’989999′),(‘CRISS;,’877777′) table edu(‘passport’ ,’level’,’result’) with 6 DATA : (‘900000′ ,’DIPLOMA’,’3.4′) (‘900000′ ,’DEGREE’,’3.0′) (‘900000′ ,’MASTER’,’4.0′) (‘989999′ ,’HIGHSCHOOL’,’2.0′) (‘989999′ ,’DEGREE’,’3.0′) (‘877777′ ,’HIGHSCHOOL’,’2.5′)

    RESULT SHOULD LIKE THIS

    NAME PASSPORT LEVEL

    FORD 900000 DIPLOMA DEGREE ASTER CHANG 989999 HIGHSCHOOL DEGREE CRISS 877777 HIGHSCHOOL

    is it posibble to do the SQL join ? thanks so much

  15. |

    FARIZA, The following joins will return the level in rows. Then you can write a stragg function to transform the level to a comma delimeted list of values.

    By the way, level is reserved, can’t use it in SQL unless you quote it.

     SELECT emp.name, emp.passport, edu."level"
       FROM emp, edu
      WHERE emp.passport = edu.passport;
    

    Or using ANSI SQL:

     SELECT emp.name, emp.passport, edu."level"
       FROM emp inner join edu on emp.passport = edu.passport;