DEV Community: Goran Kortjie

Learn SQL: Microsoft SQL Server - Episode 16: Outer Join | Left and Right

Goran Kortjie — Sat, 08 Jan 2022 22:33:25 +0000

In this last entry, I want to revisit how Join operations work, we know so far they will match the data from one table to another table and pull the joining records.

However sometimes that might not be enough. In some cases you might want to pull the records from one table and then joining records from the other table with empty values, whenever there is no matching records.

Thats sounds confusing so lets dive into SSMS to make it more clear.

Lets write a SQL query that will pull all customers who have placed an order. Any number of orders. We will be using Sales.SalesOrder.Header and Sales.Customer tables.

Sales.Customer is the list of customers and Sales.SalesOrderHeader is the list of all the orders.

We will construct this query as follows.

Select * from Sales.Customer a
join Sales.SalesOrderHeader b
on a.CustomerID = b.CustomerID

I made use of aliases to make it a little easier to construct this query statement, by giving Sales.Customer an Alias of a and Sales.SalesOrderHeader the Alias of b

We basically want to join the two tables based on the matching CustomerID.

Notice how many results was returned from this statement. 31465 total rows.

We will add a comment inside our query to indicate this result. A comment is not evaluated by SQL. It is typically used by us as programmers and developers to explain our intent to other programmers and our future selfs when we return to our code.

The way we write comments in SQL is by prefixing our comment with two dashes.

-- total (comment)

for example

-- total number of records

If we want to get a list of all the customers, whether they have placed orders or not for this result set, if they haven't placed an order then all the sales records will be Null or empty.

Left

To accomplish this we need to write an Outer Join. To write an Outer Join we need to decide which table do we want to get all the data from. We specify the table we want to get all the data from by indicating it as either Left or Right.

Select * from Sales.Customer a
left join Sales.SalesOrderHeader b
on a.CustomerID = b.CustomerID

In the statement above, Left refers to the Sales.Customer table. Which means we want to get all the data from Sales.Customer and if there are any matching records in the Sales.SalesOrderHeader table return those records, otherwise leave those records as Null.

Right

The Right method works exactly like the Left, except it refers to the table on end of the query statement. In our example above it would refer to Sales.SalesOrderHeader table.

That concludes this series on Learn SQL. I must say I was intimidated by SQL when I first heard of it back in 2020.

After going on this journey I have realised my error in judgement and found myself understanding the concepts quite easily, since they resemble javascript in many ways, thankfully not all ways.

Once again I hope you enjoyed this series and have a great 2022. Namaste.

Learn SQL: Microsoft SQL Server - Episode 15: Deleting records from a table

Goran Kortjie — Sat, 08 Jan 2022 21:49:50 +0000

Time for the scariest segment of SQL query statements. As in the previous entry, we need to make sure that we are working with the correct data.

Continuing with our previous episode, we will be working in the HumanResourcesDepartment table. This is where we will be performing the delete operation, we will delete the row with the DepartmentID = 17.

To construct a delete statement we need to use the following syntax.

delete from [table-name]
where [column-name] = value

Notice how this allows us to wipe out the entire HumanResourcesDepartment table if we were to omit the Where clause.

We can use any Column-Name in the table to specify which row we want to delete. Generally using a key would be safest for deleting a single record in a table.

As a side-note...We can also delete multiple rows simultaneously by specifying a column that has a value present in more than one record or row. For example in the GroupName with the value 'Executive General and Administration'. If we pass in this value in our Where clause it will remove each row containing 'Executive General and Administration' in it's GroupName column.

Be safe

To make this perfectly clear, we need to ensure the row or rows we want to delete are the values specified in the Where clause.

I personally would double-check the row or rows I need to delete by using a Select statement.

Select * from [Table-Name]
Where [column-name] = [value]

By using this statement before we perform our delete operation we can confirm that we are working with the correct data.

Always make sure you are working with the correct data and never perform a Delete operation without a Where clause.

Thats all from me, I hope you enjoyed this episode and look forward to the next and final entry on Learn SQL.

Learn SQL: Microsoft SQL Server - Episode 14: Update records in a table

Goran Kortjie — Sat, 08 Jan 2022 21:42:48 +0000

Have you ever wondered how to update your data in a table by using SQL Statements? In this entry we are going to explore and demonstrate how we can accomplish this exciting task.

The task of updating data comes in handy, when we find the data in the table to be incorrect, or in a scenario where we need to make some changes based on some new information we received.

To demonstrate this, let’s use a SELECT statement on the HumanResourcesDepartment table in SSMS.

The result set of running this SQL statement gives us the data currently inside the HumanResourcesDepartment table.

I have inserted the data from the previous episode, back into the table for consistency.

Imagine we made a mistake with the last entry when we inserted the data into the table and instead of 'Growth and Education', the GroupName column should contain 'Growth and Development'. To accomplish this, we need to construct an Update Query Statement.

The syntax for the update statement is as follows:

update [Table-Name] 
set [Column-Name] = value
Where [Unique-Identifier] = [Identifier]

Ensure the unique identifier is in fact unique in the table, usually the key for the table would be unique and therefore makes an excellent value to use.

By following this syntax, if we want to change the value in GroupName, this is how it would look.

update [HumanResources].[Department]
set GroupName = 'Growth and Development'
Where DepartmentID = 17

When we execute this Update Statement and then check the data in the HumanResourcesDepartment table, we should see the data has updated.

Just to make it absolutely clear, the Where clause inside of your Update Statement is very scary, if we provide it with incorrect information it could end up updating data where we did not intend or worse update an entire column of data.

I personally would double-check the column I need to update by using a Select statement.

Select * from [Table-Name]
Where [Unique-Identifier] = [Identifier]

By using this Select Statement before we perform our Update Statement we can confirm that we are working with the correct data.

Update Multiple Columns

We can even update multiple columns in one SQL statement. For instance, we want to change the value of the Name column from 'Learning' to 'Upskill'

All we need to do is separate the Column-Name in the SET Clause with dashes.

update [Table-Name] 
set [Column-Name] = value, [Column-Name] = value, ...
Where [Unique-Identifier] = [Identifier]

We can update as many columns as we want for the given table.

Let’s update the Name and ModifiedDate for the row with the DepartmentID = 17.

update [HumanResources].[Department]
set GroupName = 'Growth and Development', Name = 'Upskill', ModifiedDate = getDate()
Where DepartmentID = 17

That's all, this is how we can update records in a table. Easy right!

I really hope this entry was informative and helps you along your journey to mastering SQL.

Learn SQL: Microsoft SQL Server - Episode 13: Insert new records in a Table

Goran Kortjie — Thu, 23 Dec 2021 17:01:49 +0000

In this episode we will be discussing a key concept of SQL and database administration, we will be inserting a new record into a table.

We will be using the AdventureWorks database as usual, here we will be inserting a record into the HumanResourcesDepartment table.

We first need to see which columns are available in this table. Inside the HumanResourcesDepartment table there are four columns, namely the DepartmentID, Name, GroupName and ModifiedDate columns respectively.

These are the columns we will be inserting records, we will be performing these tasks using a SQL statement.

In most cases, the primary key (PK) column does not require an explicit value to be entered. This value is automatically generated from the system.

To determine whether we need to add a value to a specific column or not, follow these steps in SMSS.

Right-click on the table-name and click on design. When we click on design we will see all the column names, their data types and whether they allow NULL values or not.

Below we will see a Column Properties window. When we click on our different columns above, we see the column properties below change.

We click on the DepartmentID column; next in the Column Properties window scroll down until we see Identity Specification. We expand this by clicking on the arrow on the left-side.

These values indicate that this column is in fact an identity column. Identity Increment means the identity column increases by 1 every-time a new value is entered and the Identity Seed means the starting value was 1.

In short, every-time we add a new record; we will automatically generate a new value, which will be one higher than the previous highest value.

We could check the other columns to determine if they are Identity columns, however they are not number values and additionally they do not increment, therefore it is safe to assume that DepartmentID column is the only column that is identity in this table.

However we should double-check them to make absolutely certain.

Insert a new record

The structure of an insert statement is a bit different from what we have seen thus far.

insert into [Table-Name] (column-name/s)
values (values to insert into sql)

For example, to insert a new record in the HumanResourcesDepartment table we can write it as follows.

insert into [HumanResources].[Department] (Name, GroupName, ModifiedDate)
values ('Learning', 'Growth and Education', getdate())

Notice how the values in parenthesis, (column-names/s) and (values to insert into sql) need to correspond.

This means Name corresponds to 'Learning'; GroupName corresponds to 'Growth and Education' and ModifiedDate corresponds to getdate().

Getdate is a special built-in function provided by SMSS and allows us to get the current date.

Let’s see this in action within SMSS.

Once we execute this SQL query we get the message 1 row affected followed by the time that the query executed.

We can perform a SELECT statement to confirm our record has been inserted into the HumanResourcesDepartment table.

There you have it, this is how we insert a new record into a table.

Learn continually - there's always "one more thing" to learn.
-Steve Jobs

Learn SQL: Microsoft SQL Server - Episode 12: Finding the length of a String

Goran Kortjie — Sat, 18 Dec 2021 07:35:20 +0000

Many times we have to look at how many characters there are in a string. Sometimes we even need to find the position of a character or a set of characters in a particular string in a column.

In this episode, we will look at how we can accomplish this very exercise.

In SMSS, for this example we will be using the Person.EmailAddress table. We will start by writing a SQL statement to SELECT everything from this table.

We do this to get familiar with the columns and what data exist inside of them.

We will be using the EmailAddress column in our SQL statement. The first thing we want to do is count the number of characters in the EmailAddress column for each row. To accomplish this we need to modify our SQL statement.

LEN

We will first be replacing the * with EmailAddress, this is to see which email address we are working with. We calculate the length or the number of characters by using the LEN function. LEN measures the length of a column.

Lets see this in practice in SMSS.

This sort of function comes in handy, when we need to determine if there are any empty columns, or to extract a certain number of characters from a column.

CHARINDEX

Next we are going to use a another function called CharIndex. The CharIndex function allows you to find the position/index of a given character or a set of characters.

The CharIndex function takes two parameters or arguments, the first argument is the character or set of characters you hope to find; this is written inbetween quotation marks, since it will most likely be a string. The second argument is the column-name where you want to look for this value.

CharIndex(firstArg, secondArg)

Remember, to enclose the value of the first argument in single quotation marks.

To make it easier to identify we give the CharIndex query an Alias. In this example we will call it @ Position.

There you have it, two new functions you can use to find the LENgth of a set of characters in a column and CharIndex to find the position or index of a particular character or set of characters in a particular column.

😯 Now you have more tools added to your toolbelt 😯, don't forget to play around with them and think of some scenario's where they might be useful.

I hope you enjoyed this episode and get excited about learning SQL.

Learn SQL: Microsoft SQL Server - Episode 11: Extracting data from Strings

Goran Kortjie — Fri, 12 Nov 2021 06:33:10 +0000

In this episode we are going to discuss how to extract characters from a string value.

Let's say for instance, we wanted to pull data from the Person.Person table. As we should know by now, this table contains all the information about all people in the database, customers and employees.

In this example, we will be selecting only the firstName and the lastName columns from the Person.Person table. However, we do not want to pull the entire lastName of person in the database, instead we just want to pull the first character from the lastName value.

LEFT

SSMS provides a built-in function called Left and it takes two arguments/parameters.

First Value

We need to provide it with the string value we want to extract the character from, in this case lastName.

Second Value

The number of characters that we want to return starting from the Left, in this case it is 1.

Left(lastName, 1)

We can test this in SSMS, and to make it easier to understand we can give the Left function an Alias of Last Initial.

We can change the number of characters that get returned by modifying the second argument.

Another way to extract those characters is by using a Substring.

SubString is another built-in function that takes three arguments/values.

SUBSTRING

First Value

The column that we want to use to extract the value from.

Second Value

Here we need to provide the position we want to start extracting the value at, eg choosing 2 would mean we want to start extracting from the second character, starting from the Left.

Third Value

The number of characters we want to extract.

SUBSTRING(lastName, 1, 1)

As we can see, it works similar to LEFT, we could also assign an Alias to the SUBSTRING function to make things easier to understand.

RIGHT

Similar to LEFT their is another function called RIGHT.

Right works like Left in that it takes two arguments.

First Value

The string value we want to use to extract characters from.

Second Value

The number of characters we want to extract starting from the Right.

RIGHT(lastname, 3)

This will return the last three characters from the lastName.

This is how we extract values from a string column or just any string value.

Extracting values has really made me hungry...I hope you enjoyed this episode and put it into practice. Signing off...

Learn SQL: Microsoft SQL Server - Episode 10: Joining Multiple Columns and Strings

Goran Kortjie — Thu, 11 Nov 2021 06:02:59 +0000

Sometimes we need to join the data from multiple columns or string values to get the desired result.

Without wasting anytime lets dive into SSMS and see it in practice.

We will be using the Person.Person table. This time however we will be joining the firstName and lastName columns to make a new Full Name column.

To accomplish this we first need to write a SELECT statement to select the firstName and lastName.

Here in our result set we have the firstName and lastName of all the people in our Person.Person table. If we need to join the firstName and lastName to form a Full Name we can do this via two different syntaxes.

Using the Plus sign +

Quite literally we add a plus sign between the firstName and lastName.

firstName + lastName

Let's see what result we get in SSMS.

Did you see? When we added the plus sign it joined the two string values, however it did not add a space in-between. Therefore we needed to add an empty string ' ' by ourselves between the firstName and lastName.

firstName + ' ' + lastName

Finally we added an Alias to make things more simple to understand.

You have to use the plus sign syntax if you are using older versions of SSMS. For newer versions after SSMS 2008, use the Concat syntax. The plus syntax works in all versions thus far.

Using the Concat function

We can also use what is called the Concat function, similar to other functions, we wrap our arguments inside the function. In this case we wrap the firstName, the empty string, and the lastName inside our Concat function.

Concat(firstName, ' ', lastName)

We can add a many arguments or columns/strings inside the Concat function as we please. However notice that all of them have to be separated by a comma.

When we run these two syntaxes side by side, we can see they produce the exact same result. Now we have two ways of combining data from columns and string values.

Pretty cool stuff! I really enjoyed this one.

Learn SQL: Microsoft SQL Server - Episode 9: Aggregation in Where Clause

Goran Kortjie — Wed, 10 Nov 2021 18:48:00 +0000

Many times we might need to use aggregated values in our Where Clause, as a way of limiting the result set of aggregated values. You would think it would be as easy as adding a Where Clause in our query statement, however we will see in this episode, this is not the case.

By using the previous episode as an example, you can check that out on the link below to get up to speed.

It's a quick read so once you're done head on back here!

Learn SQL: Microsoft SQL Server - Episode 8: NULL values in Aggregation

Goran Kortjie ・ Nov 6 '21 ・ 3 min read

#sql #database #beginners #challenge

We might want to select all those customers whose order count is less than two times or more than two, three, four, five etc...

The Count is in this example the aggregated value. However a Where Clause does not take in an aggregated value. In this episode we are going to discuss how to use aggregated values to select our records.

Lets move into SMSS and see this in practice.

In this example we will be using the Sales.SalesOrderHeader table. As we know this table stores the information for every order the customer has placed. There is essentially one record for each sales order, then there are CustomerID's that stores information about the customer who placed this order.

What we are trying to do here is to find the CustomerID Where the sales count is more than X / less than X

First we will start by using a SELECT statement and select the CustomerID and the SalesOrderID.

What we need now is the Count of Sales Orders. Therefore we wrap the salesOrderId in the Count function. Notice we are grouping by the Customer level, meaning for each customer give us the count of their salesOrderId's.

Finally we need to tell SQL where to group it by and in this case we group it by the CustomerID.

To make it easier to understand we could add an Alias to the Count function that wraps around salesOrderId.

In order to only select the customers whose sales order Count is less than two, in this scenario, normally we would use the Where clause. However if we try this we will see SSMS throws an error.

Instead of Where, we replace it with Having. Also notice we cannot use an Alias when we use Having, we must use the complete Count function query string containing the salesOrderId.

Voila!

Now we can see all the customers whose sales order Count is less than two. We could even change the operator to less than equal to two, which in this case will return customers whose sale orders are two and less than two in the result set.

Awesome right!🥳

Keep in mind, for any of the aggregated functions, such as MIN, MAX, AVG or SUM, you will have to use HAVING if you want to restrict your result set, by those aggregated values.

I hope you enjoyed this episode, happy learning...

Learn SQL: Microsoft SQL Server - Episode 8: NULL values in Aggregation

Goran Kortjie — Sat, 06 Nov 2021 13:40:19 +0000

In this episode we will be discussing the impact of NULL values in Aggregation. Null value means that a column does not contain any value. It is not an empty string or 0, it is the absence of any value.

NULL

Null* values are excluded from calculations. Whenever we does aggregations such as SUM, the NULL values are not participants in these calculations.

This is not an issue when calculations are being performed on MIN, MAX and SUM, because when NULL is ignored is does not alter the calculation in anyway since it is the equivalent of 0 in these specific calculations.

This is an issue when calculations are being performed on Count and AVG, this is the reason for the episode; to demonstrate the impact NULL values have in aggregations such as Count and AVG.

Let’s see this in practice in SSMS.

I have created a simple table to better explain the impact of NULL values, since the size of most tables in AdventureWorks are large which in turn makes it difficult to immediately see the impact they have.

If we check to see the count of the OrderSales table, we will see it picks up all 4 of the values. But if we check to see the count of only the order value column, then we are receive only 3 in total. This means that the Count function only factored in NON-NULL values.

MIN will calculate the minimum value.

MAX will calculate the maximum value.

SUM will calculate the sum of all values.

However, AVG will not work as presumed, instead of dividing by four rows it will not accept NULL as valid and therefore not include it in its calculation. Unfortunately because of this we end up with invalid data.

Average is suppose to take the SUM of all the values which is 60 and divide them by 4, which is the total amount of rows. We should receive 15.

However, we receive back 20. Since the row with a NULL value was not included in the calculation, therefore we only divided the total of 60 by 3.

Keep this in mind when you are querying your database, especially when working with AVG, since this could lead to inaccurate data.

This can be especially troublesome in a large dataset such as AdventureWorks, this behaviour is not immediately apparent and can be easily missed or forgotten.

I hope this episode was helpful, I will try to keep this little bit of knowledge at the back of my mind whenever I am querying databases and I hope you do to. Till next time...

Learn SQL: Microsoft SQL Server - Episode 7: Aggregate data with SUM and AVG

Goran Kortjie — Thu, 04 Nov 2021 20:54:11 +0000

In this episode we are going to explore two additional functions we can utilise to aggregate our data, namely SUM and AVERAGE (AVG).

As the name suggests SUM allows us to calculate the sum of the value in a given column. AVERAGE allows us to calculate the average value of all the values in a specified column.

Let's get practical and see how this works in SSMS.

Calculate SUM

First off, let's open a new query window in the AdventureWorks database. We will be using the Sales.SalesOrderHeader table. we will be using SUM to calculate the sum of the Subtotal column, which is the total order values.

Here we have the SUM of all the orders in the Sales.SalesOrderHeader table.

If for instance we need to calculate the SUM of the totals by the CustomerID or SalesPersonID, then we just need to specify this in our query statement. In our example we will use the CustomerID.

We use the Group By function to tell SQL by which column we want to calculate the total by, let’s provide an Alias for the SUM function and call it Total Order Value.

Voila! we now see for each customer their total order value.

Calculate AVG

Next we will look at the AVERAGE function, which works similar to SUM. There we will be copying the SUM query statement and pasting it below itself.

The only changes we will make is instead of SUM we replace it with AVG, this is the Average. We will also change the Alias to Average Order Value.

Awesome, we can read the Average order value for each customer. We can even combine these two statements into one.

Here we have the CustomerID, the Total Order Value of each customer as well as their Average Order Value. We can confirm these numbers by supplying a Count function in our query statement.

Let’s see an example of this in SSMS.

As we can see the numbers add up correctly!

Thats all she wrote, this is how we can calculate the SUM and Average. Remember to keep practicing and to eat your vegetables. Signing off for now, see you next time...

Learn SQL: Microsoft SQL Server - Episode 6: Alias and Aggregate Data

Goran Kortjie — Sat, 30 Oct 2021 21:57:27 +0000

In this episode we will be discussing how to use Aliases in SQL queries and how to aggregate our data.

Why Use Alias

Alias	Alias
less code	readability

When we use Aliases our SQL becomes more readable and easy to understand, plus we will also have better column names.

We can see what Aliases are in SSMS.

In this instance, we want to pull all the data with a BusinessEntityID greater than 2000, we want to Order BY, BusinessEntityID in ascending order and we want to see the BusinessEntityID in our result set, meaning we should include it in our SELECT query.

Notice how cumbersome it is, we need to specify the table we are referring to in each query statement. This is where aliases can help. Also if we want to give more meaningful names to any of the columns then aliases can also be useful.

We can give aliases to our columns and tables. We will start by giving aliases to all the tables we have used so far. For example instead of referring to Person.Person we can create an alias for it.

We use the AS keyword to create an alias, we can name it whatever we want but in most cases we would simplify it to make it easier to read and understand.

Notice how when we created an alias for our tables, SSMS no longer accepted the original table name and it threw an error in the form of the red underline. We had to change them to the alias name we gave our tables.

We can also create an alias for our columns in the same way we created an alias for our tables.

Notice in our result set, when we gave the firstName the alias of Fname it showed up in the result set. We can give our columns more meaningful names. Lets say for instance, we want to give our firstName column the alias of first name.

To do this we need to enclose the alias name in brackets or we need to enclose it inside double quotes, since it has a space in-between and SQL does not like that.

Aggregate Data

We have several functions available that we can use to aggregate data from our database.

COUNT

Count allows us to count the number of records that will be returned based on our query statement.

We can even use this function with columns to determine how many non NULL values exist in the database. Meaning that column we query should have a value.

MIN

Min allows us to return the minimum value for the specified column for that table.

Min can be used in any type of column, but most of the time it is used for numbers and dates.

To demonstrate Min we will use the Sales.SalesOrderHeader table and pull data from the subTotal column.

Max

Max allows us to return the maximum value for the specified column for that table.

Max can be used in any type of column, but most of the time it is used for numbers and dates.

We can use the Max function in the same way as the Min function, We can also use a Where clause to limit the return data by a particular date, ID or customer or anything we want to use.

Group BY

What if we had to look at Count of records for a particular column, lets say we want to see how many orders have each customer placed, or the Min or Max value of orders placed by each customer.

Group By can help us to accomplish this task.

We start off with a SELECT statement and use the COUNT() function on the **Sales.SalesOrderHeader*. This will tell us the amount of orders in this table.

We now want to see the order count by each customer, naturally we would add the CustomerID either before or after the Count(*) function in the Select statement. If we hit execute we get an error, the reason is we need to specify at what level are we counting the records at.

Since we have CustomerID in our statement, we are trying to do the count at the CustomerID level. However we need to explicitly tell SSMS this. We do this using Group BY.

Aggregated columns will show no column names in all our SQL statements. This is where an Alias is useful, by allowing us to give our aggregated data meaningful names.

We can even sort this list. Let's order the list by the alias in ascending and then in descending order.

We can now see which customers have how many orders, we can do the same query with MIN and MAX.

We will start with MIN function to calculate the minimum SubTotal.

We can use the MAX function in the same statement. We use the Order By function to decide how we want to sort the result set, either by Min Order Value or Max Order Value.

We can even chain this further by adding other aggregated values such as Count.

Thus far we have looked at aggregating by one column, However in any real case scenarios, we might need one or more columns to aggregate the data at. Let's say we want to find out the total order count by each sales person in each territory.

This means we are aggregating at the sales person level and at the territory level. To do this we need to create a new query.

We will still be working in the Sales.SalesOrderHeader table, if we have a peek at the columns within we will notice the SalesPersonID and TerritoryID columns. We will be using these columns in our query statement.

We can also order the result set of this query statement by the SalesPersonID. From the result set we can see the breakdown of each sales persons sales in each territory.

How cool was that, we were able to do some awesome things with just the basics of SQL to pull data from our database that is insightful and easy to understand.

I really hope you enjoyed it, stay zen folks!

Learn SQL: Microsoft SQL Server - Episode 5: Joining Tables

Goran Kortjie — Fri, 29 Oct 2021 03:46:37 +0000

Joining Multiple Tables

Thus far we have discussed how to pull data from a single table. However in most cases, we will need to pull the data from multiple tables in order to get the bigger picture for our analysis or reporting.

Before we can join tables in a SQL query, we need to be familiar with our database structure, which tables there are, what kind of columns are there, and how the tables connect to each other.

In the AdventureWorks, the Person schema contains a-lot of information about a person, the people who are in the database, the person table contains columns that hold different pieces of information of a person.

It holds the Primary Key (PK), which is BusinessEntityID. This means that BusinessEntityID is a unique record that identifies each record.

BusinessEntityID is also the Foreign Key (FK) for this table. This means it is used to connect to other related tables.

There is also a table called Person.EmailAddress, which contains information on each persons email address.

The table has a the Primary Key of BusinessEntityID and each email address has a Primary Key of EmailAddressID, the EmailAddressID allows for a person to have multiple email addresses.

In order for us to pull the firstName, lastName and emailAddress for a person, we need to join these two tables. A way to join them is to take a key that is common between both of them, which in this case is BusinessEntityID.

The syntax for joining tables is as follows:

Select [Column Names | * ]
From [Table Name]
On [Table.primaryKey] = [Table.primaryKey]

We can limit the amount of columns return to us by specifying the columns we want to pull in the select statement.

Notice how when we try to pull the data from BusinessEntityID, SQL throws an error, it will also indicate an error by underlining with a red line. The reason why is we an not being specific from which table we want to pull the BusinessEntityID, since it exist in both tables.

When we specify we want to pull from the person.BusinessEntityID is works perfectly.

Using this same syntax we can join multiple tables. Lets say for instance we need to join another table and it is the Person.PersonPhone table.

We can even limit this further by specifying a Where clause, lets say for instance we need to limit this query to where the firstName is Kim, we can accomplish this as follows:

We are now able to find each person with the first name as Kim, we can see their last name, email address and phone number by joining multiple tables, that is pretty amazing!

We have come to the end of this discussion about joining tables, I really hope you enjoyed reading it, join me in the next episode...