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:
|
1 |
CREATE DATABASE BookStore |
The database will contain three tables: Books, Categories, and Authors. The following script creates the Books table:
|
1 2 3 4 5 6 7 8 9 |
USE BookStore CREATE TABLE Books ( Id INT PRIMARY KEY IDENTITY(1,1), Name VARCHAR (50) NOT NULL, Price INT, CategoryId INT, AuthorId INT ) |
The Books table contains four columns: Id, Name, Price CategoryId, and AuthorId.
Similarly, execute the following script to create the Categories and Authors tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
USE BookStore CREATE TABLE Categories ( Id INT PRIMARY KEY, Name VARCHAR (50) NOT NULL, ) USE BookStore CREATE TABLE Authors ( Id INT PRIMARY KEY, Name VARCHAR (50) NOT NULL, ) |
Let’s now insert a few dummy records in the Categories table:
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO Categories VALUES (1, 'Cat-A'), (2, 'Cat-B'), (3, 'Cat-C'), (7, 'Cat-D'), (8, 'Cat-E'), (4, 'Cat-F'), (10,'Cat-G'), (12,'Cat-H'), (6, 'Cat-I') |
Similarly, run the following script to insert records in the Authors table:
|
1 2 3 4 5 6 |
INSERT INTO Authors VALUES (1, 'Author-A'), (2, 'Author-B'), (3, 'Author-C'), (10, 'Author-D'), (12, 'Author-E') |
Finally, to add dummy records in the Books table, run the following script:
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO Books VALUES ( 'Book-A', 100, 1, 2), ( 'Book-B', 200, 2, 2), ( 'Book-C', 150, 3, 2), ( 'Book-D', 100, 3,1), ( 'Book-E', 200, 3,1), ( 'Book-F', 150, 4,1), ( 'Book-G', 100, 5,5), ( 'Book-H', 200, 5,6), ('Book-I', 150, 7,8) |
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:
|
1 2 3 4 |
SELECT Books.CategoryId, Books.Name, Categories.Id, Categories.Name FROM Books INNER JOIN Categories ON Books.CategoryId = Categories.Id |
Here is the result set:
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:
|
1 2 3 4 |
SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name FROM Books LEFT JOIN Categories ON Books.CategoryId = Categories.Id |
Here is the result set:
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:
|
1 2 3 4 |
SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name FROM Books RIGHT JOIN Categories ON Books.CategoryId = Categories.Id |
Here is the result set:
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:
|
1 2 3 4 |
SELECT Books.CategoryId, Books.Name, Categories.id, Categories.Name FROM Books FULL JOIN Categories ON Books.CategoryId = Categories.Id |
Here is the result set:
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.
View all posts by Ben Richardson
- Working with the SQL MIN function in SQL Server - May 12, 2022
- SQL percentage calculation examples in SQL Server - January 19, 2022
- Working with Power BI report themes - February 25, 2021