Now that we have set a basis for how to control a view from one table, we should have enough knowledge to tackle how we incorporate information from two or more tables into one view. Joining in SQL is extremely useful and is used time and time again in a relational database. Often times we see information stored in different tables in a relational database. For example, a transaction table and a customer table are a very common occurrence. The transaction table stores all information related to a purchase such as the customer who made the purchase, the total amount, the product(s) purchased, etc. The customer table stores information based on the customer such as customer name, address, occupation, company, etc. A join would be useful if we want to combine information from both of these tables into a single view, like viewing what types of occupations purchase certain products.
We are primarily going to focus on the inner join in this tutorial. An inner join is a type of join that selects records from two different tables that have matching values in both tables. There are other types of joins that will match values if there are matching values in both tables but will still include every record from both tables but there will be many missing values if there is not a matching pair. The concept is the same when writing the query, however. There are two steps, or lines, that are necessary when writing a join. The first line should be the name of the table you are joining to, and the second is indicating the column that you want to match in each table. Joins also bring up another concept in SQL called aliasing. It is required to alias tables in a query and this makes sense because you are writing a query to two tables, and if both tables have columns with identical names an alias is required to specify which column you are looking at. The concept of an alias will be much better understood in an example.
In the example below, we are using information from the header table to get CustomerID and information from the detail table to get ProductID and joining both of these tables by their common column called SalesOrderID. The aliases are labelled after each table with a space. The alias can be a simple letter or phrase. When referring to the aliased column use the alias with a period followed by the column name. As you can see in the example, both columns are identical, so an alias is required.
