Post
Ask Question

Concept of Join in SQL Server

Introduction

In this article, we show various types of joins in SQL Server. A Join in SQL is used to combine two or more tables. We use the Join operator to gather the data in a single query.

Joins in SQL 

SQL, Joins are used to get the data of two or more tables based on a relationship. In most cases of joining, we will use a primary key in the first table and a foreign key in the second. By using joins, you can retrieve data from two or more tables based on logical relationships between the tables.

A Join condition defines a way two tables are related in a query by

  • Specifying the column to bejoind for the Join from each table. In joining foreign keys in a table and their associated key in the other table.
  • To use the logical operator in comparing values from the columns.

There are many types of Joins. They are given below.

1. Outer Join in SQL Server

There are three types of Outer join in SQL Servejoineft outer Join

  • Right Outer Join
  • Full Outer Join

2. Inner Join in SQL Server

These are two types.

  • Natural Join
  • Cross Join

Left Outer Join

In this, there are two tables. We use a Left Outer Join operator on table A and table B, and it takes all records of the left table (table A) and the matching values from the right table (table B). When there is no matching value for a row,, the Null value is used in each column.

Syntax 

SELECT 
  *   
FROM 
  Table A 
  LEFT OUTER JOIN Table B ON (condition);

Example

Before joining, we have two tables, Employee and Department table.

Employee Table

emp1.gif

Department Table

dep2.gif

left3.gif

Right Outer Join

This join returns all the rows from the right table that match the left table. If there are no matching values in the left table, NULL values are returned for each column. It is the reverse of the Left Outer Join.

Syntax 

SELECT 
  *   
FROM 
  Table A 
  RIGHT OUTER JOIN Table B ON (condition);

right4.gif

Full OujoinJoin

This join combines tjoineft outer join and thjoinght outer join. In this concept, the Full Outer Join joins both tables. Then the resultant table contains a null value for every column that lacks the matching row.

Syntax  

SELECT 
  *   
FROM 
  Table A FULL 
  OUTER JOIN Table B ON (condition);

full5.gif

Inner Join

In the concept of Inner join,, we take the tables, Table A and Table B. First, it finds all possible pairs between table A and table B. Matching rows between the tables specified in the INNER JOIN statement based on one or more columns having matchjoindata. The join that displays only the rows that have a match in both the joined tables is known as an inner join. For this, it finds the Cartesian product of this table. Then it implements the given condition on it. Find the desired output or result.

Syntax 

SELECT 
  * 
FROM 
  Table A inner  
  JOIN Table B ON (condition);

inner6.gif

Resources


Similar Articles