Inner Join In SQL With Examples

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

(departments)

Let’s Check the Syntax Of Natural Join:-

SELECT COLUMN 1, COLUMN 2
FROM TABLE NAME 1
NATURAL JOIN TABLE NAME 2;

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:-

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

SQL> select employee_id,last_name,location_id,department_id
  2  from employees join departments
  3  Using(department_id);

Output:-

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)

(departments)

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.