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
understanding of the feature you are writing about :-)
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?
/******************************************** DDL+DML */
-- UNION can be done using a FULL OUTER join
custId ,docNo ,docSum
I.custId = 1234
C.custId = 1234
'Ndfsgff sfg fgds'
using SUB QUERY */
N.UserId NUserId, N.DocContent NDocContent, U.UserId UUserId, U.
U.UserId = N.UserId
N.UserId NUserId,N.DocContent NDocContent,N.UserId UUserId,(
U.UserId = N.UserId) UName
using SUB QUERY &
* we are using the DDL+DML from above.
* 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.
using SUB QUERY &
-- using our "LEFT JOIN" query without the filter on first result set
N.UserId NUserId,N.DocContent NDocContent,(
U.UserId = N.UserId) UUserId,(
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.
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.
'Yes it is NULL'
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.
Using While Loop */
-- Using Cursor
-- we need a "While Loop" in order to loop through all the table recordset
WHILE @@FETCH_STATUS = 0
-- Using Loop
NVARCHAR(100) = (
ID = 1)
WHILE @Counter <= @RowNum
@Counter += 1
@MyVar = (
ID = @Counter)
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 */
* The idea for this Post came from the question here (Hebrew):
There are dozens of similar questions online :-)
* SQL basic JOIN tutorial
* SQL basic UNION tytorial
* highly recommended to check this link if you think about comparing "Cursor" and "While Loop"
* Transact-SQL Portal
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.
In previous comment, sounds like there is somthing wrong with the links. So I repeat them again:
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.
I agree with you. That's simpler.
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 :-)