Skip to main content

SQL JOIN EXPLAINED

SQL JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. SQL Server has 4 types of joins:

 INNER JOIN/simple join
LEFT OUTER JOIN/LEFT JOIN
RIGHT OUTER JOIN/RIGHT JOIN
FULL OUTER JOIN

SELECT Columns
FROM table1
INNER JOIN table2
ON table1.column=table2.column

We will use the two tables we created in our previous post, which can be found here to demonstrate; Employees and Departments tables

Employees Table
Departments Table
INNER JOIN


This type of JOIN returns rows from all tables in which the join condition is true. It takes the following syntax:

Example
--INNER JOIN
SELECT e.Id,e.FirrstName,e.MiddleName,e.LastName,e.Address,e.PhoneNumber,e.DateOfBirth,d.DeptName
FROM employees e
INNER JOIN departments d
ON e.DepartmentId=d.Id

Result

LEFT OUTER JOIN

This type of join will return all rows from the left-hand table plus records in the right-hand table with matching values.

Example
-- LEFT OUTER JOIN
SELECT e.Id,e.FirrstName,e.MiddleName,e.LastName,e.Address,e.PhoneNumber,e.DateOfBirth,d.DeptName
FROM employees e
LEFT OUTER JOIN departments d
ON e.DepartmentId=d.Id

Result
The records without matching values are replaced with NULLs in the respective columns as shown above.

RIGHT OUTER JOIN


This type of join returns all rows from the right-hand table and only those with matching values in the left-hand table.

Example
--RIGHT OUTER JOIN
SELECT e.Id,e.FirrstName,e.MiddleName,e.LastName,e.Address,e.PhoneNumber,e.DateOfBirth,d.DeptName
FROM employees e
RIGHT OUTER JOIN departments d
ON e.DepartmentId=d.Id

Result
The records without matching values are replaced with NULLs in the respective columns.

FULL OUTER JOIN

This type of join returns all rows from both tables with NULL values where the JOIN condition is not true.

Example
--FULL OUTER JOIN
SELECT e.Id,e.FirrstName,e.MiddleName,e.LastName,e.Address,e.PhoneNumber,e.DateOfBirth,d.DeptName
FROM employees e
FULL OUTER JOIN departments d
ON e.DepartmentId=d.Id


Result

The records without matching values are replaced with NULLs in the respective columns.
Labels:
Location: Abuja, Nigeria

Comments

Post a Comment

Popular posts from this blog

Classes in C# Explained

C# Class Explained A class is nothing but an encapsulation of properties and methods that are used to represent a real-time entity, as explained by Guru99 . For instance, if you want to work with Guest’s data as in our previous DataDriven Web application . The properties of the Guest would be the Id, GuestName, Address, Phone number etc of the Guest. The methods would include the entry and modification of Guest data. All of these operations can be represented as a class in C# as shown below. using System; namespace CsharpnaijaClassTutorial {     public class Guest     {         public int Id { get ; set ; }         public string GuestName { get ; set ; }         public string Address { get ; set ; }         public string WhomToSee { get ; set ; }     ...
1 comment
Read more

ASP.NET MVC Routing

ASP.NET MVC Routing ASP.NET MVC routing is a pattern matching system that is responsible for mapping incoming browser requests to specified MVC controller actions. When the ASP.NET MVC application launches then the application registers one or more patterns with the framework's route table to tell the routing engine what to do with any requests that matches those patterns. When the routing engine receives a request at runtime, it matches that request's URL against the URL patterns registered with it and gives the response according to a pattern match. Routing pattern is as follows A URL is requested from a browser, the URL is parsed (that is, break into controller and action), the parsed URL is compared to registered route pattern in the framework’s route table, if a route is found, its process and send response to the browser with the required response, otherwise, the HTTP 404 error is send to the browser. Route Properties ASP.NET MVC routes are res...
Post a Comment
Read more

Role-Based Authorization in ASP.NET MVC

Role-Based Authorization Explained The most challenge aspect of any web application is implementing its security. In traditional web development with ASP.NET (from version 2.0 onwards), we have been using Membership and Role providers. These providers allows us to define Roles, Users and assign roles to users which helps us to manage Authorization. But with an increase in social networking and global authentication providers, we needed an upgraded membership system. ASP.NET Identity is the new membership system for building ASP.NET web applications, phone, store, or hybrid applications using social identities for authentication and authorization. So we can now use Windows Live (e.g. Hotmail), Gmail, Facebook and Twitter for authentication before the user starts using our web application. For internal application, we need to create users and roles for providing users access to creating items, products or managing other users. Necessary references are provided by MVC 5 applicatio...
1 comment
Read more