SQL:Joins

From virtualMV2015wiki
Jump to: navigation, search
 Home  DBMS, MySQL <

Introduction

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


Icon Objective.png

Objective : MySQL Joins

By the end of this page you will be able to:

  1. Demonstrate and describe the three join types; Inner, left and right


Icon Notes.png

Notes

  • Always create the tables with the Primary keys before the Foreign keys are created.
  • List attributes in the following order: Primary key(s), Foreign key(s), table attribute(s).

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.


# Create and populate the Customer table -----------------
DROP TABLE IF EXISTS tblDepartment;
DROP TABLE IF EXISTS tblStaff;
 
CREATE TABLE tblDepartment
  ( DepId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    DepName VARCHAR(30),
  PRIMARY KEY (DepId));
 
CREATE TABLE tblStaff
  ( StfId INT UNSIGNED NOT NULL AUTO_INCREMENT,
    DepId INT UNSIGNED,
    StfName VARCHAR(30),
  PRIMARY KEY (StfId),
  FOREIGN KEY (DepId) REFERENCES tblDepartment(DepId));

If we put data into each table

INSERT INTO tblDepartment (DepName) VALUES
  ('Menswear'),('Womenswear'),('Kitchenware'),('Electronics');
SELECT * FROM tblDepartment;
 
INSERT INTO tblStaff (DepId, StfName) VALUES
  (1,'Zac'), (1,'Chris'), (2, 'Janette'), (\N,'Andrew'), (4,'Jack'), (1,'Garrett') ;  
SELECT * FROM tblStaff;

displays ...

+-------+-------------+
| DepId | DepName     |
+-------+-------------+
|     1 | Menswear    |
|     2 | Womenswear  |
|     3 | Kitchenware |
|     4 | Electronics |
+-------+-------------+
 
+-------+-------+---------+
| StfId | DepId | StfName |
+-------+-------+---------+
|     1 |     1 | Zac     |
|     2 |     1 | Chris   |
|     3 |     2 | Janette |
|     4 |  NULL | Andrew  |
|     5 |     4 | Jack    |
|     6 |     1 | Garrett |
+-------+-------+---------+

JOIN (INNER JOIN)

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

# JOIN(INNER JOIN)
SELECT * FROM tblDepartment
  INNER JOIN tblStaff 
  ON tblDepartment.DepId = tblStaff.DepId;
 
displays ..
 
+-------+-------------+-------+-------+---------+
| DepId | DepName     | StfId | DepId | StfName |
+-------+-------------+-------+-------+---------+
|     1 | Menswear    |     1 |     1 | Zac     |
|     1 | Menswear    |     2 |     1 | Chris   |
|     1 | Menswear    |     6 |     1 | Garrett |
|     2 | Womenswear  |     3 |     2 | Janette |
|     4 | Electronics |     5 |     4 | Jack    |
+-------+-------------+-------+-------+---------+

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)

SELECT * FROM tblDepartment
  LEFT JOIN tblStaff 
  ON tblDepartment.DepId = tblStaff.DepId;
 
displays ...
 
+-------+-------------+-------+-------+---------+
| DepId | DepName     | StfId | DepId | StfName |
+-------+-------------+-------+-------+---------+
|     1 | Menswear    |     1 |     1 | Zac     |
|     1 | Menswear    |     2 |     1 | Chris   |
|     1 | Menswear    |     6 |     1 | Garrett |
|     2 | Womenswear  |     3 |     2 | Janette |
|     3 | Kitchenware |  NULL |  NULL | NULL    |
|     4 | Electronics |     5 |     4 | Jack    |
+-------+-------------+-------+-------+---------+

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)

SELECT * FROM tblDepartment
  RIGHT JOIN tblStaff 
  ON tblDepartment.DepId = tblStaff.DepId;
 
displays ...
 
+-------+-------------+-------+-------+---------+
| DepId | DepName     | StfId | DepId | StfName |
+-------+-------------+-------+-------+---------+
|     1 | Menswear    |     1 |     1 | Zac     |
|     1 | Menswear    |     2 |     1 | Chris   |
|     2 | Womenswear  |     3 |     2 | Janette |
|  NULL | NULL        |     4 |  NULL | Andrew  |
|     4 | Electronics |     5 |     4 | Jack    |
|     1 | Menswear    |     6 |     1 | Garrett |
+-------+-------------+-------+-------+---------+

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


Icon Notes.png

Notes : Open Database Connectivity (ODBC) Compatibility

For Open Database Connectivity (ODBC) the additional words INNER and OUTER are included.

For further examples see WELL HOUSE CONSULTANTS LTD, 2010)[1]

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

CREATE TABLE tblSupplier
  (SupId INT UNSIGNED NOT NULL AUTO_INCREMENT, SupName VARCHAR(30), SupAddress VARCHAR(50), SupPhone VARCHAR(20),
  PRIMARY KEY (SupId));
CREATE TABLE tblOrder
  (OrdId NOT NULL AUTO_INCREMENT, SupId INT UNSIGNED, OrdCost FLOAT(7,2),
  PRIMARY KEY (OrdId),
  FOREIGN KEY (SupId) REFERENCES tblSupplier(SupId));

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.

SELECT  tblOrder.OrdId, tblOrder.OrderCost, tblOrder.SupId, tblSupplier.SupName
  FROM tblOrder
  INNER JOIN tblSupplier ON tblOrder.SupId = tblSupplier.SupId ;

Example 2: Medicine management

Fully attributed Entities:

  • Doctor(DocId, DocName)
  • Patient(PatId, DocId*, PatFirstName, PatLastName, PatAddress, PatDateofBirth)
  • Medicine(MedId, MedName)
  • PatientMedicine(PatId*, MedId*, dosage)

Attribute notes:

  • 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.

 
  SELECT * FROM tblPatient
    ORDER BY PatLastName, PatFirstName;

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

  SELECT tblPatient.PatFirstName AS FirstName, 
         tblPatient.PatLastName AS LastName,
         tblDoctor.DocName AS Doctor
    FROM tblPatient
    INNER JOIN tblDoctor ON tblPatient.DocId = tblDoctor.DocId;

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'.

  SELECT PatFirstName, PatLastName 
    FROM tblPatient
    WHERE PatLastName LIKE 'Cor%' AND 
          (DocId = 'Smit01' OR DocId = 'Fran01');

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

  SELECT tblPatientMedicine.PatId, tblMedicine.MedName
    FROM tblPatientMedicine
    INNER JOIN tblMedicine ON tblPatientMedicine.MedId = tblMedicine.MedId;

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.

  SELECT * FROM tblEmployee
    ORDER BY EmpFirstName DESC;

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

  SELECT tblEmployee.EmpFirstName, tblEmployee.EmpLastName
    FROM tblEmployee
    INNER JOIN tblLegalTeam ON tblEmployee.LTeamId = tblLegalTeam.LTeamId
      WHERE tblEmployee.EmpLastName LIKE 'BRO%' AND tblLegalTeam.LTeamName = 'Property';

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

  SELECT tblEmployee.EmpId, tblJobHours.CJobId
    FROM tblJobHours
    INNER JOIN tblEmployee ON tblJobHours.EmpId = tblEmployee.EmpId;

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

  SELECT tblLegalTeam.LTeamId, tblLegalTeam.LTeamName, COUNT(tblEmployee.EmpId)
    FROM tblEmployee
    INNER JOIN tblLegalTeam ON tblEmployee.LTeamId = tblLegalTeam.LTeamId
    GROUP BY tblLegalTeam.LTeamId;

Icon References.png References

  1. WELL HOUSE CONSULTANTS LTD (2010) MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN. Retrieved August 10, 2010 from http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

SQL:Joins. (2017). In virtualMV's ( Michael Verhaart ) wiki. Retrieved December 16, 2017, from http://www.virtualmv.com/wiki/index.php?title=SQL%3AJoins    (zotero)