SQL Server – Types of JOIN

Type of JOIN is quite often being asked during the interview where we are asked to list out the type of JOIN and the differences of each of them. I found a simple illustration of the JOIN command to give the first glance idea of what JOIN is.

Type of JOIN includes:-
1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN  (FULL OUTER JOIN)
5. CROSS JOIN

The picture above explains the results set to be returned when these type of JOIN are used in our queries. Next time, whenever there is a confusion of which JOIN command to be used, refer to this image to clarify our doubts.

However, let us have some short description of each command too, for our better understanding.

1. INNER JOIN
Returns all rows for which there is at least one match in BOTH tables.

2. LEFT JOIN
Returns all rows from the left table, and the matched rows from the right table.
Eg: The results will contain all records from the left table, even if the JOIN condition does not find any matching records in the right table, but with NULL in each column from the right table.

3. RIGHT JOIN
Returns all rows from the right table, and the matched rows from the left table. It is exactly opposite of the LEFT JOIN.
Eg: The results will contain all records from the right table, even if the JOIN condition does not find any matching records in the left table, but with NULL in each column from the left table.

4. FULL JOIN
Returns all rows for which there is a match in EITHER of the tables.
Eg: Its result set is equivalent to performing a UNION of the results of left and right outer queries.

5. CROSS JOIN
Returns all records where each row from the first table is combined with each row from the second table. Refer to the image below which I captured from the explanation made by Pinal D. in his blog.

SQL – NULL

Last year during one of the SQL Server User Group event brought by Microsoft, they invited Pinal Dave. I am sure you know this guy who wrote many SQL articles in his blog page, blog.sqlauthority.com.

One of the most interesting topics that he shared that morning was the SQL NULL. Sometimes, we have hard times dealing with the null values when we are working on our queries. During one of the interview, I was being asked about the returned result when we execute the below query.

select case when null = null then 'yup' else 'nope' end

I could not remember if I get the answer correct but I did think the query is wrong. According to my search in the Internet, the same question appeared in one of the links where the explanation is as below:-

This query will yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

It suggests the correct way to compare null value is using the IS operator. You can give a try running it from the SSMS to see the returned result set which I am going to share here as well.

select case when null is null then 'yup' else 'nope' end

Next, I wish to search a bit more about NULL values.

An article from TechNet website, it says below:-

A value of NULL indicates that the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown.

Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN. Interesting as it is, there are many more things about NULL values can be read from the websites. As for now, we know that to check the NULL values, we shall use IS or IS NOT in WHERE clause.

SQL Server – Difference Between UNION And UNION ALL

I am trying to recall some of the interview questions that I was being asked during my job hunting and would like to share some studies or findings which I read from the experts regarding these questions. The first one that I remember is the differences between UNION and UNION ALL.

I believe in many cases we do use both of these commands in our queries. And, what are their differences and in term of performance which one is better?

UNION command is used in the queries to select data from two or more tables similarly to JOIN command, except UNION command selected columns need to be same data types and same number of columns to be selected. It returns the distinct data of both tables. It does the SELECT DISTINCT on the results set.

UNION ALL command is same as the UNION command except it returns all the data from the selected tables. In other words, UNION ALL includes duplicated rows.

However, the UNION ALL gives the faster results when we execute the queries. You can check it from the execution plan to compare both. Reason is UNION command will do the filtering during the execution. If you are sure that your return results are unique, it would be a good idea to use UNION ALL for better performance.