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 Views

Views in ASP.NET MVC Application explained Find a related article By  Steve Smith  and  Luke Latham from Microsoft Corporation here In the Model-View-Controller (MVC) pattern, the  view  handles the application's data presentation and user interaction. A view is an HTML template with embedded  Razor markup . Razor markup is code that interacts with HTML markup to produce a webpage that's sent to the client. In ASP.NET MVC, views are  .cshtml  files that use the  C# programming language  in Razor markup. Usually, view files are grouped into folders named for each of the application's  controllers . The folders are stored in a  Views  folder at the root of the application as shown: The  Home  controller is represented by a  Home  folder inside the  Views  folder.  The  Home  folder contains the views for the  About ,  Contact , and  Index...
Post a 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