SQL Join | Inner | Left | Right | Outer | Self | Cross Join 2019

SQL Join By 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

  1. Inner Join
  2. Outer Join
  3. Cross Join

SQL Join
SQL 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.

Tables to Refer Join
Tables to Refer Join

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.

Inner Join in SQL
Inner Join In SQL

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

Inner Join Example
Inner Join Example

The above query selects all the columns from both table Employee and Department and we have condition added which is they are having equal department ID.

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 rows from the left table. In this the rows for which there is no matching row on the right side, the resultset will contain null. Left join also called a left outer join.

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;

Left Join or Left Outer Join
Left Join or Left Outer Join

Actual Query

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

Result Of Left Join or Left Outer Join
Result Of Left Join or Left Outer Join

Right Outer Join Or Right Join:

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

Right Join or Right Outer Join
Right Join or Right Outer Join

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

Right Join Result Of Query
Right Join Result Of Query

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.

Full Outer Join Or Full Join
Full Outer Join Or Full Join

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

Result Of Full Join Or Full Outer Join
Result Of Full Join Or Full Outer Join

Cross Join

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

11,011 rows.

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

Cross Join Result
Cross Join Result

Self  Join

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

A self-join may use an inner join or left join. When you write a query for self-join at that time remember to write 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

Self Join Query Result

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts