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…

Possibly related:

Tagged , | Post a Comment