SQL 3 Table Join
A SQL 3 table join works in the same manner as joining with 2 tables does. Though, when first learning SQL, this can be a bit confusing. Let’s show an example of how this is done and then discuss it.
Skip to step 3 in the example if you want to go directly to the syntax. On a side note, you’ll want to verify that you’re on a test database before running this query to prevent any possible, although unlikely, data conflicts (e.g., one of the test tables in this example possibly has the same name as an existing table in the DB the script is run against).
/* Note that this example can be run directly against SQL Server. One small change * is required if using MySQL, which is explained on step 2. Other SQL databases may * require additional modifications. */ /* 1) Create the tables. */ CREATE TABLE Customer ( Id INT, FirstName VARCHAR(100), LastName VARCHAR(100), CONSTRAINT PK_Customer PRIMARY KEY(Id) ); -- The customer's overall experience score: positive, negative, or neutral. CREATE TABLE ExperienceScoreType ( Id TINYINT, TypeDescription VARCHAR(100), CONSTRAINT PK_ExperienceType PRIMARY KEY(Id) ); -- The customer's written feedback and their experience score. CREATE TABLE Feedback ( Id INT, CustomerId INT, FeedbackText VARCHAR(4000), ExperienceScoreTypeId TINYINT, CONSTRAINT PK_Visitor PRIMARY KEY(Id), CONSTRAINT FK_Feedback_Customer FOREIGN KEY (CustomerId) REFERENCES Customer(Id), CONSTRAINT FK_Feedback_ExperienceScoreType FOREIGN KEY (ExperienceScoreTypeId) REFERENCES ExperienceScoreType(Id) ); CREATE INDEX IX_Feedback_CustomerId ON Feedback(CustomerId); /* 2) Add data to the tables. */ DECLARE @positive TINYINT = 1, @negative TINYINT = 2, @neutral TINYINT = 3; -- If using MySQL, comment out the previous line and uncomment the next line. -- SET @positive = 1, @negative = 2, @neutral = 3; -- Add the experience score types enumeration. INSERT INTO ExperienceScoreType(Id, TypeDescription) VALUES (@positive, 'Positive'), (@negative, 'Negative'), (@neutral, 'Neutral'); -- Add a few customers. INSERT INTO Customer(Id, FirstName, LastName) VALUES (1, 'John', 'Smith'), (2, 'Sally', 'Jones'), (3, 'Nancy', 'Harrison'); -- Add Feedback for John and Sally. INSERT INTO Feedback(ID, CustomerId, FeedbackText, ExperienceScoreTypeId) VALUES (1, 1, 'The site is great!', @positive), (2, 2, 'This site is ok.', @neutral), (3, 3, 'This site isn''t great.', @negative); /* 3) Results. */ -- Pulling data from three tables to grab the feedback and feedback score per customer. SELECT c.FirstName, c.LastName, f.FeedbackText, et.TypeDescription FROM Feedback f join ExperienceScoreType et on f.ExperienceScoreTypeId = et.Id join Customer c on f.CustomerId = c.Id; /* 4) Cleanup. */ DROP TABLE Feedback; DROP TABLE ExperienceScoreType; DROP TABLE Customer;
|John||Smith||The site is great!||Positive|
|Sally||Jones||This site is ok.||Neutral|
|Nancy||Harrison||This site isn't great.||Negative|
There are four steps in this example:
- Create the tables. Three tables are being created: #Customer, #ExperienceScoreType, and #Feedback. These tables are used to track customers and their feedback including a specific feedback score: positive, neutral, or negative. Note that primary and foreign key constraints are being used in addition to an index. You don’t necessarily need to understand these pieces in full detail for this example. But, it’s a good idea to do some research into these areas before you need to create new tables.
- Add data to the tables.
- Results. As can be seen, there are three tables with two join statements. You can join additional tables in the same way by adding another join. Note that these tables are joined on their foreign keys and that the proper indexing has been done. Items like this should be carefully considered for performance reasons.
- Cleanup. The example tables are deleted.
And that's it. As has been shown, a SQL 3 table join is really no different syntax wise than joining with just one other table. You just add another join statement. And, if you wanted to join additional tables, then you'd do the same thing again (i.e., add another join statement).