Getting Started With Inner and Left SQL Joins

Joins are the basis for utilizing any normalized, relational database. However, they can be one of the trickier concepts to grasp for beginners. You won't get very far in SQL development without understanding joins. We'll break down the way normalized data is retrieved and connected using joins and also show you a handy trick to keep things simple.

Let's start with some sample data. Here is a simple relational database diagram that shows two tables. Most things in life have some sort of hierarchal relationship; parents have children, owners have cars and credit cards have transactions. Database relationships define these types of connections between tables.

In this example, the account can have many purchases. The line represents a link between the primary key and the foreign key. Primary keys identify rows in a table. Foreign keys are a reference that say, "This row is somehow related to this other row in another table."

Here is a picture of our select results for these two separate tables:

SELECT * FROM [Account]
SELECT * FROM [Transactions]

In this scenario, our primary keys are the AccountId in the Account table and the TransactionID in the transaction table. Each is a unique identifier that represents something. AccountId one identifies Sam Jone's account. TransactionID one is a transaction for $45.23. The foreign key in our scenario is the AccountId on the Transaction trable. It's a foreigner because it represents data that comes from somewhere else. Look at the highlighted green columns. They all have AccountID one in the Transaction table. This says, "These are transactions that belong to AccountId one."

Inner Joins

Now, let's say we're asked to create a report that shows Account Number, Account Name, Tran Amount and Transaction Date together. Selecting from just one of these tables can only bring back half of the results. This is where we need to do a join. SQL joins almost always happen on the primary and foreign keys. In this case, the two keys that link the tables together are the primary key called AccountId on the account table and the foreign key called AccountId on the Transaction table.

The code for a SQL join starts just after the FROM statement. The statement "INNER JOIN" says we're going to join up another table. We'll talk about what "INNER" means in a moment. We then supply the name of the table we want to join. After this, we add the "ON" clause which says what two keys are related.

The query returns the following:

There it is! The transactions have automatically been correlated to the accounts that they belong to. Here we see data from two separate tables joined together in a logical way. But what happened to Bill Sim's account? Since he has no transactions, his account is excluded from the results. An inner join tells SQL, "Only show me results that have a match on both tables. However, there are times when we want information about what is not in the database as well. This is where we'd use a left join.

The Powerful Left Join

In the example above, Bill Sim's account disappeared fro the search results since he didn't have a transaction. Let's say we wanted to have his account show up in the result set regardless of the fact that he has no accounts.

We just changed the "INNER" to a "LEFT." Now, our results look like this:

Now, we see that NULLs were supplied in the place of transaction columns since there are no transactions on Bill's account. The great thing is you can solve 99 percent of your joins with only INNER and LEFT SQL joins.

Using the Query Designer to Create Joins

Now that you understand how to use joins, you can utilize the Query Designer tool to create the SQL code visually. This is a handy shortcut that can save you a lot of typing. However, it is easier to make mistakes when creating queries visually. For this reason, it's important to grasp the concepts of joins before building them visually.

To open the Query Designer, open SQL Server Management Studio and go to the Query menu. Select Design Query in Editor and the Query Designer window will display. A list of tables should automatically be displayed. Double click any tables you wish to add and they will appear in the designer window. Close the Add Table window once you have the tables you need.

If your database is properly designed, many of the joins will be automatically defined. A well-built database already knows about how primary keys and foreign keys relate to one another. The line which appears between tables signifies that join code has been created between the two tables based on the graphically connected fields.

If the join does not appear automatically, you simply drag one key over to the other. The line will appear, signifying that the tables are now joined together. You can see the actual SQL that was written in the bottom half of the window. Always double check the SQL and make sure the join was created as expected.

Warning Signs

It's very easy to accidently create a join that is invalid. SQL Server won't stop you from doing a join that doesn't make any sense. Here are three signs that something went wrong with your joins:

No Rows Returned – If nothing comes back, you probably accidently create a join that has no matching criteria. Manually run a select on the involved tables and see if you can spot any primary keys that match foreign keys. If you see some, you'll need to revisit your join.

Too Many Rows Returned – If you have a table with 10 rows joined to a table with 15 rows, you shouldn't get back thousands of rows. If you do, you've probably joined something incorrectly. Think about the logical maximum possibility of matches that the two tables could have. Usually, the result is equal or lower than that number.

CROSS JOIN SQL Appears – If you are using the Query Designer and the generated SQL contains a CROSS JOIN clause, you've probably forgotten to join one of your tables to the others. Take a look back at the SQL and see what you've missed.

Now that you're fully equipped with knowledge of joins, you're ready to start analyzing data, creating reports and building meaningful, useful queries. Test out your knowledge on your own database today.