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
MultiQuote
| 