SQL Tips: Why we need to have RIGHT OUTER JOIN in SQL

Recently I was reading some article and in comments section I found this interesting question.

Q’s: If we perform left outer join on employee and department it will return all records from left table and only matching from right. similarly, right outer join will return all the records from department and only matching from employee table. NOW… what if we swap the tables in left outer join. i.e if we perform left outer join of department and employee which will return same records as output as right outer join.

THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL???

Before I dive into the answer, I would love to give a brief intro about Left outer join and Right outer join.

join_left_right

For the sake of this example, lets say you have 100 students, 70 of which have enrollments. You have a total of 50 enrollments, 40 of which have at least 1 student and 10 enrollments have no student.

LEFT OUTER JOIN would be “show me all students, with their corresponding enrollments if they have one”.
This might be a general student list, or could be used to identify students with no enrollments.
Returns 100 rows

RIGHT OUTER JOIN would be “show me all enrollments, and the students assigned to them if there are any”.
This could be used to identify enrollments that have no students assigned, or enrollments that have too many students.
Returns 80 rows (list of 70 students in the 40 enrollments, plus the 10 enrollments with no student)

Lets get back to our main question,”WHY DO WE NEED RIGHT OUTER JOIN”?

Answer to the above question was also provided by some anonymous user then and there in itself.

Anything you can write as a “RIGHT OUTER” could be written as a “LEFT OUTER” by just flipping the tables, so technically “RIGHT OUTER” isn’t functionally required in order to have working code.

It was created to allow the coder to express it that way if it makes more sense to them that way. So basically it exists for readability and potential maintainability.

Having said that I rarely see anyone code a “RIGHT OUTER”, most people just automatically order their tables to be “LEFT OUTER” joins if an outer is needed.

I found it interesting, so thought to share with others also. May be it can be of some help to some one else.

Advertisements

SQL Tips: Parsing special characters in Queries

Recently I was working on some SSIS reports. Everything was working fine up till my queries ran on testing server where it failed during parsing of special characters in the query.

Below query worked fine  when there was no special character in the data.

DECLARE @t TABLE
(
StudentID INT,
Subjects VARCHAR(8000)
)

INSERT INTO @T VALUES 
(111,'English French,Maths,Science'),
(112,'English Spanish,English,Science')

SELECT StudentId 'Student Id',
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS 'Subject Name'
FROM
(
SELECT 
 StudentID,
 CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
FROM @t
)XmlTable
CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)

Output:

Output5.JPG

It started failing as soon as I got special characters in my data.

--(111,'English & French,Maths,Science'),
--(112,'English & Spanish,English,Science')

Msg 9421, Level 16, State 1, Line 13
XML parsing: line 1, character 28, illegal name character

This is what I made changes to my query to get the desired results. Here I am using (SELECT ... FOR XML PATH()) instead of the naked Subjects. I am letting FOR XML PATH() do all the hard work for me.

--Changed below line
CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
--with
CAST('' + REPLACE((SELECT Subjects AS [*] FOR XML PATH('')),',','') + '' AS XML) 
AS node

Output:

output6

We can see now we are getting the results with “&” characters.

I hope this will help someone and will save some precious time.

SQL Tips – Comma separated list

Today in an interview interviewer asked me to make a query to create a comma separated list which I did easily using STUFF and XML Path. Later I realized I could do it through other ways also. I will write in this blog all possible ways I know.
Table Schema

CREATE TABLE Student(StudentId int,SubjectName varchar(100))

INSERT INTO Student VALUES(111,'English')
INSERT INTO Student VALUES(111,'Science')
INSERT INTO Student VALUES(112,'English')
INSERT INTO Student VALUES(112,'Science')
INSERT INTO Student VALUES(111,'Maths')

Query 1: Below query will pull list for each StudentId  one at a time.

SELECT STUFF((SELECT ',' + SubjectName
FROM Student SS
WHERE SS.StudentId = S.StudentId
ORDER BY SubjectName
FOR XML PATH('')), 1, 1, '') AS SubjectList
FROM Student S
GROUP BY S.StudentId

output1

Query 2: Below query will return all the subjects in one string value. Basically, in this code we are checking if the variable has been initialized yet. If it hasn’t, set it to the empty string, and append the first SubjectName (no leading comma). If it has, then append a comma, then append the SubjectName.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + SubjectName
FROM Student
SELECT @listStr 'Subject List'

output2

Query 3: Below query will also return the same output as above query, but its implementation is different. This is also a good way but not the best way.

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + SubjectName + ','
FROM Student
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1) 'Student List'

output2

We can also use cursors to get same results but they aren’t the efficient way.

Splitting comma separated list

Suppose we have another scenario where we have a string of comma separated list and we have to return a table of it.

DECLARE @t TABLE
(
StudentID INT,
Subjects VARCHAR(8000)
)

INSERT INTO @T
SELECT  
        StudentId,
        STUFF((SELECT  ',' + SubjectName
            FROM Student SS
            WHERE  SS.StudentId = S.StudentId
            ORDER BY SubjectName
        FOR XML PATH('')), 1, 1, '') AS 'Subject List'
FROM Student S
GROUP BY S.StudentId

output3

Using below query we can split a comma separated list into a table.

Query

SELECT StudentId 'Student Id',
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS 'Subject Name'
FROM
(
SELECT 
	StudentID,
	CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
FROM @t
)XmlTable
CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)

output4

We can use above query in UDF’s also to return a table value as output.

I hope it will help someone. Any comments or suggests are welcome.

Dynamic query for Parent/Child

Often new developers gets tasks to create dynamic menus from database and the first thing they stuck is how to query the data from database.  I am not writing anything new; its just I want to write assuming it will help somebody someday.

To begin with I will create a new SQL Table first. The below table consists of four columns:

  1. MenuId
  2. MenuName
  3. Description
  4. ParentId

GO

/****** Object: Table [dbo].[tblMenu] Script Date: 03/09/2016 11:18:44 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblMenu](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[MenuName] [varchar](50) NULL,
[Description] [varchar](255) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[MenuID] 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 OFF
GO

I inserted somesample data using below query.

INSERT INTO [tblMenu]
Select ‘Product’,’A List of Products’, NULL
UNION ALL Select ‘Applications’,’Appliations’,NULL
UNION ALL Select ‘Document’,’Documentation’, NULL
UNION ALL Select ‘Support’,’Support’, NULL
UNION ALL Select ‘Download’,’Download’, NULL
UNION ALL Select ‘Background’,’ProductBackground’, 1
UNION ALL Select ‘Details’,’Product Details’, 1
UNION ALL Select ‘Mobile Device’,’Mobile DeviceApplications’, 2
UNION ALL Select ‘Portal’,’Portal Applications’,2
UNION ALL Select ‘Web Applicaitons’,’WebApplications’, 2
UNION ALL Select ‘Demo’,’Demo Applicaitons’, 2
UNION ALL Select ‘Performance Tests’,’ApplicationPerformance Tests’, 2
UNION ALL Select ‘Tutorials’,’TutorialDocumentation’, 3
UNION ALL Select ‘Programmers’,’ProgrammDocumentation’, 3
UNION ALL Select ‘FAQ’,’Frequently AskedQuestions’, 4
UNION ALL Select ‘Forum’,’Forum’, 4
UNION ALL Select ‘Contact Us’,’Contact Us’, 4
UNION ALL Select ‘InternetRestrictions’,’Internet Restrictions’, 6
UNION ALL Select ‘Speed Solution’,’Speed Solutions’,6
UNION ALL Select ‘Application Center Test’,’Application Center Test Results’, 12
UNION ALL Select ‘Modem Results’,’Modem Results’,12

After inserting the data this is how my data looks like

Sql Data

Then the final query to get the desired output.

SELECT
parent.MenuId ParentId,
parent.MenuName Menu,
child.MenuId ChildId,
child.MenuName as SubMenu
FROM dbo.tblMenu parent
INNER JOIN tblMenu child ON parent.MenuId = child.ParentId;

This is how data looks now.

final query

Hope it will help somebody.

 

Generate rows based on the values of a column

Recently while working on a module, I was asked to create data rows based on the column values of a SQL table. I needed to generate the rows in table B based on the values of column table A.

So we had a  SQL table with UserId and Quantity as column and below possible values.

UserId Quantity
ACC_xxx_003 1
ACC_xxx_004 4
ACC_xxx_005 1
ACC_xxx_006 3

The desired output was:

UserId Quantity
ACC_xxx_003 ACC_xxx_003-001
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_004 ACC_xxx_004-004
ACC_xxx_005 ACC_xxx_005-001
ACC_xxx_006 ACC_xxx_006-003
ACC_xxx_006 ACC_xxx_006-003
ACC_xxx_006 ACC_xxx_006-003

To get this working there were different approaches but I chose to use CTE for its simplicity and ease of use. Below is the code which I created.

CREATE TABLE UserMapping (
	UserId VARCHAR(50)
	,Quantity INT
	)
 
INSERT INTO UserMapping
VALUES 
	('ACC_xxx_003',1)
	,('ACC_xxx_004',4)
	,('ACC_xxx_005',1)
	,('ACC_xxx_006',3);
 
	--===== Create number table on-the-fly
WITH CTE (n)
AS (
	SELECT 1 AS n
	
	UNION ALL
	
	SELECT n + 1 AS n
	FROM CTE
	WHERE n < 101
	)
	,Num2 (n)
AS (
	SELECT 1
	FROM CTE AS X
		,CTE AS Y
	)
	,Nums (n)
AS (
	SELECT ROW_NUMBER() OVER (
			ORDER BY n
			)
	FROM Num2
	)
SELECT UserId
	,Quantity AS Serial
INTO FormattedTable
FROM UserMapping
CROSS APPLY (
	SELECT n
	FROM Nums
	) d
WHERE Quantity >= n
 
SELECT UserId
,UserId + '-' + right('000' + cast(Serial AS VARCHAR(50)), 3) AS Serial
FROM FormattedTable
ORDER BY UserId
 
DROP TABLE UserMapping
	,FormattedTable