Udjuni.com

Mathematics

Science

Computing

Accounting

Business

History

Agriculture

Biology

SQL cross and equi-joins

In the posting , Selecting data from Multiple tables, we briefly covered ways of selecting data from a number of tables. We also briefly talked about the use of SQL INNER JOIN. In this posting, we are going to cover SQL JOINS in detail including:

how to perform a left join
how to perform a right join
how to perform an equi-join
how to perform an outer join
how to perform a non-equi-join
how to join a table to itself

As you may recall from our posting Database Design that separating related data into different tables enable us to manipulate data about an entity while minimizing the need to duplicate or change data that does not need to. Even though the data is separated into different but somehow related tables, SQL is pretty powerful at gathering and manipulating data from across several tables, by using what in database terms is called JOINS.
Before we go on with the queries, Let us say we have data in these tables bellow:

**Department**
ID	departmentid	departmentname
1	1	HR
2	2	Sales
3	3	Billing
4	4	IT

and

**employee**
ID	employeeid	firstname	lastname	age	departmentid
1	1	Klemens	Munango	56	1
2	2	Anna	Manyima	67	1
3	3	Valens	Mudumbi	34	2
4	4	Rudolph	Kambatu	25	3
5	5	Anselma	Muyenga	45	2

SQL Cross-Join

A simple join of the two tables could be performed using the following query:

select * from employee, department;

This query produces the following results:

**Query1**
employee.ID	employeeid	firstname	lastname	age	employee.departmentid	department.ID	department.departmentid	departmentname
1	1	Klemens	Munango	56	1	1	1	HR
1	1	Klemens	Munango	56	1	2	2	Sales
1	1	Klemens	Munango	56	1	3	3	Billing
1	1	Klemens	Munango	56	1	4	4	IT
2	2	Anna	Manyima	67	1	1	1	HR
2	2	Anna	Manyima	67	1	2	2	Sales
2	2	Anna	Manyima	67	1	3	3	Billing
2	2	Anna	Manyima	67	1	4	4	IT
3	3	Valens	Mudumbi	34	2	1	1	HR
3	3	Valens	Mudumbi	34	2	2	2	Sales
3	3	Valens	Mudumbi	34	2	3	3	Billing
3	3	Valens	Mudumbi	34	2	4	4	IT
4	4	Rudolph	Kambatu	25	3	1	1	HR
4	4	Rudolph	Kambatu	25	3	2	2	Sales
4	4	Rudolph	Kambatu	25	3	3	3	Billing
4	4	Rudolph	Kambatu	25	3	4	4	IT
5	4	Anselma	Muyenga	45	2	1	1	HR
5	4	Anselma	Muyenga	45	2	2	2	Sales
5	4	Anselma	Muyenga	45	2	3	3	Billing
5	4	Anselma	Muyenga	45	2	4	4	IT

As you can see here, the query maps every employee with every department, regardless of whether that employee actually belongs to the department or not. These kind of queries are pretty useless in the real world. However, you could use a similar query to return useful information from the same two tables. The query you just looked at used what is called a cross join where each row in each table is mapped to every row in the other.

SQL Equi-Join

Now take a look at the query bellow:

select * from employee a, department b 
where a.departmentid = b.departmentid;

Simply put, this kind of join is called equi-join because it joins the tables on a common column or field.

**Query1**
a.ID	employeeid	firstname	lastname	age	a.departmentid	b.ID	b.departmentid	departmentname
2	2	Anna	Manyima	67	1	1	1	HR
1	1	Klemens	Munango	56	1	1	1	HR
5	5	Anselma	Muyenga	45	2	2	2	Sales
3	3	Valens	Mudumbi	34	2	2	2	Sales
4	4	Rudolph	Kambatu	25	3	3	3	Billing

This query only returns the correct results with each employee mapped to their correct departments, but there seems to be repeated data in terms of repeated columns. To get rid of that we will have to specify the columns to be returned. To do that we will issue the same query, but ask it to only return the employee names and the department to which they belong:

select employeeid, firstname,lastname, departmentname 
from employee a, department b 
where a.departmentid = b.departmentid;

This will return the neat and clean records of employees and their respective departments.

**Query1**
employeeid	firstname	lastname	departmentname
2	Anna	Manyima	HR
1	Klemens	Munango	HR
5	Anselma	Muyenga	Sales
3	Valens	Mudumbi	Sales
4	Rudolph	Kambatu	Billing

You can further qualify the records returned by your equi-join query. For example, if you only wanted to see employees that belong to a certain department; let us say you are intested in employees from the sales and billing departments and do not want to see those in HR. You would then issue the following query:

select employeeid, firstname,lastname, departmentname
from employee a, department b
where a.departmentid = b.departmentid and
          ( departmentname = 'Sales' OR
            departmentname = 'Billing');

This query returns the following results:

**Query1**
employeeid	firstname	lastname	departmentname
5	Anselma	Muyenga	Sales
3	Valens	Mudumbi	Sales
4	Rudolph	Kambatu	Billing

To show you what you can do with your query, using the same query you can even sort your results by say first name in either ascending or descending order. To sort the results by first name in ascending order you issue the following query:

select employeeid, firstname,lastname, departmentname
from employee a, department b
where a.departmentid = b.departmentid and
          ( departmentname = 'Sales' OR
            departmentname = 'Billing')
order by firstname;

to get the following results:

**Query1**
employeeid	firstname	lastname	departmentname
5	Anselma	Muyenga	Sales
4	Rudolph	Kambatu	Billing
3	Valens	Mudumbi	Sales

In the next few postings we will explore other method of joining data from different tables

SQL inner and outer joins
Just like we saw in SQL cross and equi-Joins, SQL is pretty powerful at gathering and manipulating data from several tables. Now we are going to talk

Udjuni.com

SQL cross and equi-joins

SQL Cross-Join

SQL Equi-Join

RELATED ARTICLES: