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

The String.Join Method in C# Explained

The String.Join Method in C#   The string.Join concatenates the elements of a specified array or the members of a collection, using the specified separator between each element or member. Overloads of string.Join Method Description Join(Char, Object[]) Concatenates the string representations of an array of objects, using the specified separator between each member. Join(Char, String[]) Concatenates an array of strings, using the specified separator between each member. Join(String, IEnumerable<String>) Concatenates the members of a constructed IEnumerable<T> collection of type String, using the specified separator between each member. Join(String, Object[]) Concatenates the elements of an object array, using the specified separator between each element. Join(String, String[]) Concatenates all the elements of a string array, usi...
Post a Comment
Read more

Models in ASP.NET MVC

Models in ASP.NET MVC Explained A model is a class that contains the business logic of your application. It also used for accessing data from the database. The model class does not handle directly input from the browser. In MVC, it is the Controller that handle input from the browser directly and process the request by receiving data from the model and pass it back to view as response. It does not contain any HTML code either. It is a best practice but not mandatory for developers to not have any communications with the view directly, models should only contain a POCO ( Plain Old CLR Objects ) classes. All processing logic and communication with the view should be handled by another layer called Viewmodels. Models are also refers as objects that are used to implement conceptual logic for the application. A controller interacts with the model, access the data, perform the logic and pass that data to the view. Note that it is not mandatory, but it is a good programming...
Post a Comment
Read more

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