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

Most Popular Programming Languages in 2020

Most Popular Programming Languages in 2020 In this blog post, you will learn about the most popular programming languages in 2020 for creating the best web applications. Check its pros and cons. Analyzed by technostacks Not very long ago, just a few people were considered to be computer programmers, and the general public viewed them with awe. In this digital age that we are now living in, however, a large number of IT jobs need a solid grasp of one or more programming languages. Whether one wants to develop a mobile app or get a certification for having programming knowledge, or even to learn new skills, one needs to opt for the right programming language. Below mentioned eight most popular programming languages which are in demand for software development and web applications. This is the most used programming languages in 2019 and will be in 2020. For each, there is little information about the language, benefits and its complexity, as well as about its usage. One must...
1 comment
Read more

HashTable in C# with Example

  HashTable in C# with Example Hashtable  is used to store a collection of key/value pairs of different  data types  and are organized based on the hash code of the key.   Generally, the hashtable object will contain buckets to store elements of the collection. The bucket here, is a virtual subgroup of elements within the hashtable and each bucket is associated with a hash code, which is generated based on the key of an element.   In C#, hashtable is same as a  dictionary  object but the only difference is that the  dictionary  object is used to store a key-value pair of same  data type  elements.   When compared with  dictionary  object, the hashtable will provide a lower performance because the hashtable elements are of object type so the boxing and unboxing process will occur when we are storing or retrieving values from the hashtable.   C# HashTable Declaration Hashtable is a non-generic type...
Post a Comment
Read more