Using Aliases and Joining Related Tables

During this blog post, I am going to explain the JOIN operator in more detail and how it can be worked to your advantage when customising result sets.

When using databases that have been normalized (the act of organising and sorting data in a particular way that reduces the amount of redundancy and duplication in a table - don't worry a future blog post will cover this in much more detail!), data that is required for a single result set output could be located in more than one table within the database. The JOIN operator is a perfect way to combat this problem. 

Using Aliases

Before I begin explaining about the different kinds of joins, firstly I will discuss Aliases and how they are really useful when creating joins.

If a column with the same name exists in more than one table that you are joining together, you must use what is called an "Alias" in order to differentiate between the 2 columns. In other words, an alternative name which, once declared, can be used throughout the rest of the script. The code snippet below shows an example: 

This is definitely a good practice to use as it becomes invaluable for creating longer scripts, where column names do tend to be duplicated. Aliases can also be used to shorten really long table names as you really don't want to be typing out impossibly long table names over and over again if there's an easier method or way of getting around it. 

Inner Join

Inner joins return all related fields from both tables where there is at least one match in both tables. An example of this is: 

Outer Join

Outer joins can be used to return all rows from one table regardless on whether there are related values in the other table or not. 

Outer joins can be split into 3 different categories: 

Left Outer Join

Left Outer Joins returns all rows from the left table in the query, regardless of there being any matches.

Right Outer Join

Right Outer Joins returns all rows from the right table in the query, regardless of there being any matches.

Full Outer Join

Full Outer Joins return rows where there is a match in one of the tables, regardless of what table it is.

Self Join

Self joins are used when a table has been referenced more than once because it uses a different alias each time the table is referred to. In effect, this join is typically just another form of INNER JOIN however it joins to itself.

In order to improve the result set using the tutorial, I first generated a new column in the Item table called ParentId. This will allow items to not only be individual but can be used as part of another item for sale. For example, the company manufactures screws and these screws are used to put together a table. See the code for this below:

To ensure that this worked, I clicked on the Item table in the Object Explorer and clicked on "Select Top 1000 Rows" and your result set should look similar to this:

Next, I would advise putting some ParentIds into the field to demonstrate some form of "hierarchy". To do this, right click on the Item table again but this time click "Edit Top 200 Rows". Manually enter numbers between 1 and 10 randomly throughout the table. Note that you don't need to do this for every single row, only a few will be sufficient: 

Now run the following query:

  

Cross Join

Cross joins select all data rows from both tables, regardless on whether they are related or not. This can be useful (as used in the code example below) as you can select the same item for every customer (even if the relationship isn't there yet) and then perform an INSERT statement (this will be covered later). See the code below for a simple Cross Join: