Crazy TSQL Queries play time
Background
Most of the articles in WIKI try to bring us a tutorials on specific topic or best solution for a specific problem. This post is different! It has nothing to do with Optimization, Query's cost or Best solution (getting the best query) or tutorial, but instead, it is all about crazy queries for getting most basic "build-in feature" (action or function for example) without using the "build-in feature".
The idea for this post came from lots of questions we can find in forums, and it look like they do not have any reason to be asked in the first place (for example this question from MSDN SQL Hebrew forum). These questions most likely came from Job Interviews, courses, exams, and riddles. For example: "how can we build a UNION query using JOIN", "how can we build a JOIN operation without using the JOIN".
While none of these issues will use in production server probably, it's great way to make sure that we really understand the operation/function we are trying to replace and those that we use for the replace.
Please feel free to add any idea, crazy as it is, as long as it requires the ability and understanding of the feature you are writing about :-)
Playing with JOIN & UNION
Learning about "UNION" is simple, Learning about "JOIN" can be done in one hour, but how many of us really understand the meaning, and able to convert "JOIN" to "UNION" and vice versa?
UNION using JOIN
/******************************************** DDL+DML */
CREATE TABLE invoices (custId int,docNo int,docSum smallmoney)
CREATE TABLE creditNotes (custId int,docNo int,docSum smallmoney)
GO
INSERT INTO invoices VALUES (1234,1,1000),(1234,2,987)
INSERT INTO creditNotes VALUES (1234,10,456),(1234,11,256),(1234,12,252),(1234,13,253),(1234,14,254)
GO
/******************************************** UNION usin JOIN */
-- UNION can be done using a FULL OUTER join
SELECT custId ,docNo ,docSum
FROM invoices
WHERE custId=1234
UNION
SELECT custId ,docNo ,docSum
FROM creditNotes
WHERE custId=1234
GO
SELECT
COALESCE(I.custId, C.custId) as custId
,COALESCE(I.docNo, C.docNo) as docNo
,COALESCE(I.docSum, C.docSum) as docSum
from invoices I
FULL OUTER JOIN creditNotes C ON 1=0
where I.custId = 1234 or C.custId = 1234
GO
.
INNER JOIN using SUB QUERY
/******************************************** DDL+DML */
CREATE TABLE UsersTbl (UserId int, Name nvarchar(100))
CREATE TABLE NotesTbl (UserId int,DocContent nvarchar(100))
GO
INSERT INTO UsersTbl VALUES (1,'A'),(2,'B'),(4,'N'),(11,'F')
INSERT INTO NotesTbl VALUES (1,'fgsdfgsg'),(2,'fgdgdfgs'),(1,'Ndfsgff sfg fgds'),(9,'Ndfsgff sfg fgds')
GO
/******************************************** INNER JOIN using SUB QUERY */
select
N.UserId NUserId, N.DocContent NDocContent, U.UserId UUserId, U.Name UName
from UsersTbl U
INNER join NotesTbl N on U.UserId = N.UserId
GO
select
N.UserId NUserId,N.DocContent NDocContent,N.UserId UUserId,(select Name from UsersTbl U where U.UserId = N.UserId) UName
from NotesTbl N
where N.UserId in (select UserId from UsersTbl)
GO
.
LEFT JOIN using SUB QUERY & UNION
/******************************************** LEFT JOIN using SUB QUERY & UNION */
select
N.UserId NUserId, N.DocContent NDocContent, U.UserId UUserId, U.Name UName
from UsersTbl U
LEFT join NotesTbl N on U.UserId = N.UserId
GO
select
N.UserId NUserId,N.DocContent NDocContent,N.UserId UUserId,(select Name from UsersTbl U where U.UserId = N.UserId) UName
from NotesTbl N
where N.UserId in (select UserId from UsersTbl)
UNION ALL
select NULL,NULL,UserId,Name
from UsersTbl
where UserId not in (select UserId from NotesTbl)
GO
* we are using the DDL+DML from above.
RIGHT JOIN we can query using LEFT JOIN
* we use the above LEFT JOIN query idea.
FULL OUTER JOIN using "LEFT JOIN" UNION "RIGHT JOIN"
* We can use the above queries and UNION to get both LEFT JOIN and RIGHT JOIN result set.
FULL OUTER JOIN using SUB QUERY & UNION
/******************************************** FULL OUTER JOIN using SUB QUERY & UNION */
select
N.UserId NUserId, N.DocContent NDocContent, U.UserId UUserId, U.Name UName
from UsersTbl U
FULL OUTER join NotesTbl N on U.UserId = N.UserId
GO
-- using our "LEFT JOIN" query without the filter on first result set
select
N.UserId NUserId,N.DocContent NDocContent,(select U.UserId from UsersTbl U where U.UserId = N.UserId) UUserId,(select Name from UsersTbl U where U.UserId = N.UserId) UName
from NotesTbl N
UNION ALL
select NULL,NULL,UserId,Name
from UsersTbl
where UserId not in (select UserId from NotesTbl)
GO
.
Playing with NULL
The internet is full with question about NULL.
What is so confusing about NULL that make it a great subject for debates?
NULL is not equal NULL
That's make it a great playground for us.
ISNULL using COALESCE
Let's start with simple example. the function ISNULL Replaces the first parameter with specified replacement value, if it is NULL. The function COALESCE returns the value of the first expression in a list, that initially does not evaluate to NULL.
/******************************************** ISNULL using COALESCE */
declare @QQ01 as nvarchar(10) = 'd'
select ISNULL(@QQ01,'Yes it is NULL')
SELECT COALESCE(@QQ01,'Yes it is NULL')
GO
COALESCE using ISNULL
/******************************************** COALESCE using ISNULL */
declare @QQ01 as nvarchar(10) = NULL
declare @QQ02 as nvarchar(10) = 'B'
declare @QQ03 as nvarchar(10) = NULL
declare @QQ04 as nvarchar(10) = 'D'
select COALESCE(@QQ01,@QQ02,@QQ03,@QQ04)
select ISNULL(@QQ01,ISNULL(@QQ02,ISNULL(@QQ03,@QQ04)))
GO
.
Playing with Cursor and Loops
There are lot of questions about the difference between "Cursor" and "While Loop". This is a fundamental mistake to compare them at all. It's like comparing a car and a boat. We use the car moving on land, and we use a boat to travel at sea. I would not recommend anyone to try the opposite. That's might be another playground for us here.
Cursor Using While Loop (without using cursor)
use tempdb
GO
/******************************************** DDL+DML */
CREATE TABLE CursorAndLoopTbl(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Txt NVARCHAR(100)
)
GO
INSERT INTO CursorAndLoopTbl (Txt)
SELECT top 10000 LEFT(REPLICATE(CAST(NEWID() AS VARCHAR(36)),30),100)
FROM sys.all_columns
CROSS JOIN sys.all_objects
GO
select * from CursorAndLoopTbl
GO
/******************************************** Cursor Using While Loop */
-- Using Cursor
DECLARE MyCursor CURSOR FAST_FORWARD
FOR (SELECT Txt FROM CursorAndLoopTbl)
GO
declare @MyVar as NVARCHAR(100)
OPEN MyCursor
FETCH NEXT
FROM MyCursor
INTO @MyVar
-- we need a "While Loop" in order to loop through all the table recordset
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT @MyVar
FETCH NEXT
FROM MyCursor
INTO @MyVar
END
CLOSE MyCursor
GO
DEALLOCATE MyCursor
GO
-- Using Loop
DECLARE @Counter INT = 1
DECLARE @RowNum INT = (SELECT COUNT(*) FROM CursorAndLoopTbl)
DECLARE @MyVar as NVARCHAR(100) = (select Txt from CursorAndLoopTbl where ID = 1)
WHILE @Counter <= @RowNum BEGIN
PRINT @MyVar
SET @Counter += 1
SELECT @MyVar = (select Txt from CursorAndLoopTbl where ID = @Counter)
END
GO
DROP TABLE CursorAndLoopTbl
GO
DISTINCT Using GROUP BY
The SELECT DISTINCT statement is used to return only distinct (different) values. Using Group By we can group all identical records together in one group, and get the same result.
/**************************************** DDL+DML */
CREATE TABLE T (ID int,Txt NVARCHAR(10))
GO
INSERT T
SELECT 1, 'a' UNION all
SELECT 2, 's' UNION all
SELECT 3, 'd' UNION all
SELECT 4, 'f' UNION all
SELECT 2, 's' UNION all
SELECT 1, 'a' UNION all
SELECT 4, 'f' UNION all
SELECT 5, 'a'
GO
/**************************************** using DISTINCT */
SELECT DISTINCT ID, Txt from T
GO
/**************************************** using GROUP BY */
SELECT ID, Txt from T
GROUP BY ID, Txt
GO
.
References & Resources
* The idea for this Post came from the question here (Hebrew):
http://social.technet.microsoft.com/Forums/he-IL/03fa90e1-1a2a-4756-8ca3-44ac3b015cf1/-?forum=sqlhe
There are dozens of similar questions online :-)
* SQL basic JOIN tutorial
http://technet.microsoft.com/en-us/library/ms191517(v=sql.105).aspx
http://www.w3schools.com/sql/sql_join.asp
* SQL basic UNION tytorial
http://technet.microsoft.com/en-us/library/ms180026.aspx
http://www.w3schools.com/sql/sql_union.asp
* Cursor
http://technet.microsoft.com/en-us/library/ms181441.aspx
* WHILE
http://technet.microsoft.com/en-us/library/ms178642.aspx
* highly recommended to check this link if you think about comparing "Cursor" and "While Loop"
http://ariely.info/Blog/tabid/83/EntryId/132/SQL-Server-cursor-loop.aspx