Crazy TSQL Queries play time

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


See Also

Transact-SQL Portal

Sort by: Published Date | Most Recent | Most Useful
Comments
  • This is a nice article, Ronen.

    It's better to change reference links for "SQL basic JOIN tutorial" and "SQL basic UNION tytorial" and link them from "technet.microsoft.com/.../ms191517(v=sql.105).aspx" and "technet.microsoft.com/.../ms180026.aspx".

    This is just a short suggestion. Your article is great with no doubt.

    Thanks.

  • In previous comment, sounds like there is somthing wrong with the links. So I repeat them again:

    technet.microsoft.com/.../ms191517(v=sql.105).aspx

    technet.microsoft.com/.../ms180026.aspx

  • Thanks Saeid,

    I just published the first result that Google returned :-)

    * I'll add the links to BOL As you suggest (in addition to the existing links). I must say that I think that the explanation of w3schools is much simpler and better fit for novice users than in BOL. This manual includes neat LAB can play with queries on-line. moreover, The w3schools site is a place i used years ago. I used to mention it a lot on my old site k4school.com/.../all.asp (k4school is very very old site i developed, it is close for more then 10 years). If you search for w3schools in the forums, then you will found lots of link.

  • Thanks Saeid,

    I just published the first result that Google returned :-)

    * I'll add the links to BOL As you suggest (in addition to the existing links). I must say that I think that the explanation of w3schools is much simpler and better fit for novice users than in BOL. This manual includes neat LAB can play with queries on-line. moreover, The w3schools site is a place i used years ago. I used to mention it a lot on my old site k4school.com/.../all.asp (k4school is very very old site i developed, it is close for more then 10 years). If you search for w3schools in the forums, then you will found lots of link.

  • Thanks Ronen,

    I agree with you. That's simpler.

  • great article

    i have just used it now for interview

    i am new to this site and just came from your link at Facebook

  • Congratulations on winning a medal! blogs.technet.com/.../technet-guru-awards-october-2013.aspx

  • Thanks all for the feedback :-)