Inner Join In SQL With Examples

0
230

Introduction to Inner Join In SQL

The most valuable and commonly used of the joins is the Inner Join In SQL. 

They are also referred to as an EQUIJOIN.

The Inner Join In SQL creates a new result table by joining column values of two tables (table1 and table2) based upon the join-word. 

The query compares each row of table1 with each row of table2 to find all combinations of rows which capture the join-predicate.  When the join-predicate is satisfied, column values for each matched pair of rows of A and B are linked into a result row.

Types of Inner Join

Natural join:

– USING clause

– ON clause

  • The NATURAL JOIN clause is based on every column in the two tables that have the same name.
  • It selects rows from the two tables that have similar values in all equaled columns.
  • If the columns having the same names have various data types, an error is turned.

Here are the details of two table:-

  • Employees
  • Departments

Employees Table structure

 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME  NOT NULL NUMBER(6)
 LAST_NAME     VARCHAR2(20)
 EMAIL           NOT NULL VARCHAR2(25)
 PHONE_NUMBER  NOT NULL VARCHAR2(25)
 HIRE_DATE         VARCHAR2(20)
 JOB_ID    NOT NULL DATE
 SALARY          NOT NULL VARCHAR2(10)
  COMMISSION_PCT       NUMBER(8,2)
 MANAGER_ID   NUMBER(2,2)
 DEPARTMENT_ID  NUMBER(6)

(departments)

 DEPARTMENT_ID                NOT NULL NUMBER(4)
 DEPARTMENT_NAME   NOT NULL VARCHAR2(30)
 MANAGER_ID     NUMBER(6)
 LOCATION_ID       NUMBER(4)

Let’s Check the Syntax Of Natural Join:-

SELECT COLUMN 1, COLUMN 2
FROM TABLE NAME 1
NATURAL JOIN TABLE NAME 2;
 DEPARTMENT_ID                NOT NULL NUMBER(4)
 DEPARTMENT_NAME   NOT NULL VARCHAR2(30)
 MANAGER_ID     NUMBER(6)
 LOCATION_ID       NUMBER(4)

Display Department_id,department_name,location_id,city?

Using Natural Join.

Step1:- Check the tables which had the column name ask in Query.

For Example:-


SQL> describe departments;

Step1:- Check the tables which had the column name ask in Query.

SQL> describe departments;

SQL> describe locations;

Here are two tables which is having the columns inside it.

Step2:- Write a Query to retrive the information. 


select department_id,department_name,location_id,city
from departments
Natural JOIN Locations;

Output:-

DEPARTMENT_ID DEPARTMENT_NAME    LOCATION_ID   CITY
23    clg_admin 1400Southlake

Retrieving Records with the USING Clause:-

  • If some columns have the same names but the data types do not match, use the USING clause to define the columns for the equijoin.
  • Use the USING clause to join on the basis of only one column when more than one column matches.
  • The NATURAL JOIN and USING clauses are commonly exclusive.

Here are the details of two table:-

  • Employees
  • Departments
 EMPLOYEE_ID NOT NULL NUMBER(6)
 FIRST_NAME   NOT NULL NUMBER(6)
 LAST_NAME     VARCHAR2(20)
 EMAIL           NOT NULL VARCHAR2(25)
 PHONE_NUMBER  NOT NULL VARCHAR2(25)
 HIRE_DATE         VARCHAR2(20)
 JOB_ID    NOT NULL DATE
 SALARY          NOT NULL VARCHAR2(10)
  COMMISSION_PCT        NUMBER(8,2)
 MANAGER_ID   NUMBER(2,2)
 DEPARTMENT_ID  NUMBER(6)

 DEPARTMENT_ID                NOT NULL NUMBER(4)
 DEPARTMENT_NAME   NOT NULL VARCHAR2(30)
 MANAGER_ID     NUMBER(6)
 LOCATION_ID       NUMBER(4)
SQL> select employee_id,last_name,location_id,department_id
  2  from employees join departments
  3  Using(department_id);

Output:-

EMPLOYEE_IDLAST_NAME   LOCATION_IDDEPARTMENT_ID
101 King170090
102Kochhar   90
103De Haan         90
104Hunold  60
105Ernst  60

In this example, we retrieve a data from two tables on the basis of one common column department_id because there is two common columns.

ON Clause:-

  • The join condition for the natural join is essentially an equijoin of all columns with the related name.
  • Use the ON clause to define arbitrary conditions or specify columns to join.
  • The join condition is separated from other search requirements.
  • The ON clause makes the code simple to understand.

Here are the details of two table:-

  • Employees
  • Departments

Let’s Check the Syntax Of ON Clause:-

SELECT TABLE _ALISE.COLUMN 1, TABLE_ALISE.COLUMN 2

FROM TABLE NAME 1 TABLE_ALISE NAME

JOIN TABLE NAME 2 TABLE_ALISE NAME

USING(TABLE_ALISE.Common_column_name1=TABLE_ALISE.Common_column_name2);

For Example:-

Q] Display the following columns from

And  (departments)

 EMPLOYEE_ID             NOT NULL NUMBER(6)
 FIRST_NAME   VARCHAR2(20)
 MANAGER_ID

(departments)

 DEPARTMENT_ID          NOT NULL NUMBER(4)
 DEPARTMENT_NAME

Step 1:- Give table Alise.

Table Alise

employees e

departments d

The query is as follows:-

select e.employee_id,e.first_name,e.manager_id,d.department_id,d.department_name
from employees e JOIN departments d
ON(e.department_id=d.department_id);

Output:-

EMPLOYEE_ID FIRST_NAME  MANAGER_ID  DEPARTMENT_ID D_NAME

        100              Steven            90                                                        Executive

Here we join a specific column and retrieve the information on the basis common columns from both the table.

Conclusion

In this blog, you will get the knowledge of types of inner join and their uses which is help you to build a career in SQL.

LEAVE A REPLY

Please enter your comment!
Please enter your name here