Grab Deal : Flat 30% off on live classes + 2 free self-paced courses! - SCHEDULE CALL

- SQL Server Blogs -

All About SQL Joins and Subqueries

People who will be reading this blog are mostly from IT backgrounds. It means somewhere at one point in time they have all developed some computer programs and stored data in a database. Data stored requires retrieval at some point in time. If you have followed the standard normalization procedure of a relational database management system then you would not be saving all the data in one table. So to generate a register say for example sales register you have to extract data from multiple tables to generate the final output.

Let us take the example of the Sales Register. Usually, in any application, sales data are stored in Sales Header, Sales Detail file. Along with them you need to extract data from Customer master and item master. Although you have primary key and foreign keys in each table to help you relate them together, but to extract data from them together you need a specific process or tool to do so. This process or tool is called joins.

Different types of joins that serve different types of data extraction purpose. Over the next few paragraphs, we are going to learn about these different types of joins.

Types of SQL Joins

There are four types of joins

  1. Inner Join
  2. Left Join
  3. Right Join
  4. Full Join

Joins-Comparative Study

Inner Join Left Join Right Join Full Join
Select all records from Table A and Table B, where the join condition is met. Select all records from Table A, along with records from Table B for which the join condition is met (if at all). Select all records from Table B, along with records from Table A for which the join condition is met (if at all). Select all records from Table A and Table B, regardless of whether the join condition is met or not.

For the examples of inner join, outer join and full join we will be using three tables, namely

1. Client
2. Employee
3. Project

The queries to create the three tables are as follows.

Client

CREATE TABLE [dbo].[Client](
	[Clientid] [varchar](50) NULL,
	[Clientname] [varchar](max) NULL,
	[Age] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Employee

CREATE TABLE [dbo].[Employee](
	[EmpId] [varchar](50) NULL,
	[Empname] [varchar](max) NULL,
	[Age] [int] NULL,
	[Email] [varchar](max) NULL,
	[Phone] [int] NULL,
	[Adress] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Project

CREATE TABLE [dbo].[Project](
	[Projectid] [varchar](50) NULL,
	[Empid] [varchar](50) NULL,
	[Clientid] [varchar](50) NULL,
	[Projectname] [varchar](50) NULL
) ON [PRIMARY]

Inner Join

SELECT Employee.EmpID, Employee.Empname,  Project.Projectid, Project.ProjectName
FROM Employee
INNER JOIN Project ON Employee.EmpID=Project.EmpID;

LEFT JOIN

SELECT Employee.Empname, Project.ProjectID, Project.ProjectName
FROM Employee
LEFT JOIN
Project
ON Employee.EmpID = Project.EmpID ;

RIGHT JOIN

SELECT Employee.Empname, Project.ProjectID, Project.ProjectName
FROM Employee
RIGHT JOIN
Project
ON Employee.EmpID = Project.EmpID ;

FULL JOIN

SELECT Employee.Empname,  Project.ProjectID
FROM Employee
FULL JOIN Project
ON Employee.EmpID = Project.EmpID;

CROSS JOIN

This is a type of join where each of Table A combines with each row of Table B.This is also called Cartesian product.

Let us take two tables Name and Department. After cross join it will look like below.

The query to create Name table

CREATE TABLE [dbo].[Name](
	[Name] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The query to create Department table
CREATE TABLE [dbo].[Department](
	[Department] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

There are two ways we implement cross join

Using CROSS JOIN Keyword Not using WHERE Clause after FROM Clause
select * from 
[dbo].[Name]
cross join
[dbo].[Department]
select * from 
[dbo].[Name]
,
[dbo].[Department]


Advantage of CROSS JOIN

CROSS JOIN gives you an overview of all the possible combinations of tables when you are combining two tables.

Read: How to Compare MongoDB and DynamoDB?

The disadvantage of CROSS JOIN

Usually slow when the volume of data in the tables are very high.

Self Joins

Self joins is a type of query when you join the same table multiple times. SQL Server normally does not allow the use of tables with the same name, multiple times in the same query. So you need to use Aliases.

Imagine a situation where you have a database table which contains details of employees, their designation and their reporting boss as well. You need to develop a hierarchy report on which employee reports to whom. This is one of the reasons where you can use self-joins.

Let us take the staff table example for this case study. The query to create the Staff tables is as follows.

CREATE TABLE [dbo].[staffs](
	[staff_id] [varchar](50) NULL,
	[first_name] [varchar](max) NULL,
	[last_name] [varchar](max) NULL,
	[phone] [varchar](max) NULL,
	[active] [int] NULL,
	[store_id] [int] NULL,
	[manager_id] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The data snapshot is like below.

The idea of self-join here will be to show the staff name along with their manager name. Here it goes.

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    staffs e
INNER JOIN staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

Here is the output

MERGE JOIN

MERGE join is one of the four join operators which join data from two tables into one single output.

For the Merge join case study we will be using two tables called Mergeexample1 and Mergeexample2. Following is the query to create the tables.

create table mergeexample1
 (field1 int, 
 field2 int, 
 name1 char(200))

create table mergeexample2 
(field1 int, 
field2 int, 
name1 char(200))

To update the data into the tables we will be using the following SQL Script.

set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
    insert mergeexample1 values (@i * 2, @i * 5, @i)
    insert mergeexample2  values (@i * 3, @i * 7, @i)
    set @i = @i + 1
  end

The output looks like this

Mergeexample1

Mergeexample2

The merge joins  SQL Statement will look like below.

select *
from mergeexample1 T1 join mergeexample2 T2  on T1.field1 = T2.field1
option (merge join)

The output looks like below.

UNION

Union operator combines two or more tables and gives one single output. The criteria for UNION are that the tables that are combined should have the same number of fields. The catch is union only returns unique values i.e. if a row with the same value exists in tables that are combined, then the resulting output will only show one row.

We will be using mergeexample1 and mergeexample2 for the union example. The SQL Statement looks like below.

Read: What is SQL Formatter? Features of SQL Formatter Online
select * from [dbo].[mergeexample1]
union
select * from [dbo].[mergeexample2]

The output looks like

UNION ALL

UNION ALL operator combines inputs from two or more tables. The only difference is that it returns all the rows from all the combined tables even if there are duplicate rows.

The SQL Statement looks like below

select * from [dbo].[mergeexample1]
union all
select * from [dbo].[mergeexample2]

The output looks like below

INTERSECT

The INTERSECT returns the common rows between the two tables that are involved in the query.

The SQL Statement looks like below

select * from [dbo].[mergeexample1]
Intersect
select * from [dbo].[mergeexample2]

The output looks like below

Advantage of JOIN

  1. Executes faster

Disadvantage of JOIN

  1. Not easy to read as subqueries.
  2. It is very confusing to decide which type of join needs to be used in what kind of situation.

Subqueries

A subquery can be defined as a query within a query.

Two types:

  1. Simple
  2. Correlated
Simple Corelated
A simple subquery is evaluated once only for each table A simple subquery is evaluated once only for each table A correlated subquery is evaluated once for each row.

Example of Simple Subquery

We will be using the Product and SalesOrderDetail table of Adventureworks2016.

SELECT Name
  FROM [Production].[Product]
 WHERE ProductID IN (SELECT ProductId 
                FROM [Sales]. [SalesOrderDetail]
               WHERE OrderQty > 5)

Output

Example of Correlated Subquery

select ProductID ,
sum(OrderQty) Orderqty 
from [Sales].[SalesOrderDetail] where ProductID in(select ProductID from [Production].[Product])
group by ProductID

Output

Advantage of Subquery

  1. Can replace complex joins in some cases
  2. A complex query can be broken down into series of logical steps

Disadvantage of Subquery

Read: Normalization-How to Normalize Database in a World of Abnormalities
  1. Data retrieval becomes slow.
  2. Overload on the system.

Logical Statements in SQL

Three types 

1) IIF()
2) CASE
3) CHOOSE

We will be using the Adventureworks database for the above mentioned SQL Statements.
 

IIF()

select LoginID,JobTitle,iif(MaritalStatus='S','SINGLE','MARRIED') [Marital Status] from [HumanResources].[Employee]

Output

CASE

select LoginID,JobTitle,
Case [MaritalStatus]
when 'M' then 'MARRIED'
when 'S' then 'SINGLE'
END
from [HumanResources].[Employee] 

Output

CHOOSE

SELECT 
    CHOOSE (2, 'First', 'Second', 'Third') Result;

Output

Exist

SELECT DepartmentID, Name   
FROM HumanResources.Department   
WHERE EXISTS (SELECT NULL)  
ORDER BY Name ASC ;  

Output

Delete using Select Statement

Delete from [dbo].[Employee] where Age in (Select Age from [dbo].[Employee] where Address=New York)

Output

Summary

In the above few paragraphs, I have tried to explain different types of joins, different types of subqueries and other logical statements. This would give the reader a good idea of the power of these SQL tools and how to use them in real-life scenarios. Happy reading!



fbicons FaceBook twitterTwitter google+Google+ lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    Sanchayan Banerjee

    I love to learn new things and also like sharing my knowledge with others. As an experienced IT Professional I like to update myself constantly with new and upcoming technologies. The database management system is one of my favorite subjects which I constantly explore.


Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

3 days 22 Mar 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

2 days 21 Mar 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

3 days 22 Mar 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

3 days 22 Mar 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

3 days 22 Mar 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

10 days 29 Mar 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

4 days 23 Mar 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

10 days 29 Mar 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

4 days 23 Mar 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

18 days 06 Apr 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

31 days 19 Apr 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

10 days 29 Mar 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews