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
|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
The queries to create the three tables are as follows.
CREATE TABLE [dbo].[Client]( [Clientid] [varchar](50) NULL, [Clientname] [varchar](max) NULL, [Age] [int] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
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]
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
|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 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
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 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.
select * from [dbo].[mergeexample1] union select * from [dbo].[mergeexample2]
The output looks like
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
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
A subquery can be defined as a query within a query.
|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)
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
Disadvantage of Subquery
Logical Statements in SQL
We will be using the Adventureworks database for the above mentioned SQL Statements.
select LoginID,JobTitle,iif(MaritalStatus='S','SINGLE','MARRIED') [Marital Status] from [HumanResources].[Employee]
select LoginID,JobTitle, Case [MaritalStatus] when 'M' then 'MARRIED' when 'S' then 'SINGLE' END from [HumanResources].[Employee] Output
SELECT CHOOSE (2, 'First', 'Second', 'Third') Result;
SELECT DepartmentID, Name FROM HumanResources.Department WHERE EXISTS (SELECT NULL) ORDER BY Name ASC ;
Delete using Select Statement
Delete from [dbo].[Employee] where Age in (Select Age from [dbo].[Employee] where Address=New York)
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!
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.
MS SQL Server
Receive Latest Materials and Offers on SQL Server Course