SQL JOINS

Joins in SQL are used to get related data from different tables. SQL joins are very useful feature in relational database while writing logical queries. There are different types of joins in SQL. They are

• Inner Join (Join)
• Outer Join
• Cross Join
• Self Join

Inner Join (Join)

Inner Join is used to retrieve only the matching records from both tables. The un-matching records will be omitted from result. The Join in SQL without any keywords like Inner, Outer, Left, Right or Cross is considered as Inner Join.

Outer Join

It retrieves either all rows from one table and only matching rows from other table or all rows from all tables. Doesn’t bother about matching. There are three kinds of outer join. They are.

Left Outer Join or Left Join

When we want all the rows from left table and only matching rows from right table, then we can use Left Outer or Left Join. If there is no matching records, it will return null values.

Right Outer Join or Right Join

When we need to fetch all the records from right table and only matching rows from left table, then we have to use Right outer or Right Join. If there is no matching rows in left table, then it will return null values.

Full Outer Join or Full Join

This combines both Left Outer join and Right Outer joins together. It returns records from both table when condition matches. If no match, then will show null. Concluding Outer Join, it can be said as it will return complete records from any of Left table or Right table and only matching from other table or from both(FULL) tables as matching together and not matching as null.

Cross Join

It is the Cartesian product of tables involved in join. Which means, each records from both tables will be combined to return result. This It is one of the least used join.

Self Join

Joining a table with itself is called Self Join It is mainly used with a table having hierarchy structure data in it like Employee Table with Manager_Id. Here Manager is also an employee and other employee will have a higher employee as manager. So in that situation, to get a manager of an employee, we need to join the same table with itself to get employee and manager. first table will act as employee table and second joining will act as Manager table.

Joins in SQL Based on Operators

Based on the operator used for joining tables, it can be classified into two types.

Equi Join

If we use only the = in join clause, then it is called Equi Join

Theta Join

When we use operators like >, <, >=, <= is known as Theta Join