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_id Firstname Lastname
1 Dan patel
2 Riya Patel
3 Priya Joshi
4 Aisha Patel

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

pro_id Product_name e_id
1 Pens 2
2 Books 4

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_id firstname Lastname product_name
2 Riya Patel Pens
4 Aisha Patel Books

Example(Multi join):

Table: salary(sa_id is primary key)

sa_id salary
1 10,000
2 20,000
3 5,000
4 30,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_id name ad_id s_id
1 Riya 2 2
2 Khyati 3 3

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:

Id name city Salary
1 Riya v.v.nagar 20,000
2 Khyati Nadiad 5,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