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

Read: Learn How to Add Column Using SQL Server ALTER TABLE ADD Column Statement

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.

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.

Read: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

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

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

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

Read: SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access

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

Disadvantage of Subquery

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!