You are here

SQL: INNER JOIN

inner join

INNER JOIN

  • Inner join is same as join, so we can use either inner join or join keyword.
  • Using this join we can joins two or more tables and we can also specify more then one predicate in the ON clause.
  • This type of join can be used in situations where selecting only those rows that have common values in columns specified in the ON clause. It returns all rows from both tables where there is match based on the predicate.
  • The WHERE and ON clause is same for the inner join both server the same filtering purpose. Both filter only rows for which predicate evaluates to true and discard rows for which the predicate evaluates to false.

Syntax :
Select <col1>, <col2>,…. <coln> from <table1> INNER JOIN/JOIN <table2> ON table1.col=table2.col ;

Explanation:

  • Col1 in table1 is primary key.
  • Col2 in table2 is foreign key.
  • Col1 and col2 must have the same data type and size.

Example(simple join):

Table: employee(emp_id is primary key)

emp_idFirstnameLastname
1Danpatel
2RiyaPatel
3PriyaJoshi
4AishaPatel

Table: product(pro_id is primary key and e_id is foreign key)

pro_idProduct_namee_id
1Pens2
2Books4

Retrieve the name of employee and product handle by it.

Basic  join:

select employee.emp_id, employee.firstname, employee.lastname, product.product_name from employee  INNER JOIN product on employee.emp_id=product.e_id;

Table Alias:

select e.emp_id, e.firstname, e.lastname, p.product_name from employee as e  INNER JOIN product as p on e.emp_id=p.e_id;

Output:

emp_idfirstnameLastnameproduct_name
2RiyaPatelPens
4AishaPatelBooks

Example(Multi join):

Table: salary(sa_id is primary key)

sa_idsalary
110,000
220,000
35,000
430,000

Table: address(a_id is primary key)

a_id

city

1

Anand
2

v.v.nagar

3

Nadiad
4

Baroda

Table: tblemployee(e_id is primary key, ad_id is foreign key references to address table a_id, s_id is foreign key references to salary table sa_id )

e_idnamead_ids_id
1Riya22
2Khyati33

Retrieve the name of employee and it’s address and salary.

select e.e_id as id, e.Name as [name], a.city, sa.salary from tblemployee as e inner join [address] as a on a.a_id=e.ad_id inner join salary as sa on sa.sa_id=e.s_id;

output:

IdnamecitySalary
1Riyav.v.nagar20,000
2KhyatiNadiad5,000

Download Example [media-downloader media_id=”263″ texts=”Download Code”]

Keep Visiting TechwithR.com for more upcoming top technical articles.

Detail course about DBA visit TechNet Consultancy

You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.

Leave a Reply

Top