## 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`

.

`/******************************************** 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` `*/`
`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.

* 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` `*/`
`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 UNION tytorial
http://technet.microsoft.com/en-us/library/ms180026.aspx
http://www.w3schools.com/sql/sql_union.asp

* 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