Ben Richardson
Output of Full Join Query

SQL JOIN TABLES: Working with Queries in SQL Server

November 6, 2019 by

In this article, you will see how to use different types of SQL JOIN tables queries to select data from two or more related tables.

In a relational database, multiple tables are connected to each other via foreign key constraints. If you want to retrieve data from related tables simultaneously, SQL JOIN tables queries will be useful.

SQL JOIN tables queries can be divided into four main types:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

Before we see the SQL JOIN tables queries in action, let’s create a dummy database.

Creating a dummy database

In this section, we will create a simple database for an imaginary book store. The name of the database will be the BookStore. The following script creates our database:

The database will contain three tables: Books, Categories, and Authors. The following script creates the Books table:

The Books table contains four columns: Id, Name, Price CategoryId, and AuthorId.

Similarly, execute the following script to create the Categories and Authors tables:

Let’s now insert a few dummy records in the Categories table:

Similarly, run the following script to insert records in the Authors table:

Finally, to add dummy records in the Books table, run the following script:

Note: Since the Id column of the Books has Identity property, we do not need to specify the value for the Id column.

Let’s now see each of the SQL JOIN tables queries in detail.

SQL JOIN tables query type 1 – INNER JOIN

The INNER JOIN query retrieves records from only those rows of both the tables in the JOIN query, where there is a match found between the values for the columns on which the INNER JOIN is being applied.

This may sound complex, but don’t worry, in practice, it is pretty straight forward.

Let’s retrieve the values for the CategoryId and Name columns from the Books table, and the Id and Name columns from the Categories table, where the values for the CateogryId column of the Books table and the Id column of the Categories table are the same:

Here is the result set:

Output of using Inner Join query.

You can see that from the Books table, the records for the books with CategoryId of 5 are not being displayed since the Id column of the Categories table doesn’t contain 5. Similarly, from the Categories tables, categories with the Id value of 8, 10, 12, and 16 are not being displayed since the CateogryId column from the Books table doesn’t contain these values.

Hence, for the INNER JOIN, the only rows that are selected from both tables are those where the values for the columns involved in the INNER JOIN query are the same.

SQL JOIN tables query type 2 – LEFT JOIN

In LEFT JOIN, all the records from the table on the left of the LEFT JOIN clause are retrieved.

By contrast, from the table on the right of the LEFT JOIN clause, only those rows are selected where the values for the columns involved in the LEFT JOIN query are the same.

For example, if you apply a LEFT JOIN on the CategoryId column of the Books table and the Id column of the Categories table. All the records from the Books table (the table on the left) will be retrieved, whereas from the Categories table (the table on the right) only those records will be retrieved where the CategoryId column of the Books table and the Id column of the Categories table, have the same values.

Look at the following script:

Here is the result set:

Output of Left Join Query

From the result, you can see that all the records from the books table are displayed. For the id and Name columns of the Categories table, only the matching records are displayed. Since the Categories table doesn’t contain 5 in the Id column, NULL values have been added in the columns for the Categories table.

SQL JOIN tables query type 3 – RIGHT JOIN

The RIGHT JOIN clause is the exact opposite of the LEFT JOIN clause.

In the RIGHT JOIN, all the records from the table to the right of the RIGHT JOIN clause are retrieved. Conversely, from the table to the left of the RIGHT JOIN clause, only those rows are selected where the values for the columns involved in the RIGHT JOIN query are the same.

For example, if you apply a RIGHT JOIN on the CategoryId column of the Books table and the Id column of the Categories table. All the records from the Categories table (the table on the right) will be retrieved, whereas from the Books table (the table on the left) only those records will be retrieved where the CategoryId column of the Books table and the Id column of the Categories table, have the same values.

Look at the following script:

Here is the result set:

Output of Right Join Query

From the output, you can see that all the records from the Categories table columns are displayed. For the CategoryId and Name columns of the Books table, only the matching records are displayed. Since the Books table doesn’t contain any record with values 6, 8, 10, or 12, in the CategoryId column, NULL values have been added in the columns from the Books table.

SQL JOIN tables query 4 – FULL JOIN

The FULL JOIN clause retrieves all the records from both the tables irrespective of the match between the values in the columns used in the FULL JOIN clause.

The following script applies a FULL join on the CategoryId column of the Books table and the Id column of the Categories table:

Here is the result set:

Output of Full Join Query

The output shows that all the records have been retrieved from the Books and Categories tables. NULL values have been added for the rows where a match is not found between the CategoryId column of the Books table and the Id column of the Categories table.

Conclusion

The SQL JOIN tables queries are used to retrieve related data from multiple tables. In this article, you saw how to implement different types of SQL JOIN tables queries in Microsoft SQL Server, with the help of different examples.

Ben Richardson

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson
Ben Richardson
184 Views