SQL SERVER – Introduction to JOINs – Basic of JOINs


INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.
LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*FROM Table1 t1WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)GO
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.
/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID t2.IDWHERE t2.ID IS NULL
The above example can also be created using Right Outer Join.
NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.
USE AdventureWorks
GO
CREATE TABLE table1(ID INTValue VARCHAR(10))INSERT INTO Table1 (IDValue)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 4,'Fourth'UNION ALLSELECT 5,'Fifth'GOCREATE TABLE table2(ID INTValue VARCHAR(10))INSERT INTO Table2 (IDValue)SELECT 1,'First'UNION ALLSELECT 2,'Second'UNION ALLSELECT 3,'Third'UNION ALLSELECT 6,'Sixth'UNION ALLSELECT 7,'Seventh'UNION ALLSELECT 8,'Eighth'GOSELECT *FROM Table1SELECT *FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */SELECT t1.*,t2.*FROM Table1 t1INNER JOIN Table2 t2 ON t1.ID t2.ID
GO
/* LEFT JOIN */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID t2.ID
GO
/* RIGHT JOIN */SELECT t1.*,t2.*FROM Table1 t1RIGHT JOIN Table2 t2 ON t1.ID t2.ID
GO
/* OUTER JOIN */SELECT t1.*,t2.*FROM Table1 t1FULL OUTER JOIN Table2 t2 ON t1.ID t2.ID
GO
/* LEFT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1LEFT JOIN Table2 t2 ON t1.ID t2.IDWHERE t2.ID IS NULLGO/* RIGHT JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1RIGHT JOIN Table2 t2 ON t1.ID t2.IDWHERE t1.ID IS NULLGO/* OUTER JOIN - WHERE NULL */SELECT t1.*,t2.*FROM Table1 t1FULL OUTER JOIN Table2 t2 ON t1.ID t2.IDWHERE t1.ID IS NULL OR t2.ID IS NULLGO/* CROSS JOIN */SELECT t1.*,t2.*FROM Table1 t1CROSS JOIN Table2 t2
GO
DROP TABLE table1DROP TABLE table2
GO

HOT KEYWORDS IN .NET

.net Language Integrated Query .NET4 3 Layer Architecture 3 Tier 3 tier architecture Add update delete Aggregate in LINQ Aggregate Operators Aggregation An Introduction to LINQ ASP.Net GridView CASE statements CatchingCatching Parsing Exceptions CompiledQuery Complied Queries Complied Queries LINQ Concurrency conflicts in LINQConnection string Create XML XLinq data binding LINQ Data Classes Data Navigation databinding in LINQ DataContexDataTable LINQ Deferred Execution Deferred Execution Immediate Execution LINQ Delete Dlinq DLINQ QUERYDynamic Linq Entity Sets Event Bubbling exact match Filtering Lists from where select Functional Construction Generic Collection h LINQ how to Execute SQL Query using LINQ to SQL Immediate Execution LINQ in between Insert Insert data into database IQueryable isual Studio 2010 Javascript and Linq Menus Language Integrated Query legacy collection LINQ LINQ Object Linq Aggregate Operators LINQ FAQ LINQ in .NET Linq Menus LINQ Query Syntaxlinq to dataset LINQ to DataTable linq to entities LINQ to Object linq to objects LINQ to SQL Class Linq to SQL Performance LINQ to XML LinqDataSource LinqDataSource control LNIQ load XML Xlinq Logging in LINQ Logging in LINQ to SQL Parallel Computing Parallel Library Parallel LINQ Parameterized Query popupextender query data read XML read XML in Silverlight Select and SelectMany SQL Class SQL Query using LINQ to SQL sql server compact relational database system linq enterprise application data access layer binding Typed Lists UDF SQL SERVER FUNCTION LINQ ENTITY CLASS Update Using LINQ in VS2005 Using Stored Procedure LINQ var keyword Var versus IEnumerable LINQ WCF Data Service what is LINQ Where Where LINQ write XML XAttribute XElement XElement classxLinq XML tree

LINQ in C#


Introduction to LINQ Queries (C#)
Describes the three parts of the basic LINQ query operation that are common across all languages and data sources.
LINQ and Generic Types (C#)
Provides a brief introduction to generic types as they are used in LINQ.
Basic LINQ Query Operations (C#)
Describes the most common types of query operations and how they are expressed in Visual Basic and C#.
Data Transformations with LINQ (C#)
Describes the various ways that you can transform data retrieved in queries.
Type Relationships in LINQ Query Operations (C#)
Describes how types are preserved and/or transformed in the three parts of a LINQ query operation
LINQ Query Syntax versus Method Syntax (C#)
Compares method syntax and query syntax as two ways to express a LINQ query.
C# Features That Support LINQ
Describes the language constructs added in C# 3.0 that support LINQ.
Walkthrough: Writing Queries in C# (LINQ)
Step-by-step instructions for creating a C# LINQ project, adding a simple data source, and performing some basic query operations.

What's New in ASP.NET MVC 4

ASP.NET MVC 4 puts its focus on making it easier to develop mobile web applications. In this hands-on lab, you will start with the MVC 4 Internet Application project template and create a Photo Gallery application. You will progressively enhance the application using jQuery Mobile together with MVC 4 new features, to make it work great across different mobile devices and desktop web browsers. You will also learn about new code recipes for code generation and how MVC 4 makes it easier to write asynchronous action methods by supporting ActionResult return types.


Overview

ASP.NET MVC 4 is a framework for building scalable, standards-based web applications using well-established design patterns and the power of the ASP.NET and the .NET framework. This new, fourth version of the framework focuses on making mobile web application development easier.
To begin with, when you create a new MVC 4 project there is now a mobile application project template you can use to build a standalone app specifically for mobile devices. Additionally, MVC 4 integrates with jQuery Mobile through a jQuery.Mobile.MVC NuGet package. jQuery Mobile is an HTML5-based framework for developing web apps that are compatible with all popular mobile device platforms, including Windows Phone, iPhone, Android and so on. However, if you need specialization, MVC 4 also enables you to serve different views for different devices and provide device-specific optimizations.
In this hands-on lab, you will start with the MVC 4 “Internet Application” project template to create a Photo Gallery application. You will progressively enhance the app using jQuery Mobile and MVC 4’s new features to make it compatible with different mobile devices and desktop web browsers. You will also learn about new code recipes for code generation and how MVC 4 makes it easier for you to write asynchronous action methods by supporting Task<ActionResult> return types.

ASP.NET Web API


ASP.NET MVC 4 includes ASP.NET Web API, a new framework for creating HTTP services that can reach a broad range of clients including browsers and mobile devices. ASP.NET Web API is also an ideal platform for building RESTful services.
ASP.NET Web API includes support for the following features:
  • Modern HTTP programming model: Directly access and manipulate HTTP requests and responses in your Web APIs using a new, strongly typed HTTP object model. The same programming model and HTTP pipeline is symmetrically available on the client through the newHttpClient type.
  • Full support for routes: ASP.NET Web API supports the full set of route capabilities of ASP.NET Routing, including route parameters and constraints. Additionally, use simple conventions to map actions to HTTP methods.
  • Content negotiation: The client and server can work together to determine the right format for data being returned from a web API. ASP.NET Web API provides default support for XML, JSON, and Form URL-encoded formats and you can extend this support by adding your own formatters, or even replace the default content negotiation strategy.
  • Model binding and validation: Model binders provide an easy way to extract data from various parts of an HTTP request and convert those message parts into .NET objects which can be used by the Web API actions. Validation is also performed on action parameters based on data annotations.
  • Filters: ASP.NET Web API supports filters including well-known filters such as the [Authorize]attribute. You can author and plug in your own filters for actions, authorization and exception handling.
  • Query composition: Use the [Queryable] filter attribute on an action that returns IQueryable to enable support for querying your web API via the OData query conventions.
  • Improved testability: Rather than setting HTTP details in static context objects, web API actions work with instances of HttpRequestMessage and HttpResponseMessage. Create a unit test project along with your Web API project to get started quickly writing unit tests for your Web API functionality.
  • Code-based configuration: ASP.NET Web API configuration is accomplished solely through code, leaving your config files clean. Use the provide service locator pattern to configure extensibility points.
  • Improved support for Inversion of Control (IoC) containers: ASP.NET Web API provides great support for IoC containers through an improved dependency resolver abstraction
  • Self-host: Web APIs can be hosted in your own process in addition to IIS while still using the full power of routes and other features of Web API.
  • Create custom help and test pages: You now can easily build custom help and test pages for your web APIs by using the new IApiExplorer service to get a complete runtime description of your web APIs.
  • Monitoring and diagnostics: ASP.NET Web API now provides light weight tracing infrastructure that makes it easy to integrate with existing logging solutions such as System.Diagnostics, ETW and third party logging frameworks. You can enable tracing by providing an ITraceWriterimplementation and adding it to your web API configuration.
  • Link generation: Use the ASP.NET Web API UrlHelper to generate links to related resources in the same application.
  • Web API project template: Select the new Web API project form the New MVC 4 Project wizard to quickly get up and running with ASP.NET Web API.
  • Scaffolding: Use the Add Controller dialog to quickly scaffold a web API controller based on an Entity Framework based model type.

About jQuery Mobile

With jQuery Mobile you can create a website that has the look and feel of a native mobile app. My favorite feature of the jQuery Mobile framework is the ajax-driven page navigation. After navigating through a few pages on a jQuery Mobile website view the page’s generated source and you will see that all subsequent pages have been injected to the homepage’s DOM and the navigation is based on hash values. This allows for smooth transitions between pages that have already been loaded. Read more about jQuery Mobile here: http://jquerymobile.com/

Search This Blog

Arsip Blog

Powered by Blogger.

Recent

Comment

Author Info

Like This Theme

Popular Posts

Video Of Day

Sponsor

Most Popular