SQL INNER and OUTER JOINS

One of the areas I feel I need to brush up on are joins. I know they “join” tables together, but exactly how and why I’m not sure. So I set about to do some testing to learn.

First some setup…

-- create department table
CREATE TABLE department ( departmentid number(5) NOT NULL,
	departmentname varchar(20),
	CONSTRAINT PK_dptid PRIMARY KEY (departmentid) );
-- create employee table
CREATE TABLE employee ( lastname VARCHAR2(20) NOT NULL ,
	departmentid NUMBER(5),
	CONSTRAINT FK_DEPTID FOREIGN KEY (departmentid)
		REFERENCES department(departmentid) ON DELETE CASCADE);
-- Insert data into DEPARTMERNT table
insert into department values (31,'sales');
insert into department values (33,'engineering');
insert into department values (34,'clerical');
insert into department values (35,'marketing');
-- Insert data into EMPLOYEE table
insert into employee values ('rafferty',31);
insert into employee values ('jones',33);
insert into employee values ('steinberg',33);
insert into employee values ('robinson',34);
insert into employee values ('smith',34);
insert into employee values ('jasper',NULL);

Now for some testing.
If you query the employee table by itself you will get the employee name and a department number. The number by itself is not very helpful or user friendly. However all the department names are in the department table not the employee table. I assume this would be done to conserve space and improve performance?
Anyway, to get a more user friendly report you have to join the employee table and the department tables.
The INNER JOIN below will display all rows that have a matching department.

select lastname, departmentname
	from employee
	inner join department
	on employee.departmentid = department.departmentid;

Note however that it does not display employees without a department or departments without employees.
To do that you have to do either,

a LEFT OUTER JOIN to display employees without a department

select lastname, departmentname
	from employee
	left outer join department
	on employee.departmentid = department.departmentid;

a RIGHT OUT JOIN to display departments without employees,

select lastname, departmentname
	from employee
	right outer join department
	on employee.departmentid = department.departmentid;

or a FULL OUTER JOIN to display departments without employees and employees without departments

select lastname, departmentname
	from employee
	FULL outer join department
	on employee.departmentid = department.departmentid;

The LEFT and RIGHT of the OUTER JOINs come from the fact that employee is on the LEFT hand side of the JOIN statement and department is on the RIGHT hand side. I always wondered what the LEFT and RIGHT meant! Now I know.

Most of this information come thanks of http://en.wikipedia.org/wiki/Join_(SQL) and some testing of my own.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s