Join in SQl

SQL Join is used to get data from two or more tables, and show record in single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. Join Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables. A table can also join to itself known as, Self Join.

 

Below is types of join in SQL.

  • Inner
  • Outer
  • Left
  • Right

Inner JOIN

Inner Join is a simple JOIN in which the result is based on matched data as per the equality condition specified in the query.

Inner Join Syntax is,

SELECT column-name-list
from table-name1 
INNER JOIN 
table-name2
WHERE table-name1.column-name = table-name2.column-name;

Inner JOIN query will be,

SELECT * from User, UserType where User.Id= UserType.UserId;

Left Outer Join

The left outer join returns a result table with the matched data of two tables then remaining rows of the left table and null for the right table’s column.

Left Outer Join syntax is,

SELECT column-name-list
from table-name1 
LEFT OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

 

 

Left Outer Join query will be,

SELECT * FROM User LEFT OUTER JOIN UserType ON (User.Id=UserType.UserId);

Right Outer Join

The right outer join returns a result table with the matched data of two tables then remaining rows of the right table and null for the left table’s column.

RIGHT Outer Join syntax is,

SELECT column-name-list
from table-name1 
RIGHT OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

RIGHT Outer Join query will be,

SELECT * FROM User RIGHT OUTER JOIN UserType ON (User.Id=UserType.UserId);

Full Outer Join

Full outer join returns a result table with the matched data of two table then remaining rows of both left table and then the right table.

Full Outer Join Syntax is,

select column-name-list
from table-name1 
FULL OUTER JOIN 
table-name2
on table-name1.column-name = table-name2.column-name;

Full Outer Join query will be like,

SELECT * FROM User FULL OUTER JOIN UserType on (User.Id=UserType.UserId);

Leave a Reply

Your email address will not be published. Required fields are marked *