SQL JOIN's revisited.

-
Yesterday I was asked what's the difference between UNION and UNION ALL, it made me think, I've been writing Stored Procedures for SQL Server for quite some time now. But almost always forget what what the difference between the two. It's time to reinforce it by writing it down through this blog.


UNION is used to combine result-sets of two or more SELECT statements,  the difference is really simple in UNION it only joins distinct values, meaning if there are duplicates it wont show it from the result-set, that's where ALL keyword is used when you want to include the duplicates in return.

Union Syntax:

SELECT ProductId
FROM Products
UNION
SELECT  ProductId
FROM Orders

 This will return unique ProductId from Products and Orders Table combined and will ignore duplicates.

Up Next differences between INNER JOIN, OUTER JOIN, LEFT and RIGHT JOIN and let's not forget CROSS JOIN

CROSS JOIN
Using cross join multiplies your rows from the first table to the rows of the second table, which produces a Cartesian product

 INNER JOIN
Also known as the default join, simply joins both table as long as there's a match between two tables rows.

LEFT JOIN
Returns all rows in the left table with matching rows on the right table, it return null on the rows with no matching values from the right table.

SELECT p.ProductId, p.ProductName, o.Qty
FROM Products p
LEFT JOIN Orders o ON p.ProductId = o.ProductId

Here Products (Left table) that are not in Orders(Right table) will simply have a null value.

RIGHT JOIN
simply the opposite or Left Join,  need I say more?

Let's include the Having keyword its usually used to with grouping result-set and Having adds a condition to the grouping. without the Group By clause it acts similar to a WHERE clause.

Comments

Post a Comment

Popular posts from this blog

Serializing JSON string to ExpandoObject

-
In some case you want to create and Object from JSON, a great way of doing this to deserialize this JSON to an ExpandoObject. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 /// <summary> /// Convert Json String to an Expando Object /// </summary> /// <param name="that">Json string to convert</param> /// <returns>Expando Object</returns> public static ExpandoObject ToExpando ( this string that) { ExpandoObject result; try { result = JsonConvert.DeserializeObject<ExpandoObject>(that, new ExpandoObjectConverter()); } catch (Exception e) { throw e; } return result; } Here we use JsonConvert.DeserializeObject, you need to add reference to NewtonSoft.Json see below. 1 2 3 using Newtonsoft.Json ; using Newtonsoft.Json.Converters ; using Newtonsoft.Json.Linq ;
Read more

XML to ExpandoObject using Recursion in C#

-
Here's how to convert XML to an ExpandoObject using recursion. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 public static dynamic ToExpando ( this XDocument document) { return CreateExpando (document.Root); } private static dynamic CreateExpando (XElement element) { var result = new ExpandoObject() as IDictionary< string , object >; if (element.Elements().Any(x => x.HasElements)) { var list = new List<ExpandoObject>(); result.Add(element.Name.ToString(), list); foreach ( var childElement in element.Elements()) { list.Add(CreateExpando(childElement)); } } else { foreach ( var leafElement in element.Elements()) { result.Add(leafElement.Name.ToString(), leafElement.Value); } } return result; } Happy Coding!
Read more

Automatically Discover and Assign Parameter with Values to a Stored Procedure Call in C#

-
I'm sure most programmers have run across a situation wherein they have to pass  a lot of parameters to a stored procedure call in C#. Instead of manually typing each parameter in and assigning a value, why not develop a way to automatically discover the parameter, add it to the parameter list of the command object and assign the corresponding value from an entity or model. Here's my implementation using extension method. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 /// <summary> /// Automatically assigns parameters to Command.Parameter Collection from List of parameter names /// </summary> /// <param name="that">Command object being extended</param> /// <param name="model">Expando model</param> /// <param name="conn">sQLConnection used to g...
Read more