SQL Join | Inner | Left | Right | Outer | Self | Cross Join 2019 — Sagar Jaybhay

Join statement is used to combine data or rows from more than one table which is based on a common field between them. In general 2 tables are related to each other by using foreign key constrains.

There are different types of join In this Outer Join is divided into 3 sub joins.

1. Left Join or left outer join
2. Right Outer Join or Right Join
3. Full Join or Full Outer Join.

See below image we use this 2 tables for our join examples. In that 2 tables we have departmentid is common field or column.

Inner Join

Inner Join will return only matching rows from 2 tables where a condition is matching and unmatched rows simply eliminated. Returns records that have matching values in both tables. Matching rows between 2 tables are only selected.

General Syntax

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

Actual Query

select * from Employee inner join Department on Employee.DepartmentID=Department.DepartmentID;

The result fetches by this query are only matched department id if the id is not present in the department table or vice versa then these rows are not populating in the result of the inner join query.

The ultimate meaning of the inner join is only given a matching row between these 2 tables.

Left outer Join or Left Join

The left join returns all the matching rows + nonmatching rowsfrom the left table. In this the rows for which there is no matching row on theright side, the resultset will contain null. Left join also called a left outerjoin.

In nutshell, we get a complete left table and only matching row from the right table.

General Syntax of a left join:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;

Actual Query

select * from Employee as e left join Department on e.DepartmentID=Department.DepartmentID;

Right Outer Join Or Right Join:

The right join returns all the matching rows + non-matching rows from the right table

General Syntax:

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

Right join also called as right outer join. The rows for which there is no matching row on the left side, the result-set will contain null.

Actual Query

select * from Employee as e right join Department on e.DepartmentID=Department.DepartmentID order by Department.DepartmentID desc;

Full Outer Join or Full Join

Full join returns all rows from both left and right tables and it includes non-matching rows also. Full join create a result set by combining both left and right to join. The rows for which no matching rows found it will give null values.

General Syntax

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

Actual Query

select * from Employee as e full join Department on e.DepartmentID=Department.DepartmentID order by Department.DepartmentID desc;

Cross Join

It produces the Cartesian product of 2 tables which is involved inthe join. In the above examples, we have Employee table and Department table inEmployee table we have 1001 rows and in Department table, we have 11 rows thenthe output of this join contains

11,011 rows.

In this, if you use on syntax means condition it will throw an error.

Self Join

join means a table join to itself and this is called self-join. It is useful for querying hierarchical data orcomparing rows within the same table. A self-join is not any different type of join. Most of the time when thetable has a FOREIGN KEY which references its own PRIMARY KEY.

A self-join may use an inner joinor left join. When you write a query for self-join at that time remember towrite alias for table else it will throw an error.

Create table statement for creating a table.

create table selfjoin (empid int,name varchar(300),managerid int)

after creating a table we can add rows to this table

insert into selfjoin values (1,'sagar',3); insert into selfjoin values (2,'ram',1); insert into selfjoin values (3,'ravi',3); insert into selfjoin values (4,'sham',1); insert into selfjoin values (5,'naga',6); insert into selfjoin values (6,'saga',5);

General Syntax

SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;

In our example, we need to find out who is a whose manager.

Actual Query

select a.name,b.name from selfjoin as a inner join selfjoin as b on a.managerid=b.empid

Originally published at https://sagarjaybhay.com on December 9, 2019.