SQL Inner Join
Introduction:
An SQL INNER JOIN is used to combine rows from two or more tables based on a related column between them. This is a fundamental operation in SQL that allows you to retrieve data that spans multiple tables, making it essential for effective database management and analysis.
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
Practical Use:
Imagine you have a table of customer orders and another table with customer details. An INNER JOIN allows you to combine these tables to see the complete order information along with customer details.
Table of Contents:
- Example: SQL INNER JOIN between two tables
- Example :SQL INNER JOIN using JOIN keyword
- Example :SQL INNER JOIN using more than two tables
- Pictorial Presentation: SQL INNER JOIN
- SQL INNER JOIN for all columns
- Difference between JOIN and INNER JOIN
- Difference between INNER JOIN and OUTER JOIN
- INNER JOIN ON vs WHERE clause
- Frequently Asked Questions (FAQ)
- Key points to remember
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
Example: SQL INNER JOIN using more than two tables
Sample table: customer_new
-----------+-------------+
customer_id|customer_name|
-----------+-------------+
1|Eden Ross |
2|Jax Prince |
3|Dilan McKay |
4|Bob Brown |
-----------+-------------+
Sample table: products_new
----------+------------+
product_id|product_name|
----------+------------+
101|Laptop |
102|Smartphone |
103|Tablet |
104|Headphones |
----------+------------+
Sample table: orders_new
--------+----------+-----------+----------+
order_id|order_date|customer_id|product_id|
--------+----------+-----------+----------+
1001|2024-01-15| 1| 101|
1002|2024-01-16| 2| 102|
1003|2024-01-17| 1| 103|
1004|2024-01-18| 3| 104|
1005|2024-01-19| 4| 101|
--------+----------+-----------+----------+
SQL Code:
SELECT
o.order_id,
o.order_date,
c.customer_name,
p.product_name
FROM
orders_new o
INNER JOIN
customers_new c ON o.customer_id = c.customer_id
INNER JOIN
products_new p ON o.product_id = p.product_id;
Explanation:
- The above SQL query joins the orders table with the customers and products tables to fetch the order details along with customer names and product names.
- The query starts by selecting the columns we want to retrieve: order_id, order_date, customer_name, and product_name.
- It specifies the orders table as the base table (o is an alias for orders).
- An INNER JOIN is performed between orders (o) and customers (c), where customer_id in both tables matches.
- Another INNER JOIN is performed between orders (o) and products (p), where product_id in both tables matches.
- This results in a combined dataset that includes only the rows where there are matching values in all three tables for customer_id and product_id.
Output:
--------+----------+-------------+------------+
order_id|order_date|customer_name|product_name|
--------+----------+-------------+------------+
1001|2024-01-15|Eden Ross |Laptop |
1002|2024-01-16|Jax Prince |Smartphone |
1003|2024-01-17|Eden Ross |Tablet |
1004|2024-01-18|Dilan McKay |Headphones |
1005|2024-01-19|Bob Brown |Laptop |
--------+----------+-------------+------------+
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
Frequently Asked Questions (FAQ) - SQL INNER JOIN
1. What is an SQL INNER JOIN?
An SQL INNER JOIN combines rows from two or more tables based on a related column between them. It is a fundamental operation that retrieves data spanning multiple tables, essential for effective database management and analysis.
2. How does an INNER JOIN work in SQL?
An INNER JOIN selects all rows from both participating tables as long as there is a match between the columns. It is equivalent to the JOIN clause and combines rows from two or more tables.
3. What is the difference between SQL JOIN and SQL INNER JOIN?
JOIN and INNER JOIN are functionally equivalent. Both return rows from tables where the key records match. However, INNER JOIN specifically emphasizes the selection of matching rows from the combined tables.
4. How does SQL INNER JOIN differ from SQL OUTER JOIN?
- INNER JOIN: Returns only the rows with matching values in both tables.
- OUTER JOIN: Returns matching rows and includes unmatched rows with NULL values. There are three types of OUTER JOINs:
- LEFT OUTER JOIN: Includes all rows from the left table and unmatched rows from the right table.
- RIGHT OUTER JOIN: Includes all rows from the right table and unmatched rows from the left table.
- FULL OUTER JOIN: Includes matching rows and unmatched rows from both tables.
5. When should we use SQL INNER JOIN versus a WHERE clause?
- INNER JOIN: Used to link tables based on matching columns.
- WHERE clause: Used for filtering data within the joined tables based on specific conditions. INNER JOIN is more readable and aligns with ANSI SQL standards, whereas the WHERE clause is more oriented toward filtering after the tables are joined.
6. What are some key points to remember about SQL INNER JOIN?
- INNER JOIN is essential for combining rows from multiple tables based on related columns.
- It is equivalent to the JOIN clause and is commonly used for retrieving data from relational databases.
- INNER JOIN focuses on matching records, whereas OUTER JOIN includes unmatched records with NULL values.
- Linking tables should be done using INNER JOIN, while filtering data should be done with the WHERE clause.
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
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql/joins/perform-an-inner-join.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics