SQL:Joins

Overview

 * Joins are used to create relations between tables.
 * There are Inner, Left and Right.
 * This page shows examples of the Inner join in a select statement

Creating example tables and data
For this example a business is divided up into departments, and each staff member may be allocated to zero or one department.

If we put data into each table

displays ...

JOIN (INNER JOIN)
An inner join looks at the data and joins the data only showing records that are not null.

Notice that Andrew from the staff table AND Kitchenware from the Department table is missing

LEFT JOIN (LEFT OUTER JOIN)
A left join shows ALL the data from the first (left) table with associated data from the second (right) table. Here, all the data from the Department table with any related staff. If there are no staff associated, the staff area is null (e.g. Kitchenware)

Notice that Andrew from the staff table is missing and Kitchenware is included

RIGHT JOIN (RIGHT OUTER JOIN)
A right join shows ALL the data from the second (right) table with associated data from the first (left) table. Here, all the data from the Staff table with any related departments. If there are no departments associated, the department area is null (e.g. Andrew)

So Andrew from the staff table is included and Kitchenware is missing

For further examples see WELL HOUSE CONSULTANTS LTD, 2010)

Example 1: One-to-many
ERD: [Supplier] -||---0< [Order]

Fully attributed Entities:
 * Supplier ( SupId, SupName, SupAddress, SupPhone)
 * Order ( OrdId, SupId*, OrdCost)

In MySQL this would be

Sample Select statements
Display all orders showing OrdId, OrderCost, SupId and SupName

So we need to start with the Order table and JOIN it to the Supplier table.

Example 2: Medicine management
Fully attributed Entities: Attribute notes:
 * Doctor( DocId, DocName)
 * Patient( PatId, DocId*, PatFirstName, PatLastName, PatAddress, PatDateofBirth)
 * Medicine( MedId, MedName)
 * PatientMedicine( PatId*, MedId* , dosage)
 * Each patient can only have one doctor.
 * The PatientMedicine has a concatenated Primary key PatId + MedId. This will mean that a patient can only ever have a medicine dosage listed once.

Sample Select statements
1. List all the patient details (from patient table only), in alphabetical order of last name and within the last name, in first name order.

2. List the patient’s first and last names and their respective doctor’s names (and give meaningful column headings).

3. List first and last names of all patients, where the last name starts with the letters 'Cor', and the doctor id is 'Smit01' or 'Fran01'.

4. For each type of medicine prescribed, list the patient id and name of the medicine.

Example 3: Legal client management
Fully attributed Entities:
 * LegalTeam( LTeamId, LTeamName)
 * Employee( EmpId, LTeamId*, EmpFirstName, EmpLastName, EmpAddress)
 * ClientJob( CJobId, CJobName)
 * JobHours( EmpId*, CJobId* , JHrsWorked)

Notes:
 * Legal Team contains information about the Legal team’s role e.g. Family Law,Property etc

Sample Select statements
List all the employee details (from the employee table only) in alphabetical order on the First Name in descending order.

List the employees first and last names, where the last name starts with the letters 'BRO', in Property Legal_Team.

List the employee’s id and the Job_nos they have been working on.

For each Legal Team, list the Team_id and Name and the number of employees who work in that team.