w3resource

SQL Inner Join

What is Inner Join in SQL?

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

Pictorial presentation of SQL Inner Join:

SQL inner join

Syntax:

SELECT * 
FROM table1 INNER JOIN table2 
ON table1.column_name = table2.column_name; 

OR

SELECT * 
FROM table1
JOIN table2 
ON table1.column_name = table2.column_name; 

The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.

Syntax diagram - INNER JOIN

SQL INNER JOIN syntax

Example: SQL INNER JOIN between two tables

Here is an example of inner join in SQL between two tables.

Sample table: foods


Sample table: company


To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition -

1. company_id of foods and company table must be same,

the following SQL statement can be used :

SQL Code:

SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city 
FROM foods 
INNER JOIN company 
ON foods.company_id =company.company_id;

Output:

ITEM_NAME                 ITEM_ COMPANY_NAME              COMPANY_CITY
------------------------- ----- ------------------------- --------------
Chex Mix                  Pcs   Akas Foods                Delhi
Cheez-It                  Pcs   Jack Hill Ltd             London
BN Biscuit                Pcs   Jack Hill Ltd             London
Mighty Munch              Pcs   Foodies.                  London
Pot Rice                  Pcs   Jack Hill Ltd             London
Jaffa Cakes               Pcs   Order All                 Boston

Example of SQL INNER JOIN using JOIN keyword

To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition -

1. company id of foods and company id of company table must be same,

the following SQL statement can be used:

SQL Code:

SELECT foods.item_name,foods.item_unit, 
company.company_name,company.company_city 
FROM foods 
JOIN company 
ON foods.company_id =company.company_id;

Output:

ITEM_NAME                 ITEM_ COMPANY_NAME              COMPANY_CITY
------------------------- ----- ------------------------- -------------
Chex Mix                  Pcs   Akas Foods                Delhi
Cheez-It                  Pcs   Jack Hill Ltd             London
BN Biscuit                Pcs   Jack Hill Ltd             London
Mighty Munch              Pcs   Foodies.                  London
Pot Rice                  Pcs   Jack Hill Ltd             London
Jaffa Cakes               Pcs   Order All                 Boston

Pictorial Presentation of SQL Inner Join of Company and Foods Tables:

SQL INNER JOIN OF COMPANY AND FOODS TABLES

SQL INNER JOIN for all columns

To get all the columns from foods and company table after joining, with the following condition -

1. company id of foods and company id of company table must be same,

the following SQL statement can be used:

SQL Code:

SELECT * 
FROM foods 
JOIN company 
ON foods.company_id =company.company_id;

Output:

ITEM_ID  ITEM_NAME                 ITEM_ COMPAN COMPAN COMPANY_NAME              COMPANY_CITY
-------- ------------------------- ----- ------ ------ ------------------------- -------------
1        Chex Mix                  Pcs   16     16     Akas Foods                Delhi
6        Cheez-It                  Pcs   15     15     Jack Hill Ltd             London
2        BN Biscuit                Pcs   15     15     Jack Hill Ltd             London
3        Mighty Munch              Pcs   17     17     Foodies.                  London
4        Pot Rice                  Pcs   15     15     Jack Hill Ltd             London
5        Jaffa Cakes               Pcs   18     18     Order All                 Boston

Difference between JOIN and INNER JOIN

JOIN returns all rows from tables where the key record of one table is equal to the key records of another table.

The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. An SQL INNER JOIN is same as JOIN clause, combining rows from two or more tables.

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.

Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same for both the students and courses tables.

Using JOIN Clause

SELECT * FROM 
Table1 JOIN Table2 
ON Table1.column_name=Table2.column_name;

Using INNER JOIN Clause

SELECT * 
FROM Table1 INNER JOIN Table2 
ON Table1.column_name= Table2.column_name;

Difference between INNER JOIN and OUTER JOIN

An INNER JOIN is such type of join that returns all rows from both the participating tables where the key record of one table is equal to the key records of another table. This type of join required a comparison operator to match rows from the participating tables based on a common field or column of both the tables.

Where as the OUTER JOIN returns all rows from the participating tables which satisfy the condition and also those rows which do not match the condition will appear in this operation. This result set can appear in three types of format -

The first one is LEFT OUTER JOIN, in this join includes all the rows from a left table of JOIN clause and the unmatched rows from a right table with NULL values for selected columns.

The second one is RIGHT OUTER JOIN, in this join includes all rows from the right of JOIN cause and the unmatched rows from the left table with NULL values for selected columns.

The last one in FULL OUTER JOIN, in this join, includes the matching rows from the left and right tables of JOIN clause and the unmatched rows from left and right table with NULL values for selected columns.

Example:

Here is two table tableX and tableY and they have no duplicate rows in each. In tableX the values ( A,B) are unique and in tableY the values (E,F) are unique, but the values (C and D) are common in both the tables.

tablex and tabley for outer join difference

Here is INNER JOIN

SELECT * 
FROM tableX 
INNER JOIN tableY on tableX.X = tableY.Y;

or

SELECT tableX.*,tableY.*  
FROM tableX,tableY 
WHERE tableX.X = tableY.Y;

Output:

Sql inner join and outer join difference

Here only the matching of both tableX and tableY have appeared in the result set.

Here is LEFT OUTER JOIN

SELECT tableX.*,tableY.*  
FROM tableX,tableY 
WHERE tableX.X = tableY.Y(+)

or

SELECT * 
FROM tableX 
LEFT OUTER JOIN tableY ON tableX.X= tableY.Y

Output:

Sql left outer join difference with inner join

Here all the rows from tableX that is left side of JOIN clause and all the rows with NULL values for unmatched columns from tableY that is the right side of JOIN clause have appeared.

Here is RIGHT OUTER JOIN

SELECT * FROM tableX 
RIGHT OUTER JOIN tableY ON tableX.X= tableY.Y

Output:

Sql right outer join difference

Here all the rows from tableY that is the right side of JOIN clause and all the rows with NULL values for unmatched columns from tableX that is left side of JOIN clause have appeared.

Here is FULL OUTER JOIN

SELECT * 
FROM tableX 
FULL OUTER JOIN tableY ON tableX.X= tableY.Y

Output:

Sql full outer join difference

Here all the matching rows from tableX and tableY and all the unmatched rows with NULL values for both the tables have appeared.

INNER JOIN ON vs WHERE clause

The WHERE clause, what is done is that all records that match the WHERE condition are included in the result set but an INNER JOIN is that, data not matching the JOIN condition is excluded from the result set.

Linking between two or more tables should be done using an INNER JOIN ON clause but filtering on individual data elements should be done with WHERE clause.

INNER JOIN is ANSI syntax whereas the WHERE syntax is more relational model oriented.

The INNER JOIN is generally considered more readable and it is a cartesian product of the tables, especially when you join lots of tables but the result of two tables JOIN'ed can be filtered on matching columns using the WHERE clause.

INNER JOINS: Relational Databases

Key points to remember

Click on the following to get the slides presentation -

SQL JOINS, slide presentation

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: SQL NON EQUI JOIN
Next: SQL NATURAL JOIN



Follow us on Facebook and Twitter for latest update.