Jan 24, 2020 
8.3k
29
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
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.
There are four types of joins
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]
SELECT Employee.EmpID, Employee.Empname, Project.Projectid, Project.ProjectName FROM Employee INNER JOIN Project ON Employee.EmpID=Project.EmpID;
SELECT Employee.Empname, Project.ProjectID, Project.ProjectName FROM Employee LEFT JOIN Project ON Employee.EmpID = Project.EmpID ;
SELECT Employee.Empname, Project.ProjectID, Project.ProjectName FROM Employee RIGHT JOIN Project ON Employee.EmpID = Project.EmpID ;
SELECT Employee.Empname, Project.ProjectID FROM Employee FULL JOIN Project ON Employee.EmpID = Project.EmpID;
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: Coalesce Function SQL Server Example
| 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] |
 |
 |
CROSS JOIN gives you an overview of all the possible combinations of tables when you are combining two tables.
Usually slow when the volume of data in the tables are very high.
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 Where Statement Clause with Examples
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
Disadvantage of JOIN
Subqueries
A subquery can be defined as a query within a query.
Two types:
| 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
Read: How To Use The SQL NOT EXISTS and EXISTS Operator ?
Disadvantage of Subquery
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!
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Feb 28, 2019
541.7k
541.7k