UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT) Difference of INTERSECT and INNER JOIN:

UNION and new feature of SQL Server 2005 (INTERSECTION, EXCEPT):

You might have read my previous articles on JOIN as per those articles; JOIN is multiplication of data whereas UNION is addition.

UNION does nothing other than stack the data of multiple result sets in one result sets. While using the UNION you have to consider few things like: column name, number and aliases should be mentioned in first part of the SELECT. ORDER BY clause will sort the complete result sets, it will use the column name of first SELECT statement and should be write down after the last SELECT statement.

Let us create two demo tables to use UNION.

–create first table for demonstration

CREATE TABLE CAR

(

ID int IDENTITY(1,1),

CarName VARCHAR(16)

)

–create second table for demo

CREATE TABLE BIKE

(

ID int IDENTITY(1,1),

BikeName VARCHAR(16)

)

–Insert records in CAR table

INSERT INTO CAR

SELECT ‘HONDA crv’ UNION ALL

SELECT ‘Mahi. Raunalt’ UNION ALL

SELECT ‘Test’

–Isert records in BIKE table.

INSERT INTO BIKE

SELECT ‘HONDA Karishma’ UNION ALL

SELECT ‘Bajaj DTSI’ UNION ALL

SELECT ‘Test’

Now, both the select statement of above two tables will be merged and will return single result sets.

–use of union all

SELECT ID,CarName FROM CAR

UNION ALL

SELECT ID,BikeName FROM BIKE

Order by CarName

Above query returns all the records of both tables. Though we gave ORDER BY for CarName but it will sort complete second column which include some data of bikeName as well. You cannot give ORDER BY on BikeName column.

Above query was just an example, you can use UNION for less than or equal to 256 tables. This is not at all small amount.

Intersection Union: Intersection Union find common row in both data sets. As soon as you read this statement, you will say, ohh, this could be done by INNER JOIN as well. So, the answer is INNER JOIN matches two table horizontally and INTERSECTION matches two datasets vertically. There is one more difference in INTERSECTION and INNER JOIN is that, Intersection query will see NULL as common and includes the row in the intersection and INNER JOIN will not even includes two different row with NULL value in result sets.

Now, have a look at INTERSET usage in below query.

–use of intersect union

SELECT ID,CarName FROM CAR

INTERSECT

SELECT ID,BikeName FROM BIKE

Order by CarName

You will get only one row which is same in both the table.

EXCEPT: Except (a.k.a. DIFFERENCE UNION) finds records exists in one data sets and not available in another datasets. In above case we have three records in CAR table but third record is exist in BIKE table as well so EXCEPT will display only first two records. Have a look at below query.

–use of EXCEPT

SELECT ID,CarName FROM CAR

EXCEPT

SELECT ID,BikeName FROM BIKE

Order by CarName

Reference: Ritesh Shah
Advertisements

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:

Business Logic with JOIN – Multiple Join examples in SQL-Server 2005:
I wrote my previous article about different type of JOIN in SQL-Server 2005. Due to the space constraint in that article, complex example has not been given there, only few simple examples. I felt to explode this topic further by giving solution to some really needed business logic.
If you want to create report for blood testing laboratory, who used to check samples of employee of different companies for different purposes. Following should be the table structure, we will be creating three table. One will store information about Order received from different companies. Another one will be the detail of that order, means which company sent how much samples of how much employee?? And the last one is further more details about how much test should be performs on which sample???
Let us create three tables.
–CREATING FIRST ORDER TABLE
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[Orders] Script Date: 03/08/2009 12:24:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Orders](
[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[OrderDate] [datetime] NOT NULL,
[CompanyName] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
Now, child table of above.
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[OrderDetails] Script Date: 03/08/2009 12:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SampleOfEmployee] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SampleReceivedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[SampleNumber] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [fk_orderid] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [fk_orderid]
One more child table.
USE [AdventureWorks]
GO
/****** Object: Table [dbo].[sampledetails] Script Date: 03/08/2009 12:43:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sampledetails](
[SampleNumber] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TestType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
PRIMARY KEY CLUSTERED
(
[SampleNumber] ASC,
[TestType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING ON
Now, this is time to insert few records on above tables.
–Received three order from two company
INSERT INTO Orders
SELECT ‘L1000’,‘2009-03-01’,‘Test Inc.’ UNION ALL
SELECT ‘L1001’,‘2009-03-02’,‘Zeel Inc.’ UNION ALL
SELECT ‘L1002’,‘2009-03-04’,‘ABC Inc.’
–received two sample for first order, one sample for second order
–sample for third order yet to receive
INSERT INTO OrderDetails
SELECT ‘L1000’,‘L1000-01’,‘James’,‘2009-03-01’ UNION ALL
SELECT ‘L1000’,‘L1000-02’,‘John’,‘2009-04-01’ UNION ALL
SELECT ‘L1001’,‘L1001-01’,‘Smita’,‘2009-03-05’
–details of sample, which test suppose to be performed
–on which sample
INSERT INTO SampleDetails
SELECT ‘L1000-01’,‘Cancer’ UNION ALL
SELECT ‘L1000-01’,‘AIDS’ UNION ALL
SELECT ‘L1000-02’,‘BP’ UNION ALL
SELECT ‘L1001-01’,‘AIDS’ UNION ALL
SELECT ‘L1001-01’,‘Cancer’
Well, now we are ready to pull data out of three tables with different type of JOIN which we have seen in my previous article.
NOTE: many different logic can be used to perform following tasks, I am giving this just to explain the use of JOIN otherwise there are some more facility which can handle all of the below situation other than join.
If you want to get OrderID, OrderDate, Company (who gave the order??), SampleNumber, EmployeeName (for which employee company has sent the sample??), TestType (which test to be performed in above tables?)
You can use following query which has used one LEFT OUTER JOIN and one INNER JOIN.
SELECT O.OrderID,O.OrderDate,O.CompanyName,
OD.SampleofEmployee,OD.SampleNumber,SD.TestType
FROM
ORDERS AS O
LEFT OUTER JOIN
ORDERDETAILS AS OD
ON
O.OrderID=OD.OrderID
INNER JOIN
SampleDetails AS SD
ON
OD.SampleNumber=SD.SampleNumber
If there is a need to find how many samples came from which company. Use following query with aggregate function COUNT.
SELECT
O.CompanyName,Count(SampleNumber) as ‘TOTAL’
FROM
ORDERS AS O
LEFT OUTER JOIN
ORDERDETAILS AS OD
ON
O.OrderID=OD.OrderID
GROUP BY O.CompanyName
ORDER BY TOTAL DESC
Reference: Ritesh Shah

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:

Complete ref of SQL-Server Join, Inner Join, Left Outer Join, Right Outer Join, Full Outer Join, in SQL-Server 2005:
Merging data is heart of SQL and it shows the depth of relational algebra as well as the power and flexibility of SQL. SQL is excellent at selecting the data and SQL Developer can be master by understanding the relational algebra.
Join seems known technology but it is fun all time. Developer can make very creative query with joins by making data twist and shout. Pulling the data out of join could be the answer of very big code of programming language with iteration. It is much faster than any loop in your code. I promise you will have fun by learning this powerful technique.
SQL-Server 2005 is relational database so you always need to join more than one table in order to get complete information out of data tables. Since, tables are self explanatory itself, order of table doesn’t matter.
Here are the types of available joins in SQL-Server 2005:
Note: AdventureWorks database is going to be used throughout in this chapter.
Inner Join: It’s a very common and frequently needed join. Common intersection will be pulled out in this kind of join. It means, it will compare the result sets of first table with result sets of second table based on common fields or based on primary key and foreign key relationship. Below given is an example of Inner Join.
SELECT Sales.SalesPerson.SalesPersonID,
Sales.SalesPersonQuotaHistory.SalesQuota
FROM Sales.SalesPerson
INNER JOIN
Sales.SalesPersonQuotaHistory
ON
Sales.SalesPerson.SalesPersonID=Sales.SalesPersonQuotaHistory.SalesPersonID
You can get join two table with SalesPersonID in above case as both have common field.
(Part of Outer Join) Left Outer Join: Include all rows from the left side table no matter whether matching rows are exists in right side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Left Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Left out join has been used so it will list all employees, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Right Outer Join: Include all rows from the right side table no matter whether matching rows are exists in left side table or not. It means that Outer join is an extended version of Inner Join as it not only gives your common intersection but it do gives non matching data from left side or right side along with intersection.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Right Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Employee table is joined with EmployeeAddress table based on EmployeeID. Right outer join has been used so it will list all employees’ Address, no matter where it meets associated entry in EmployeeAddress.
(Part of Outer Join) Full Outer Join: Includes all rows from both tables regardless of match exists. It means it a union distinct of Left Outer Join and Right Outer Join.
SELECT HumanResources.Employee.EmployeeID,HumanResources.Employee.NationalIDNumber,
HumanResources.EmployeeAddress.AddressID
FROM HumanResources.Employee Full Outer Join HumanResources.EmployeeAddress
ON
HumanResources.Employee.EmployeeID=HumanResources.EmployeeAddress.EmployeeID
Self Join: A self-join refers back to itself. You can use this type of unary relationship for recursive purpose. Like employee table to find boss.
–creating table for self join
create table emp
(
ID int Identity(1,1),
Name Varchar(15),
BossID int
)
GO
–Inserting some records in above table.
Insert Into emp
SELECT ‘DM’,0 UNION ALL
SELECT ‘MA’,1 UNION ALL
SELECT ‘UC’,2 UNION ALL
SELECT ‘LU’,2
GO
–equi join which will find boss name for every employee, DM is a main boss in this case.
SELECT e.ID,e.Name,ep.Name as ‘boss name’
FROM emp e JOIN emp ep
on
e.bossid=ep.id
GO
There are few more types of join like theta join, equi join, natural join etc. which I will explain in coming article.
Reference: Ritesh Shah