SQL Join Command - Inner, Outer and Left Join

    8 Votes

In order to avoid data duplication, data is stored in related tables. Join commands like inner join, outer join and left join is used to fetch data from related tables.

Different type of joins are
  • Join or inner join
  • Outer Join
  • Left Join
  • Full Join

SQL Join query with examples on Employee and Salary table given below are

Table Name : Employee
Employee_IDEmployee_NameAge
1 John 28
2 Alex 24
3 James 35
4 Roy 22
5 Kay 44
Table Name : Salary
Employee_ID_RefYearMonthSalary
1 2015 April 30000
1 2015 May 31000
1 2015 June 32000
2 2015 April 40000
2 2015 May 41000
2 2015 June 42000

Inner JOIN

Inner join returns rows when there is at least one match in both tables. SQL query to join above tables is as given below.

select * from employee a join salary b on a.employee_id = b.employee_id_ref

The result of the above given query is as shown below.

Employee_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1 John 28 1 2015 April 30000
1 John 28 1 2015 May 31000
1 John 28 1 2015 June 32000
2 Alex 24 2 2015 April 40000
2 Alex 24 2 2015 May 41000
2 Alex 24 2 2015 June 42000

Left JOIN

Return all rows from the left table, even if there are no matches in the right table. SQL query to left join above tables is as given below.

select * from employee a left join salary b on a.employee_id = b.employee_id_ref
Employee_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1 John 28 1 2015 April 30000
1 John 28 1 2015 May 31000
1 John 28 1 2015 June 32000
2 Alex 24 2 2015 April 40000
2 Alex 24 2 2015 May 41000
2 Alex 24 2 2015 June 42000
3 James 35        
4 Roy 22        
5 Kay 44        

Here, even though there is no matching records for employees "James,Roy and Kay", there names are shown in the results.

Right JOIN

Return all rows from the right table, even if there are no matches in the left table. SQL query to right join above tables are as given below

select * from employee a right join salary b on a.employee_id = b.employee_id_ref
Employee_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1 John 28 1 2015 April 30000
1 John 28 1 2015 May 31000
1 John 28 1 2015 June 32000
2 Alex 24 2 2015 April 40000
2 Alex 24 2 2015 May 41000
2 Alex 24 2 2015 June 42000

Full JOIN

Return rows when there is a match in one of the tables. SQL query to full join above tables are as given below

select * from employee a full join salary b on a.employee_id = b.employee_id_ref

The result of the full join is as shown below.

Employee_IDEmployee_NameAgeEmployee_ID_RefYearMonthSalary
1 John 28 1 2015 April 30000
1 John 28 1 2015 May 31000
1 John 28 1 2015 June 32000
2 Alex 24 2 2015 April 40000
2 Alex 24 2 2015 May 41000
2 Alex 24 2 2015 June 42000
3 James 35        
4 Roy 22        
5 Kay 44        

SQL Mathematical Operators

SQL Calculation fields can be used to perform mathematical ( + , - , * , /) operation on retrieved data. SQL Query with different mathematical operations performed on the table sales is given below

Table Name : Sales

ItemQuantityPriceReductionTotal
Item1 5 400 10 2000
Item2 6 300 10 1800
Item3 3 15 5 45
Item4 2 30 5 60
Item5 2 300 5 600
Item6 6 400 10 2400

For multiplication , following SQL Query is used . For example, Query to find the total of each based on the quantity and base price

select items, quantity* price Total from sales
ItemTotal
Item1 2000
Item2 1800
Item3 45
Item4 60
Item5 600
Item6 2400

Similarly mathematical operators ( - , + , % ) can be used in query.

SQL Functions

  • Upper
  • Lower
  • Substring
  • Date
  • ABS
  • Cos
  • exp
  • pi
  • sin
  • sqrt
  • tan