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

Collections in C#

Collections in C# In our previous article , we have learned about how we can use arrays in C#. Arrays in programming are used to group a set of related objects. So one could create an array or a set of Integers, which could be accessed via one variable name. What is Collections in C#? Collections are similar to Arrays, it provides a more flexible way of working with a group of objects. In arrays, you would have noticed that you need to define the number of elements in an array beforehand. This had to be done when the array was declared. But in a collection, you don't need to define the size of the collection beforehand. You can add elements or even remove elements from the collection at any point of time. This article will focus on how we can work with the different collections available in C#. There are three distinct collection types in C#: standard generic concurrent The standard collections are found under the System.Collections. They do not store elemen...
Post a Comment
Read more

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

System.IO Namesapce in C#

  System.IO Namesapce in C# A  file  is a collection of data stored in a disk with a specific name and a directory path. When a file is opened for reading or writing, it becomes a  stream . The stream is basically the sequence of bytes passing through the communication path. There are two main streams: the  input stream  and the  output stream . The  input stream  is used for reading data from file (read operation) and the  output stream  is used for writing into the file (write operation). From the above definition of file, the C# provides a namespace that enable us to manipulate file in C# called System.IO.   System.IO  is a  namespace  and it contains a standard IO (input/output) types such as classes , structures , enumerations , and  delegates  to perform a read/write operations on different sources like file, memory, network, etc.   System.IO Classes The table below shows differen...
Post a Comment
Read more