Joins in DBMS
Join-in DBMS allows you to function, manage, manipulate or control DBMS relations (tables). Join is a binary operation which enables us to merge join products and selection in one single statement.
The objective behind creating a join condition is that it will help the user to merge the records (data) from two or more DBMS relations (tables). It is also used to retrieve or recollect data from multiple tables in a horizontal way.
Purpose of using join in DBMS
Join functions to bring or retrieve data from two or more tables. It is also used for merging fields (columns) from two or more tables by making use of a common set of values to both tables.
Types of joins
 |
| types of joins |
1. ANSI format joins
When we retrieve data from multiple tables with 'on' the keyword join condition is called ANSI joins.
Syntax for ANSI Joins
Select * from <Tablename1> <join key> on <joining condition>;
Inner Join - The INNER JOIN selects all rows from both involved tables as long as conditions are fulfilled . It retrieves data (records) from different tables with the "on" Clause from the condition. It creates a new table by joining all the rows from both tables where the conditions are fulfilled. Means inner join returns only matching data from both the tables.
Syntax-
SELECT column_ name(s) FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Outer Join - Outer join can return the unmatched data of tables. If we want to display the unmatched data from multiple tables then we use outer join.
Outer Joins are three types of
Left Outer Join - In the left outer join operation it gives the matching data from both participating tables and also gives unmatched data from the left side table only.
 |
| Left outer join |
Syntax-
select * FROM table1 LEFT OUTER JOIN table2 ON
table1.column_name=table2.column_name;
Right Outer Join - In the right outer join operation it gives the matching data from both the participating tables as well as unmatched data from the right side table only.
 |
| Right outer join |
select * FROM table1 RIGHT OUTER JOIN table2 ONtable1.column_name=table2.column_name;
Full Outer Join - We can say that it is a union (mixture) of left outer join or right outer join. In full outer join operation it gives the result of matching and also unmatched data from both the tables at a time.
 |
| Full outer join |
Syntax-
select * FROM table1 FULL OUTER JOIN table2 ON
table1.column_name=table2.column_name;
Cross Join - If we want to join two tables without using any condition, it is considered a cross join. Cross join method joins each row of table one with the each row of table two.
Syntax-
SELECT table1.column1,table2.column2...
FROM table1,table2[,table3];
Natural Join - In natural join Operation when we use natural join we should have a common column name in tables. It also retrieves matching data from the tables only. It is similar to equi-join but avoids duplicate column names from the result.
Syntax-
SELECT *
FROM table1
NATURAL JOIN table2;
2. Non -ANSI Format Joins
When we retrieve data from multiple tables based on "where" Clause condition is called as Non-ANSI format joins.
Syntax for Non-ANSI Joins
Select * from <Tablename1><Tablename2> where <joining condition>;
Equi Join - In equi join we can retrieve data from multiple tables based on the Equal operator (=). If we want to use equi-join we must have a common column in tables and those columns' data type must match. Equi join always gives matching data from tables as a result. In equi-join, which column is a common column in multiple tables, that column should be used in joining condition.
Syntax-
SELECT *
FROM table1
JOIN table2
[ON (join _ condition)]
Non - Equi Join - In non-Equi join we can retrieve data from multiple tables based on any condition except an equal (=) condition. We can use different operator's but not equal operators.
Syntax-
SELECT *
FROM table_name1, table_name2
WHERE table_name1.column [> | < | >= | <= ] table_name2.column;
Self Join - Self join can be implemented only on a single table. If we want to use self join we require Alias names, otherwise we are not able to use self join. And we can say that joining a table by itself is known as self join.
Syntax-
SELECT column_ name(s)
FROM table1 T1, table1 T2
WHERE condition;