SELECT + JOIN

I learned SQL (basic and quite advanced stuff) at the univerity a few years ago. I’ve been using SQL commands quite often; it’s difficult to imagine an application or a web page without a database in the back-end.

Recently, I’ve discovered I remember little about using JOIN command in SELECTs. There are a few types of JOINs; however, I’ve been using mostly INNER JOIN. I decided to change that and refresh my knowledge in that field. This way, I created the examples queries and put them in the post you’re reading.

Idea:

Let’s imagine a simple database with two tables:

departments – stores information about departments in a company
employees – stores data about employees of that company

Also, let’s assume an employee can work in a department (there can be employees that don’t work in any department – freelancers; also, there can be departments where nobody works in). To create such database along with tables filled with data you can run this script (it’s dedicated for SQL Server).

Status quo:

You have the following tables in the “test” DB:

DEPARTMENTS
Dept ID	Dept Name
1	Sales
2	Games
3	HR
4	Finance
5	Systems

EMPLOYEES
Employee ID	Employee Name	Dept ID
1	Jackson	1
2	Smith	2
3	Gates	2
4	McDonald	3
5	Ohara	5
6	Toureg

Exercises:

1. Select all employees that are asigned to a department:

select e.empl_id, e.name, d.dept_id
from employees e
inner join departments d
on e.dept_id = d.dept_id

Here we use INNER JOIN (which is eqaul to JOIN). Here the only rows taken from left table are those in which the joined columns match:

empl_id	name	dept_id
1	Jackson	1
2	Smith	2
3	Gates	2
4	McDonald	3
5	Ohara	5

2. Select all employees and show to which department they are asigned (even if they are not):

select e.empl_id, e.name, d.dept_id
from employees e
left outer join departments d
on e.dept_id = d.dept_id

Here we use LEFT OUTER JOIN (which is eqaul to LEFT JOIN). You can see the difference: all rows from left table are taken, not just ones in which the joined columns match:

empl_id	name	dept_id
1	Jackson	1
2	Smith	2
3	Gates	2
4	McDonald	3
5	Ohara	5
5	Toureg	NULL

3. Select all departments and all employees that work in them (even if there is not an employee assigned):

select d.dept_id, d.name, e.name
from employees e
right outer join departments d
on e.dept_id = d.dept_id

Here we use RIGHT OUTER JOIN (which is eqaul to RIGHT JOIN). Again, all rows from first table are taken, not just ones in which the joined columns match:

dept_id	name	empl_id
1	Sales	Jackson
2	Games	Smith
2	Games	Gates
3	HR	McDonald
4	Finance	NULL
5	Systems	Ohara

4. Select all employees details and all departments id and connect employees with the departments:

select e.empl_id, e.name, d.dept_id
from employees e
full outer join departments d
on e.dept_id = d.dept_id

Here we use FULL OUTER JOIN (which is eqaul to FULL JOIN). This time, all rows from left and right tables are returned; if there’s no match row for one, the columns from the other tables contain NULLs:

empl_id	name	dept_id
6	JToureg	NULL
1	Jackson	1
2	Smith	2
3	Gates	2
4	McDonald	3
NULL	NULL	4
5	Ohara	5

Finally, there is CROSS JOIN which populates Cartesian products of rows from two tables: each row from first table is combined with all rows from the second table (see the snipped below).

select e.empl_id, e.name, d.dept_id
from employees e
cross join departments d

I hope the above mentioned examples show in simple way the difference between JOINS and will help you choose the right one to retrieve to list of appropriate values from your tables.