DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • MongoDB to Couchbase: An Introduction to Developers and Experts
  • How to Decide Between JOIN and JOIN FETCH
  • Optimize Slow Data Queries With Doris JOIN Strategies
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Trending

  • Memory Leak Due to Time-Taking finalize() Method
  • Docker Base Images Demystified: A Practical Guide
  • A Simple, Convenience Package for the Azure Cosmos DB Go SDK
  • Designing a Java Connector for Software Integrations
  1. DZone
  2. Data Engineering
  3. Databases
  4. An Overview of SQL Server Joins

An Overview of SQL Server Joins

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

By 
Nisarg Upadhyay user avatar
Nisarg Upadhyay
·
Dec. 14, 22 · Tutorial
Likes (6)
Comment
Save
Tweet
Share
3.7K Views

Join the DZone community and get the full member experience.

Join For Free

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:

MS SQL
 
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: 

MS SQL
 
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:

MS SQL
 
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: 

MS SQL
 
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:

Inner Join example


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:

MS SQL
 
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: 

MS SQL
 
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:

Left Join example


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:

MS SQL
 
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. 

MS SQL
 
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:

Right Join example

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:

MS SQL
 
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. 

MS SQL
 
SELECT * FROM tblSchoolCategory sc , tblSchool s


Query Output:

Cross Join example

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. 

Enable SQL Server 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.

List of tables in DemoDatabase


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.

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.

The said feature increases the coding speed because the developer does not need to refer 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:

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


Query Output:

shows that query writing becomes easier with Devart SQL Complete.

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. 

Database Data (computing) Joins (concurrency library) sql

Opinions expressed by DZone contributors are their own.

Related

  • MongoDB to Couchbase: An Introduction to Developers and Experts
  • How to Decide Between JOIN and JOIN FETCH
  • Optimize Slow Data Queries With Doris JOIN Strategies
  • Data Privacy and Security: A Developer's Guide to Handling Sensitive Data With DuckDB

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!