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.

Advertisements

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.

Iciban Sushi

Iciban Sushi, AMK Hub, Singapore.

After the 2XU marathon on the Sunday’s morning, I took a short nap in the afternoon after reached home and showered. I was too eager to join my cousin and my aunt for dinner that night, just before I prepared to depart from Singapore to Kuala Lumpur.

My cousin told me they planned to have dinner at the AMK Hub instead of having the home cooked dinner. I left the house about 2 hours earlier after the laundry and reached AMK Hub at 4.30PM. We stopped by at the FairPrice before dinner.


It took us quite long to get a table although we reached earlier because my aunt was sitting on the wheelchair, so sushi bar is not convenient for her. They use the numbered queuing system and it is more systematic. The waiters and waitresses are polite and approachable. They are mainly Filipino.

I was unable to decide whether I want to try their Udon or getting the rice set. After seeing the menu, I decided to try the new set on the menu, Kaisen Chirashi Don. The word “chirashi” means “scattered”, so this is basically a big bowl of rice mixed with salmon, tuna and snowfish with vegetables. The base is quite plain and I added some chili powder onto it.

The ingredients are fresh, generous amount and filling. The taste is quite plain and that brings out the taste of freshness. On top of that, the set comes with a bowl of hot miso soup. We completed our meals with additional maki.

It was a day with full load of carbo after running for half marathon. I am so happy and satisfied. 🙂

Pig & Wolf

Pig & Wolf, Pavilion, Kuala Lumpur, Malaysia.
Address: 7.01.07 & E7.01.07 Level 7, Pavilion Mall, 168, Jalan Bukit Bintang, Kuala Lumpur, Malaysia.

It has been a while since my last visit to Pavilion and now, it has a new wing called Pavilion Elite with many branded outlets and restaurants situated at this new wing. Besides that, I came across a another new wing in the old Pavilion, the Level 7 which has plenty of restaurants there.

One of them is the Pig and Wolf. It is a non-Halal restaurant. My apologies to all my Muslim readers.

According to the Pavilion’s website, Pig and Wolf restaurant is a subsidiary of the popular 3 Little Pigs & The Big Bad Wolf. It is a casual dining restaurant that offers a mixture of Western and Asian cuisines. It offers from pork burgers, pasta to pork ribs.

Together with my friend, we explored this new restaurant. Initially, we wanted to order the set meal which comes with a soft drink and soup of the day. However, after looking at the a la carte dishes, we decided to order separately which we can have what we wanted to try.

During our visit they were doing a ‘Buy 1 Free 1’ promotion for the coffee’s menu which we both felt it was a better offer than having the soft drinks from the set meal. So, my friend picked the Iced Mocha and I chose the Iced Latte. Their coffee is just right and does not need extra sugar syrup.

My friend spotted the luncheon meat fries on the menu and we ordered one basket to share together. It is a simple appetizer which we can do it at home too. The luncheon meat is slightly fried and it tastes nice and crispy.

We ordered the Mighty Porky Burger which is one of the recommended dish on their menu. The presentation of the burger is still alright with some salads on one side and wedges on the another side. I liked the crispy and hot wedges. As for the pork burger, the pork patty is slightly dry.

We ordered the Pork Steak too which was recommended by the waiter. The meat was well marinated and strong garlic smell. The meat is juicier than the pork burger and it has the same side dishes.

Overall, the food portion is good enough, maybe they can improve the taste of the food to be more attractive. The environment in the restaurant is good and quiet, suitable for big group of people as there are plenty of long tables in the restaurant. We spent our afternoon here for lunch and coffee.

Sundown Singapore & 2XU Singapore Marathon

It was last year when I was told that the Sundown Singapore Marathon’s 10th Anniversary special rate and 2XU early birds’ special rate with additional polo T-shirt giveaway for the loyal runners of Year 2015 and 2016. The race dates of both runs were just a week apart. My friend and I were discussing whether to join both of the marathons. However, with the race date so close to each other, I was not confident enough to run both half marathons.

Neither my friend did. Then, we both decided to run for 10KM for Sundown Marathon and 21.1KM for 2XU. We both registered ourselves on the same day and same time.

Since, I have run for Sundown’s half marathon in year 2015 (For the Tribe), so it would be good enough for me to run a 10KM this year just to complete the medal collection. I would not be able to get the Sundown’s finisher T-shirt because it is meant for full marathon.

Meanwhile, for 2XU, it is only those who signed up for 21.1KM half marathon would be entitled for the additional polo T-shirt and that was their highest category for 2XU marathon. Another surprised gift was a pair of sock for those super early birds. I think it was just to lure more people to sign up 2XU than Sundown.

And, I was definitely going for half marathon to get my 2XU’s finisher T-shirt for this year which was in rose gold lining and black in colour. I got my first 2XU’s finisher T-shirt in Year 2015 which was in yellow lining and black in colour.

I came down to Singapore just to run these two marathons and reached Singapore in the evening of Friday, 24th March. I went to the Marina Square with hope that I could collect the race pack for 2XU but I was told the queue was very long and wasting time. Yet, indeed it was and I gave up, then walked to the F1 Pit Building to collect the Sundown’s race pack instead. There was queue too and my side was pretty slow.

There was a lot of bad comments about Sundown’s management for this year event especially on the poor management of the race pack collection. As for myself, the queue was fairly okay because I spent 1.5 hours before my turn. However, I was pretty sad with the medal’s quality when I compared to the Year 2015’s finisher medal.

Let me show you the finisher T-shirt and the medal for the 2XU Marathon Year 2017. I will update my post with a picture of both finisher T-shirt in which I personally prefer the cutting and design of the past year.

People have been asking what my reasons for paying so much and joining the marathons after all, there are many parks in Singapore which are free to run and also there are many park connectors which allow us to run for kilometers without a cost. Not only that, sometimes, you will see people join the Spartan race. Why?

To be honest, I do not have an answer which can tell people, the greatest of joining the marathons. It is some sort of suffer whenever I have to run 10KM or more, I would think that I can just easily give up my run after 5KM or 6KM on my normal running routine. Then, why?

I think my answer will be, the satisfaction of running toward the finishing line while people around are cheering up for you to push harder for the next 10 meters or so. I think it is all about the sportsmanship’s spirit that I can feel when I run toward the finishing line. The shouts from those strangers who have the same aims and goals as us motivate me to push to my max limit to cross the line with my best timing.

Oh well, it is worth to do it when it is just for a short 10 meters distance? Hmm, well I do not have a perfect answer too but it is worth especially when you get extra bonus such as being the top 3 winners, winning prize from lucky draw if any or get to know a new friend who has the same hobby as you. Back in Year 2014 during the Standard Chartered KL Marathon, I met a new friend who run for 10KM too at the 9KM signage board. As an amateur runner, I loved to stop by to take pictures and post to Instagram, this is one of those pictures I took during the run.

And, I do love the feeling after the marathons, especially the time when i organize my medals and think about how to display my hall of fame. Yes, some people may say, medals, trophies and certifications can be custom-made outside but what the fun is? You must go through all the obstacles, sweats and the pain after the marathon before you can understand the greatest of running a marathon.

There is always a lesson to be learned from each run and a beautiful reward awaiting us in front, the sunrise (the beginning of new day).

KyoChon

KyoChon, Malaysia.
Address: SkyAvenue Genting Lot T2-33, Level T2, Sky Avenue, 69000 Genting Highland, Pahang, Malaysia.

Last March, my friends and I went to the Genting Highland for a short trip in the afternoon. It was raining when we reached the Skyway station and the crowd waiting for the cable cars was quite huge. They were mainly the tourists from China and you can imagine how big the groups of people were there. Luckily, the Genting Skyway’s management has provided a separated queue for tour groups and normal visitors to enter the queuing area. However, it did not ease the human traffic and it took quite long to reach our turn.

The ticket of the cable car and the price for a standard cable car for each trip. It is a good money to earn! As we were at the new Skyway Station, the cable cars were new ones, more stable and comfortable with more space.

It was raining on that afternoon. Upon reaching the hilltop, I could feel the cool and needed to put on my sweater. I was having cough over the few weeks. We walked around to see which restaurant was available for us to have lunch.

Our first choice to eat at Madam Kwan but the restaurant did not serve a number of dishes due to shortage of ingredients. I was pretty sadden because I could not try the Nasi Bojari. Then, we walked around again and ended up at this Korean restaurant, KyoChon which my friend said quite nice. We queued a while for our table but it was a high-round table with high seat. I did not like this kind of seat and I did not like to eat facing outside of the restaurant whereby a lot of people walking around the area. Simply unsecured.

Even though we were hungry at that time, it still took us quite long to decide which set to choose from the menu and it took some time to serve the food too. We placed our ordered with a waitress who is from Philippines and she was quite attentive to our questions and orders.

I am not too sure whether we over-ordered our food. It did look a lot of food on our small table. We have three cold drinks (OMG, so cold still drink cold drink with ice), two plates of fried chicken, one with soy sauce and another with honey, some salads and three bowl of rice, kimchi fried rice, garlic fried rice and plain white rice. Yes, now I do feel we over-ordered the food.

Personally, I liked their honey fried chicken which was very well marinated and cooked. It tasted better than the soy sauce. I guess it is because of the additional honey coating on the chicken that makes it tastier and crisper. Let me share with you a closed up picture of both chicken. The top plate is the honey chicken and the bottom one is the soy sauce.

Their Kimchi Fried Rice is nice too with strong Kimchi and spicy taste. It comes with an egg on top of the fried rice. When compared with the Garlic Fried Rice, one may find the latter is normal taste.

Fat Boys Burger

Fat Boys Burger, Orchard Road, Singapore.

I am going to do a throw back of the events happened during the last March. Just a day before the Sundown Singapore Marathon 2017, I met up with two other friends for a casual dinner after work at the Fat Boys Burger which is located at Orchard Road. It is my first time visiting this branch. The first visit was at Upper Thomson Road.

There was no queue when my friend and I walked from Plaza Singapore to this restaurant which is situation along Orchard Road, right behind the bus stand. However, the restaurant was full house and no tables available for three person. We waited quite a while because we did not want to sit at those high chairs at the bar side.

So much waiting time wasted and we quickly ordered our food once we got our seats not far from the restaurant’s entrance after a couple paid their bill and left the place. The waitress is quite friendly and repeated our orders carefully. It took a while more before they served our food. My friend ordered the Fries with Cheese and shared among us. It is a day full of fries and carbohydrates.

I ordered the Royale with Cheese which comes with a beef patty, soft sesame bun and some fries. I took a top view of the burger and then, I recalled the days before I met a sales person who works for F&B merchant portal. It makes me to take another good shot of the burger in which I decided to make it naked.

I took a close up picture of the beef patty with a piece of cheese. This is how the burger looks like. As for the taste and the sauce combination, I felt satisfied and finished the whole burger without complaints.