Our Support: During the COVID-19 outbreak, we request learners to CALL US for Special Discounts!

- 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: Power BI - Getting Started with Query Editor in Power BI

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: SQL Developer Resume Template Sample – Complete Guide for Fresher
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: What Is SQL Candidate Key? Difference between Primary Key & Candidate Key
  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!




    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

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 14 Jul 2020

DevOps

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

Upcoming Class

-0 day 10 Jul 2020

Data Science

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

Upcoming Class

6 days 16 Jul 2020

Hadoop

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

Upcoming Class

7 days 17 Jul 2020

Salesforce

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

Upcoming Class

5 days 15 Jul 2020

QA

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

Upcoming Class

-0 day 10 Jul 2020

Business Analyst

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

Upcoming Class

4 days 14 Jul 2020

MS SQL Server

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

Upcoming Class

5 days 15 Jul 2020

Python

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

Upcoming Class

13 days 23 Jul 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

4 days 14 Jul 2020

Machine Learning

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

Upcoming Class

7 days 17 Jul 2020

Tableau

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

Upcoming Class

3 days 13 Jul 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews