Page 1 of 1

Join Three Tables

#1 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6342
  • View blog
  • Posts: 25,595
  • Joined: 12-December 12

Posted 12 October 2015 - 08:18 AM

This follows-on from my tutorial Table Joins, but the code to create the two tables from that tutorial is repeated here:
CREATE DATABASE Critters
GO
USE Critters 
GO

CREATE TABLE PetTypes (
    PetTypeID int PRIMARY KEY,
    PetType varchar(40)
)
GO

INSERT INTO PetTypes VALUES 
    (1, 'Dog'), (2, 'Cat'), (3, 'Frog'), (4, 'Alligator');

CREATE TABLE Pets (
    PetID int PRIMARY KEY,
    PetName varchar(30),
    PetTypeID int,
    CONSTRAINT fkPetType FOREIGN KEY (PetTypeID) REFERENCES PetTypes(PetTypeID)
)
GO

INSERT INTO Pets VALUES
    (1, 'Tiddles', 2), (2, 'Fluffy', 2), (4, 'Rover', 1),
    (5, 'Kermit', 3), (6, 'Nemo', NULL);


Now we will add a third table:
CREATE TABLE Fed (
    FedID int IDENTITY(1,1) PRIMARY KEY,
  PetID int,
    FedDay varchar(10) DEFAULT 'Monday',
    FedPeriod varchar(10),
  CONSTRAINT fkPet FOREIGN KEY (PetID) REFERENCES Pets(PetID)
)
GO
INSERT INTO Fed (PetID, FedPeriod) VALUES 
    (1, 'Morning'), (1, 'Afternoon'), (4, 'Morning'), (6, 'Evening');


PetID       PetName                        PetTypeID
----------- ------------------------------ -----------
1           Tiddles                        2
2           Fluffy                         2
4           Rover                          1
5           Kermit                         3
6           Nemo                           NULL

PetTypeID   PetType
----------- ----------------------------------------
1           Dog
2           Cat
3           Frog
4           Alligator

FedID       PetID       FedDay     FedPeriod
----------- ----------- ---------- ----------
1           1           Monday     Morning
2           1           Monday     Afternoon
3           4           Monday     Morning
4           6           Monday     Evening


Start with INNER JOINs:
-- only where they exist in all tables
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    INNER JOIN Pets ON Fed.PetID = Pets.PetID 
    INNER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

FedDay     FedPeriod  PetName                        PetType
---------- ---------- ------------------------------ ----------------------------------------
Monday     Morning    Tiddles                        Cat
Monday     Afternoon  Tiddles                        Cat
Monday     Morning    Rover                          Dog


This is the most common, and the one we usually start with. It will only show details for pets that have been fed, and that have a type.

The statement could be bracketed like this, which might make it clearer what is happening:
SELECT FedDay, FedPeriod, PetName, PetType 
FROM (Fed INNER JOIN Pets ON Fed.PetID = Pets.PetID)
    INNER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;


We can think of it as the second INNER JOIN being joined with the results of the first INNER JOIN. The database itself determines how the results will be obtained using a query execution plan.
-- all Fed details, all fed pets, even if type is not present
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    LEFT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    LEFT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

FedDay     FedPeriod  PetName                        PetType
---------- ---------- ------------------------------ ----------------------------------------
Monday     Morning    Tiddles                        Cat
Monday     Afternoon  Tiddles                        Cat
Monday     Morning    Rover                          Dog
Monday     Evening    Nemo                           NULL


Remember, LEFT refers to the table written on the left of the join. We are showing all the Fed details, then all the fed Pets, even if they don't have a PetType (i.e. Nemo).

Other variations are now shown, with comments that attempt to describe the results.
-- match Fed to Pets, even if type not specified
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    INNER JOIN Pets ON Fed.PetID = Pets.PetID 
    LEFT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

-- match Fed to Pets, but display all types (but see the notes that follow)
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    INNER JOIN Pets ON Fed.PetID = Pets.PetID 
    RIGHT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

-- all pets, even if no type
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    RIGHT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    LEFT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID
    ORDER BY PetName;

-- all pets and all types, but not a pet without a type
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    RIGHT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    RIGHT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID
    ORDER BY PetName;

-- all pets and all types
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    RIGHT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    FULL JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID
    ORDER BY PetName;

-- would show all fed, pet, and type details
-- (appears the same as the previous query
-- because there are no 'fed' details unassociated 
-- with a pet)
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed
    FULL JOIN Pets ON Fed.PetID = Pets.PetID 
    FULL JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

-- all pets, unless they don't have a type
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    RIGHT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    INNER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;


Of particular note is this variation:
-- all pets and all types, but not a pet without a type
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed 
    RIGHT OUTER JOIN Pets ON Fed.PetID = Pets.PetID 
    RIGHT OUTER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID
    ORDER BY PetName;

FedDay     FedPeriod  PetName                        PetType
---------- ---------- ------------------------------ ----------------------------------------
NULL       NULL       NULL                           Alligator
NULL       NULL       Fluffy                         Cat
NULL       NULL       Kermit                         Frog
Monday     Morning    Rover                          Dog
Monday     Morning    Tiddles                        Cat
Monday     Afternoon  Tiddles                        Cat

(6 row(s) affected)


There are 6 rows displayed. Compare it to the 2 x FULL JOIN version:
SELECT FedDay, FedPeriod, PetName, PetType 
FROM Fed
    FULL JOIN Pets ON Fed.PetID = Pets.PetID 
    FULL JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID;

FedDay     FedPeriod  PetName                        PetType
---------- ---------- ------------------------------ ----------------------------------------
Monday     Morning    Tiddles                        Cat
Monday     Afternoon  Tiddles                        Cat
Monday     Morning    Rover                          Dog
Monday     Evening    Nemo                           NULL
NULL       NULL       Fluffy                         Cat
NULL       NULL       Kermit                         Frog
NULL       NULL       NULL                           Alligator

(7 row(s) affected)


Which produces 7 rows.

The first version with 2 RIGHT JOINs we might expect to display all possible rows. However, with right joins, execution starts at the rightmost table and works to the left. So all the PetTypes are listed, but not Nemo because he doesn't have a PetType. For all these Pets (that have a PetType) it shows their matching Fed details (or NULLs if there aren't any).

We haven't noticed this before because, with LEFT JOINs, execution starts with the leftmost table and works to the right, which is how we have been interpreting the statements up until now.

So, for all possible variations, we use FULL JOINs. You shouldn't need to use them often though, as you'll end up with large resultsets with lots of nonsense (NULL) rows.

This post has been edited by andrewsw: 12 October 2015 - 09:35 AM


Is This A Good Question/Topic? 2
  • +

Replies To: Join Three Tables

#2 ketariman  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 13-March 16

Posted 13 March 2016 - 07:04 AM

Thanks for this tutorial :bigsmile:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1