SQL: INNER JOIN

Shafeen and Tameem playing together

I got this info from http://www.techbookreport.com/index.html


What if we want to find out how much disk space the users in the development department have? Here we have data from two different tables to look and it is here that the power of the relational model becomes apparent. At this point we need to create a relationship between the User and the PC table, and this relationship is called a JOIN in SQL/relational database terminology. There are a number of different types of JOIN available, depending on the what it is you need to achieve.
The most common form of join is called an INNER JOIN, and is used to match records between tables where there are matching values in a field common to both tables. For example we have the PCType field in the User and the PCTypeName field in the PC table, and so we would want to perform an INNER JOIN on these fields. The syntax to carry this out is fairly simple:

FROM table1 INNER JOIN table2 ON table1.field1 operator table2.field2;

The operator is usually a simple '=', but other operators can be used, such as '<>', '>', '<' and so on. For the purposes of our example we want to join the tables where the User.PCType field is equal to the PC.PCTypeName field in order to extract the PC.Disk value. Our query would look as follows:

SELECT User.LastName, User.FirstName, User.PCType, PC.Disk
FROM User INNER JOIN PC ON User.PCType = PC.PCTypeName
WHERE User.Dept="Development";

What if we want to link users to software to produce a list of software packages for each user? The UserSoftware table lists the EmpNo and the ProductID code, the first of these links to the User and the second to the Software table, thus requiring two joins:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName
FROM (UserSoftware INNER JOIN User ON UserSoftware.EmpNo = User.EmpNo) 
INNER JOIN Software ON UserSoftware.ProductID = Software.ProductID;

This generates the following result:

FirstName	LastName	ProductName
Jim	Jones	MySQL
Mary	Jones	MySQL
Mary	Jones	OpenOffice
Jim	Jones	VB6
Bharat	Patel	VB6
Terry	Jones	VB6
Terry	Jones	PaintShop Pro
Mary	Jones	MS Word

To show the PCType in the result we would also need to link in the PC table, which we can join on the field of User.PCType:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName, PC.PCTypeName
FROM ((UserSoftware INNER JOIN User ON UserSoftware.EmpNo = User.EmpNo) 
INNER JOIN Software ON UserSoftware.ProductID = Software.ProductID) 
INNER JOIN PC ON User.PCType = PC.PCTypeName;

As you can see, the useful information available from the linked tables is far higher than it is from a simple listing of the tables themselves. By linking the different tables together we can gather together the information which is inherent in the tables but is not otherwise easily accessible.