Databases are usually comprised of several tables that are related to one another in some way. You need to pull the information from more than one table at a time so that you get the desired result.

Joins are used to extract data from more than one table at a time and produce the information as a single result set. Join is used to link or connect tables on a common column and return the record that match in those columns.

In SQL joins are used to get data from two or more tables based on relationship between some of the columns in tables. In most of the cases we will use primary key of first table and foreign key of secondary table to get data from tables by using this relationship we can reduce the duplication of data in every table.

Before enter into Joins concept first design two tables in database and enter data like as shown below

Give the table name as CustomerTable. Here CUSTID is the primary key.

Give the table name as ProductTable. Here PRODUCTID is the primary key and CUSTID is the foreign key. 

Types of Joins

•      Inner Join
•      Outer Join
•      Self Join 

Inner Join

The join that displays only the rows that have a match in both the joined tables is known as inner join.

This is default join in the query and view designer. 

Syntax:

SELECT T1.ColumnName, T2.ColumnName
FROM TableName1 T1
INNER JOIN TableName2 T2
ON T1.ColumnName=T2.ColumnName

 Example

SELECT C.CUSTNAME,C.ADDRESS,P.PRODUCTNAME,P.PRICE
FROM CustomerTable C
INNER JOIN ProductTable P
ON C.CUSTID=P.CUSTID

Output

Types of Inner Join

•     Equi Join
•     Natural Join
•     Cross Join

Equi Join

In an equi join, column values are compared for equality and even the duplicate columns are displayed.

Syntax:

SELECT * FROM TableName1 T1
Join TableName2 T2
ON T1.ColumnName=T2.ColumnName

 Example

SELECT * FROM CustomerTable C
Join ProductTable P
ON C.CUSTID=P.CUSTID

Output

 Natural Join

In natural join, the duplicate columns are not there. Thus, when you join the two tables, you can select all the fields from one table and specify the fields that you want from the other table.

Syntax:

SELECT * FROM TableName1

NATURAL JOIN TableName2

Example

SELECT * FROM CustomerTable 
NATURAL Join ProductTable

Natural Joins won’t work in SQL Server(only supports in Oracle) it will throw a syntax error. 

Cross Join

A cross join produces the Cartesian product of the tables those involved in the join. The size of the Cartesian product is the number of the rows in the first table multiplied by the number of the rows in the second table.

Syntax:

SELECT * FROM TableName1
CROSS JOIN TableName2

 Example

SELECT * FROM CustomerTable 
CROSS JOIN ProductTable

Output 

  Outer Join

Outer joins restrict rows from one table while allowing all rows from the second table as a result set.

There are three types of outer joins:

•       Left Outer Join
•       Right Outer Join
•       Full Outer Join

Left Outer Join

It includes all the rows from the first table and only the matching rows from the second table.

Syntax:
SELECT ColumnName FROM TableName1 T1
LEFT OUTER JOIN TableName2 T2
ON T1.ColumnName=T2.ColumnName

 Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME FROM CustomerTable C
LEFT OUTER JOIN ProductTable P
ON C.CUSTID=P.CUSTID

Output 

Right Outer Join

It includes all the rows from the second table and only the matching rows from the table.

Syntax:

SELECT ColumnName FROM TableName1 T1
RIGHT OUTER JOIN TableName2 T2
ON T1.ColumnName=T2.ColumnName

 Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE FROM CustomerTable C
RIGHT OUTER JOIN ProductTable P
ON C.CUSTID=P.CUSTID
  

Output 

 Full Outer Join

It includes all the rows, matching as well as non-matching. 

Syntax:

SELECT ColumnName FROM TableName1 T1
FULL OUTER JOIN TableName2 T2
ON T1.ColumnName=T2.ColumnName

 Example

SELECT C.CUSTID,C.CUSTNAME,P.PRODUCTNAME,P.PRICE 
FROM CustomerTable C
FULL OUTER JOIN ProductTable P
ON C.CUSTID=P.CUSTID

 

Output 

 Self Join

Self join correlates rows of a table with the other rows of the same table. It is used when a table has to be joined to itself to produce results. 

Example

First you have to create a table EmpTable and enter the details like this:

SELECT E2.EMPNAME,E1.EMPNAME as'MANAGER'
FROM EmpTable E1
INNERJOIN EmpTable E2
ON E1.EMPID=E2.EMPMGRID

Output