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:
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
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:
-- Selecting columns from two tables using INNER JOIN
SELECT
foods.item_name, -- Selecting item_name column from foods table
foods.item_unit, -- Selecting item_unit column from foods table
company.company_name, -- Selecting company_name column from company table
company.company_city -- Selecting company_city column from company table
FROM
foods -- Alias for the foods table
INNER JOIN
company -- Alias for the company table
ON
foods.company_id = company.company_id;
Explanation:
- The SQL code retrieves data from two tables, 'foods' and 'company', and combines them into a single result set based on a common column.
- The query selects specific columns from both tables, which are 'item_name' and 'item_unit' from the 'foods' table, and 'company_name' and 'company_city' from the 'company' table.
- It uses an inner join to merge the records from both tables where the 'company_id' column in the 'foods' table matches the 'company_id' column in the 'company' table.
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:
-- This query selects specific columns from two tables and joins them based on a common column.
SELECT foods.item_name, -- Selecting the item name from the 'foods' table.
foods.item_unit, -- Selecting the item unit from the 'foods' table.
company.company_name, -- Selecting the company name from the 'company' table.
company.company_city -- Selecting the company city from the 'company' table.
FROM foods -- Specifying the first table to select data from, which is 'foods'.
JOIN company -- Specifying the type of join, which is an inner join (by default), and specifying the second table to join, which is 'company'.
ON foods.company_id = company.company_id;
-- Specifying the condition for joining the two tables, which is where the 'company_id' column in the 'foods' table matches the 'company_id' column in the 'company' table.
Explanation:
- The SQL code performs the same task as the previous query but with slightly different syntax.
- The query selects specific columns from both the 'foods' and 'company' tables and merges them into a single result set.
- It uses the 'JOIN' keyword to specify the type of join, which is an inner join by default.
- The 'ON' clause specifies the condition for joining the two tables, where the 'company_id' column in the 'foods' table matches the 'company_id' column in the 'company' table.
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 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:
-- This query selects all columns from the resulting joined table of two tables based on a common column.
SELECT * -- Selecting all columns from the resulting joined table.
FROM foods -- Specifying the first table to select data from, which is 'foods'.
JOIN company -- Specifying the type of join, which is an inner join (by default), and specifying the second table to join, which is 'company'.
ON foods.company_id = company.company_id;
-- Specifying the condition for joining the two tables, which is where the 'company_id' column in the 'foods' table matches the 'company_id' column in the 'company' table.
Explanation:
- The SQL code retrieves data from two tables, 'foods' and 'company', and combines them into a single result set based on a common column.
- The query selects all columns from the resulting joined table, which includes columns from both the 'foods' and 'company' tables.
- It uses the 'JOIN' keyword to specify the type of join, which is an inner join by default.
- The 'ON' clause specifies the condition for joining the two tables, where the 'company_id' column in the 'foods' table matches the 'company_id' column in the 'company' table.
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
-- This query selects all columns from the resulting joined table of two tables based on a common column.
SELECT * -- Selecting all columns from the resulting joined table.
FROM Table1 -- Specifying the first table to select data from, which is 'Table1'.
JOIN Table2 -- Specifying the type of join, which is an inner join (by default), and specifying the second table to join, which is 'Table2'.
ON Table1.column_name = Table2.column_name;
-- Specifying the condition for joining the two tables, which is where the 'column_name' column in 'Table1' matches the 'column_name' column in 'Table2'.
Explanation:
- The SQL code retrieves data from two tables, 'Table1' and 'Table2', and combines them into a single result set based on a common column.
- The query selects all columns from the resulting joined table, which includes columns from both 'Table1' and 'Table2'.
- It uses the 'JOIN' keyword to specify the type of join, which is an inner join by default.
- The 'ON' clause specifies the condition for joining the two tables, where the 'column_name' column in 'Table1' matches the 'column_name' column in 'Table2'.
Using INNER JOIN Clause
-- This query selects all columns from the resulting joined table of two tables based on a common column.
SELECT * -- Selecting all columns from the resulting joined table.
FROM Table1 -- Specifying the first table to select data from, which is 'Table1'.
INNER JOIN Table2 -- Specifying the type of join, which is an inner join, and specifying the second table to join, which is 'Table2'.
ON Table1.column_name = Table2.column_name;
-- Specifying the condition for joining the two tables, which is where the 'column_name' column in 'Table1' matches the 'column_name' column in 'Table2'.
Explanation:
- The SQL code retrieves data from two tables, 'Table1' and 'Table2', and combines them into a single result set based on a common column.
- The query selects all columns from the resulting joined table, which includes columns from both 'Table1' and 'Table2'.
- It uses the 'INNER JOIN' keyword combination to specify the type of join, which is an inner join, meaning only the rows that have matching values in both tables are included in the result set.
- The 'ON' clause specifies the condition for joining the two tables, where the 'column_name' column in 'Table1' matches the 'column_name' column in 'Table2'.
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.
Here is INNER JOIN
-- This query selects all columns from the resulting joined table of two tables based on a common column.
SELECT * -- Selecting all columns from the resulting joined table.
FROM tableX -- Specifying the first table to select data from, which is 'tableX'.
INNER JOIN tableY -- Specifying the type of join, which is an inner join, and specifying the second table to join, which is 'tableY'.
ON tableX.X = tableY.Y;
-- Specifying the condition for joining the two tables, where the 'X' column in 'tableX' matches the 'Y' column in 'tableY'.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', and combines them into a single result set based on a common column.
- The query selects all columns from the resulting joined table, which includes columns from both 'tableX' and 'tableY'.
- It uses the 'INNER JOIN' keyword combination to specify the type of join, which is an inner join, meaning only the rows that have matching values in both tables are included in the result set.
- The 'ON' clause specifies the condition for joining the two tables, where the 'X' column in 'tableX' matches the 'Y' column in 'tableY'.
or
-- This query selects all columns from both 'tableX' and 'tableY' where a specific condition is met.
SELECT tableX.*, tableY.* -- Selecting all columns from both 'tableX' and 'tableY'.
FROM tableX, tableY -- Specifying the tables to select data from, 'tableX' and 'tableY'.
WHERE tableX.X = tableY.Y;
-- Specifying the condition for selecting rows, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', based on a specific condition.
- The query selects all columns from both 'tableX' and 'tableY'.
- It uses the comma syntax to specify multiple tables from which to select data, without explicitly specifying a join type. This syntax is equivalent to a cross join, where each row in the first table is combined with each row in the second table.
- The 'WHERE' clause specifies the condition for selecting rows, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'. This effectively filters the result set to include only rows where this condition is met.
Output:
Here only the matching of both tableX and tableY have appeared in the result set.
Here is LEFT OUTER JOIN
-- This query selects all columns from both 'tableX' and 'tableY' where a specific condition is met, with an outer join on 'tableY'.
SELECT tableX.*, tableY.* -- Selecting all columns from both 'tableX' and 'tableY'.
FROM tableX, tableY -- Specifying the tables to select data from, 'tableX' and 'tableY'.
WHERE tableX.X = tableY.Y(+);
-- Specifying the condition for selecting rows, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY', with an outer join syntax.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', based on a specific condition, with an outer join.
- The query selects all columns from both 'tableX' and 'tableY'.
- It uses the comma syntax to specify multiple tables from which to select data, without explicitly specifying a join type. This syntax is equivalent to a cross join, where each row in the first table is combined with each row in the second table.
- The '(+)' syntax on the right-hand side of the condition indicates an outer join, specifically a right outer join, where all rows from the 'tableY' table are included in the result set, regardless of whether there is a matching row in 'tableX'. The '(+)' is Oracle's proprietary syntax for outer joins.
or
-- This query selects all columns from the resulting joined table of two tables, using a left outer join.
SELECT * -- Selecting all columns from the resulting joined table.
FROM tableX -- Specifying the first table to select data from, which is 'tableX'.
LEFT OUTER JOIN tableY -- Specifying the type of join, which is a left outer join, and specifying the second table to join, which is 'tableY'.
ON tableX.X = tableY.Y;
-- Specifying the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', and combines them into a single result set based on a specific condition, using a left outer join.
- The query selects all columns from the resulting joined table, which includes columns from both 'tableX' and 'tableY'.
- It uses the 'LEFT OUTER JOIN' keyword combination to specify the type of join, which is a left outer join. This means all rows from 'tableX' are included in the result set, regardless of whether there is a matching row in 'tableY'.
- The 'ON' clause specifies the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Output:
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
-- This query selects all columns from the resulting joined table of two tables, using a right outer join.
SELECT * -- Selecting all columns from the resulting joined table.
FROM tableX -- Specifying the first table to select data from, which is 'tableX'.
RIGHT OUTER JOIN tableY -- Specifying the type of join, which is a right outer join, and specifying the second table to join, which is 'tableY'.
ON tableX.X = tableY.Y;
-- Specifying the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', and combines them into a single result set based on a specific condition, using a right outer join.
- The query selects all columns from the resulting joined table, which includes columns from both 'tableX' and 'tableY'.
- It uses the 'RIGHT OUTER JOIN' keyword combination to specify the type of join, which is a right outer join. This means all rows from 'tableY' are included in the result set, regardless of whether there is a matching row in 'tableX'.
- The 'ON' clause specifies the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Output:
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
-- This query selects all columns from the resulting joined table of two tables, using a full outer join.
SELECT * -- Selecting all columns from the resulting joined table.
FROM tableX -- Specifying the first table to select data from, which is 'tableX'.
FULL OUTER JOIN tableY -- Specifying the type of join, which is a full outer join, and specifying the second table to join, which is 'tableY'.
ON tableX.X = tableY.Y;
-- Specifying the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Explanation:
- The SQL code retrieves data from two tables, 'tableX' and 'tableY', and combines them into a single result set based on a specific condition, using a full outer join.
- The query selects all columns from the resulting joined table, which includes columns from both 'tableX' and 'tableY'.
- It uses the 'FULL OUTER JOIN' keyword combination to specify the type of join, which is a full outer join. This means all rows from both 'tableX' and 'tableY' are included in the result set, regardless of whether there is a matching row in the other table.
- The 'ON' clause specifies the condition for joining the two tables, where the value of column 'X' in 'tableX' matches the value of column 'Y' in 'tableY'.
Output:
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 -
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics