SQL - Part 6: Joining Tables


To see the effect of automatic joins in the SQL Wizard.

Background Information

In SQL, there is a term known as JOIN. This means that two tables can be operated on at the same time in such a scenario where one table has a Primary Key, and the other has a Foreign Key which matches the primary key of the first table. Essentially, this means all of the data in the row of Table 2 which has the foreign key is related to the data in the row in Table 1 with the matching primary key.

For the purposes of working with templates in AutoTag, it is important that you at least understand the requirements to join two tables: One table has a primary key (For example, the Employees table has an EmployeeID column) and one table has a matching foreign key (For example, the Orders table also has an EmployeeID column).

In the context of more advanced SQL knowledge, this primary-foreign key relationship helps us store data with a one-to-many relationship (So one employee could have many orders).


Inserting a Tag, ForEach Tag, SQL - Part 1: Basic Selection

Starting Template

Start with the SQL Wizard open. It doesn’t matter what ForEach tag you are on, or what template you have open. If you don’t know how to open this wizard yet, create a ForEach tag with a SQL datasource, and click the Wizard button in the AutoTag ribbon, or check outSQL - Part 1: Basic Selection.


1. Drag FirstName and LastName from Employees

First we’ll drag in the FirstName and LastName columns from the Employees table. This is pretty self-explanatory by now.

Show Me How!

2. Drag CompanyName and ContactName from Customers

Now, we drag CompanyName and ContactName from Customers. If you read the Background Information section above, you know that one requirement for SQL joins is that one table has a primary key, and the other has a foreign key. Well looking through the Customers table, there is no key except CustomerID. In the next step we’ll find out just what happened with the automatic joins (which actually used a middle-man table, Orders to associate Customers with every Employee they have ordered from)

Show Me How!

3. View the automatically detected joins

As you can see, Orders has an EmployeeID and CustomerID foreign keys, so we can join Employees to Orders—employee A took care of order 1—and we can join Customers to Orders—customer C was on the receiving end of order 1. Thus, order 1 relates employee A to customer C.

Show Me How!

Quiz Yourself!

What do you need in order to join two tables?
Two tables with matching data.
A primary key and foreign key in the same table
A primary key in one table and a matching foreign key in another.
Two tables that have been related by some device in SQL that I don’t know about
What exactly does it mean to join?
To become one with the data
Two tables are merged and missing columns are automatically filled in
Two tables contain data that is related in some way, such as the fact that an Office contains several Employees.
Two or several rows of data are merged together
True/False: AutoTag automatically takes care of joins for you in almost every case
True. The SQL Wizard takes care of every single join I could possibly imagine, even ones I can’t imagine and didn’t intend.
False. It’s a really complicated process that the SQL Wizard doesn’t really help me with.
True. The SQL Wizard is adept at detecting where tables can be joined, and adds the join for me.
False. But the SQL Wizard makes suggestions as to how I should join the tables


You have completed this tutorial. We recommend taking a look at the XPath - Part 1: Basic Selection tutorial next!