An Overview of SQL Server Joins

In this article, we are going to learn about SQL Joins. Also, we will learn the SQL IntelliSence feature and how it is useful. Moreover, I will explain how Devart SQL Complete works and how it is useful to developers who write SQL queries.

The SQL Joins populate the data from different tables by joining them using the common column. There are four types of joins.

1. INNER JOIN
2. LEFT JOIN or LEFT OUTER JOIN
3. RIGHT JOIN or RIGHT OUTER JOIN
4. FULL JOIN

For demonstration, I created a database named demodatabase with three tables. The script to create a table is the following:

USE [demodatabase]
GO
CREATE TABLE [dbo].[tblStudent](
   	[student_code] [varchar](10) NULL,
   	[student_name] [varchar](500) NULL,
   	[ADDRESS] [varchar](5000) NULL,
   	[School_ID] INT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSchool](
   	[School_Id] [int] IDENTITY(1,1) NOT NULL,
   	[Name] [varchar](50) NULL,
   	[Category_ID] int
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblSchoolCategory](
   	[Category_Id] [int] IDENTITY(1,1) NOT NULL,
   	[Category_Name] [varchar](50) NULL
) ON [PRIMARY]
GO

Script to insert data is the following: 

INSERT INTO [tblStudent] (student_code, student_name, ADDRESS, School_ID) VALUES
('STUD0001', '  	Kayla Woodcock', '  K201 PRAMUKH GLORY SARGASAN', 1),
('STUD0002', '  	Hudson Onslow', '   K205 PRAMUKH GLORY SARGASAN', 1),
('STUD0003', '  	Isabella Rupp', '   G201 PRAMUKH LOTUS SARGASAN', 2),
('STUD0004', '  	Eva Muirden', ' 	A205 PRAMUKH NAGAR SARGASAN', 4),
('STUD0005', '  	Sophia Hinton', '   A205 PRAMUKH NAGAR SARGASAN', 2),
('STUD0006', '  	Amy Trefl', '   	A205 PRAMUKH NAGAR SARGASAN', 1)
GO
INSERT INTO tblSchool(Name,Category_ID) VALUES
('NALANDA SCHOOL',1),
('BN HIGH SCHOOL',1),
('Sarvajanik School',2),
('LP Sawani School',NULL),
('Karvy School',NULL),
('S V Shah Vidhya vihar',NULL)
INSERT INTO tblSchoolCategory(Category_Name) VALUES
('A'),
('B'),
('C')

Now, let us understand how SQL Join works.

Inner Join

The inner joins are used when you combine the records of two tables and populate only matching data from both tables. The syntax of the INNER JOIN is the following:

SELECT <column_1>,<column_2>… FROM <table_name_1> INNER JOIN <table_name_2> ON <table_name_1>.<column_1>=<table_name_2>.<column_2>

For example, we want to show the list of students and the name of the school they are studying. The query is below: 

SELECT s.student_code,s.student_name,s.ADDRESS,T.Name FROM tblSchool t
Inner JOIN  tblStudent s ON s.School_ID=T.School_Id

Query Output:

Left Join or Left Outer Join

The left joins are used when you want to combine the records of two tables, populate matching records from both tables, and all records from the left table. If a right table does not have matching records, the query will return NULL. The syntax of the LEFT JOIN is following:

SELECT <column_1>,<column_2>… FROM <table_name_1> LEFT JOIN <table_name_2> ON <table_name_1>.<column_1>=<table_name_2>.<column_2>

For example, we want to generate a list of schools with the school name and the total number of students studying there. The query to display the total number of students and the school name is the following: 

SELECT T.Name SchoolName ,COUNT(s.student_code)TotalStudent FROM tblSchool t LEFT JOIN  tblStudent s ON s.School_ID=T.School_Id GROUP BY t.Name

Query Output:

As you can see, the query returned all schools with the student count. We have used left join with the aggregate function to show the schools with zero students.

Right Joins or Right Outer Join

The inner joins are used when you want to combine the records of two tables, populate all data from the right table, and match both tables' data. If a left table does not have matching records, then the query will return NULL. The syntax of the RIGHT JOIN is the following:

SELECT <column_1>,<column_2>… FROM <table_name_1> RIGHT JOIN <table_name_2> ON <table_name_1>.<column_1>=<table_name_2>.<column_2>

For example, we want to generate a list of categories and a count of schools that belong to the category. 

SELECT sc.Category_Name,COUNT(s.Name) FROM tblSchoolCategory sc RIGHT JOIN tblSchool s ON s.Category_ID=sc.Category_Id GROUP BY sc.Category_Name

Query Output:

As you can see, the query returned all categories with a count of schools belonging to the category.

FULL JOIN

The FULL JOINs are the combination of LEFT and RIGHT JOINs. The FULL JOIN generates the results that contain all records from both tables. The syntax of FULL JOIN is following:

SELECT <column_1>,<column_2>… FROM <table_name_1> , <table_name_2>

For example, we want to populate the records from the tblSchool and tblSchoolCategory tables with matching and non-matching records. 

SELECT * FROM tblSchoolCategory sc , tblSchool s

Query Output:

Now, let us see how SQL Server IntelliSense helps to improve the coding speed by reducing the typing efforts.

SQL IntelliSense

SQL Intellisense is a feature introduced in SQL Server 2008. It automatically gathers the list of objects and metadata of the SQL Server instance. SQL IntelliSense provides the following benefits:

• It shows the list of database objects whose name starts with a typed keyword.
• It provides quick info on the object written in a T-SQL code. When you hover the cursor on the object's name, the SQL Server IntelliSense shows its information.
• It shows the list of input parameters for specified system and user-stored procedures and functions.
• It has another option named INSERT SNIPPET, which is a script template. Suppose you are writing a script to create an index on a table. You can use the INSERT SNIPPET option to generate a create index template easily. You can enter the required details.

To enable or disable the SQL IntelliSense, Select Tools --> Options -->Expand Text Editor --> Expand Transact-SQL --> IntelliSense --> Select Enable IntelliSense. 

You can refer to SQL Server IntelliSense and Autocomplete articles to learn more about SQL Server IntelliSense. Now, let us see how a Devart SQL Complete can be a good alternative to SQL IntelliSense.

An Overview of Devart SQL Complete

The Devart SQL Complete is a SQL plugin like SQL Server IntelliSense, but it has many other features that improve the productivity of SQL developers or DBA. In this article, I will show how Devart auto-complete helps to write a SQL query with OUTER Join. For example, we want to populate the list of all schools and all students studying in them. I have split the entire query writing process into multiple images to understand the SQL Complete's functionality.

Image 1: List of the SQL commands populated by Devart SQL Complete IntelliSense. 

Image 2: Devart SQL Complete automatically populates the list of the SQL databases by typing the USE statement. As you can see in the below image, the list of databases created in the SQL instance named Nisag-PC is shown. 

Image 3: Devart SQL Complete automatically populates the list of the tables by typing the SELECT statement. The screenshot below shows that Devart SQL Complete has provided the list of tables within the DemoDatabase database. You can select multiple tables from the list and add them in a query editor by pressing the TAB key.

Image 4: The Devart SQL Сomplete populates the list of tables, list of columns, and their datatype of the selected column and total rows within a selected table by typing SELECT * FROM. As you can see in the image below, I have selected the tblStudent table, and Devart SQL complete shows a list of columns, their datatype, and estimated row counts of the tblStudent table.

Image 5: When we use SQL Joins in T-SQL query, the Devart SQL complete shows the list of matching columns after the INNER JOIN / LEFT JOIN / RIGHT JOIN keyword. As you can see in the image below, when I add the LEFT JOIN keyword and specify the name of the second table, the Devart SQL Complete populated the list of matching columns from the tblStudent and tblSchool tables.

The said feature increases the coding speed because the developer does not need to refer to the columns of the second table.

Image 6: When we use SQL Joins in a T-SQL query and add a WHERE clause, the Devart SQL Complete populates the list of columns of both tables, which helps to select the appropriate column.

As you can see below screenshot, when I added a WHERE clause, the Devart SQL Complete populates the list of columns of the tblStudent and tblSchool table. The said feature increases the coding speed because the developer does not need to refer to the columns of the second table.

In our case, we are populating records with NULL values, so I am not using the WHERE clause in the query. Below is the final query:

USE demodatabase
go
SELECT * FROM tblSchool  s LEFT JOIN  tblStudent s1 ON s.School_Id=s1.School_ID

Query Output:

The above example shows that query writing becomes easier with Devart SQL Complete.

Summary

This article explains about T-SQL Joins and their types with a simple example. The article also explains SQL IntelliSense and Devart SQL Complete.