SQL Joins

SQL  Joins statement is used to retrieve data from two or more tables based on the join condition.
The join condition refers to common key columns present between joining tables. In SQL joins, the join keyword is used to perform the join operation, and the ON clause is used to define the join condition.

In the ON clause, the key that is (primary key and foreign key) columns are used to define the join condition, Along with that the operators such as =,<,> are used to specify the match condition.

SQL Joins

 

SQL Joins -Types

Different types of SQL Joins are as follows:

  • Inner Join
  • Left Join
  • Right Join
  • Full Outer Join
  • Self Join
Inner Join

Only all matching records will be retrieved in the inner join based on the join condition. Only all matching records will be retrieved in the inner join when the join condition satisfies. The joining key column will prepare the result set by combining all records from both tables where the condition satisfies.

SQL Joins

 

Syntax and Example

 

Syntax:-

select a.column1,b.column2
 from TableA a
inner join TableB b
 on a.matchingcolumn= b.matchingcolumn

Example:-

select s.studid,d.deptname
from student s
inner join department d
on s.deptid= d.deptid
Left Join

All matching and nonmatching records from the left table of the join clause and only matching records from the right table of the join clause will be populated as query results.

sql joins

 

Syntax and Example

Syntax:-

 select a.column1,b.column2 from TableA a 
left join TableB b 
on a.matchingcolumn= b.matchingcolumn

 Example:- 
select s.studid,d.deptname from student s
 left join department d
 on s.deptid= d.deptid
Right Join

All matching and nonmatching records from the right table of the join clause and only matching records from the left table of the join clause will be populated as query results.

 

Sql joins

 

Syntax and Example

Syntax:-
select a.column1,b.column2 
from TableA a 
right join TableB b 
on a.matchingcolumn= b.matchingcolumn
 Example:-
 select s.studid,d.deptname 
from student s 
right join department d 
on s.deptid= d.deptid
Full outer Join

Returns all records from the both right and left table of the join clause based on the match condition that joins the condition. For the match data column values will be returned and for no-match column values  returns null.

Syntax and Example

Syntax:- 
select a.column1,b.column2 
from TableA a 
full outer join TableB b 
on a.matchingcolumn= b.matchingcolumn
 Example:-
 select s.studid,d.deptname 
from student s 
full outer join department d 
on s.deptid= d.deptid

 

self Join

The table joins itself to get some useful information from the same table. For example, self join is used in getting employee and their manager. Use  where clause instead of ON clause to  perform self join operation.

Syntax and Example

Syntax:- 
select a.column1,b.column2 
from TableA a 
left join TableA b 
where a.matchingcolumn= b.matchingcolumn
 Example:-
 select s.employeename,d.managername 
from employee s 
right join employee d 
where s.empid= d.managerid

 

Cross Join

Each row from one table is joined with each row of another table used in join operation.

Syntax and Example

Syntax:- 
select a.column1,b.column2 
from TableA a 
cross  join TableB b 

 Example:-
 select *
from student s 
cross join department d 
on s.deptid= d.deptid

In SQL joins cross join is used to create combination of results which is further used to perform some database operations. Suppose  student table contains 10 records and department table  contains 6 records then cross join will return 60 rows as a query result.

In SQL joins the data from multiple tables is retrieved based on requirements. Whenever we join operations on two or more tables, we have to understand which table will be used as the base table to get data and thereafter remaining tables can be used.

The SQL joins can be used to retrieve data from multiple tables, perform complex join operations, Preparing views that can be used by various types of users for database operations and reporting purposes.

Additional References 

SQL data types

SQL Syntax

What is SQL