SQL Inner Join is a type of join operation in SQL that combines rows from two or more tables based on a related column between them. It returns only the matching rows from both tables and excludes the non-matching rows.
The Inner Join operation is also known as an Equi-Join because it uses the equality operator (=) to match the related columns between the tables. The Inner Join operation is the most commonly used join operation in SQL because it helps to retrieve data from multiple tables that have a relationship between them.
The syntax for Inner Join in SQL is as follows:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
The above syntax retrieves the column_name(s) from table1 and table2 where the related column_name between them is equal. The ON keyword specifies the related column_name between the tables.
Let's take an example to understand the Inner Join operation in SQL:
We have two tables, Customers and Orders, with the following data:
| Customers | Orders | |
|---|---|---|
| CustomerID | CustomerName | OrderID |
| 1 | John | 101 |
| 2 | Jane | 102 |
| 3 | Bob | 103 |
| 4 | Sara | 104 |
To retrieve the CustomerName and OrderID from both tables where the related CustomerID is equal, we can use the following SQL query:
SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
The above query will return the following result:
| CustomerName | OrderID |
|---|---|
| John | 101 |
| Jane | 102 |
| Bob | 103 |
| Sara | 104 |
The above result shows that only the matching rows from both tables are returned, and the non-matching rows are excluded.
Inner Join operation can also be used with more than two tables by adding more Inner Join clauses to the SQL query.
Let's take an example to understand Inner Join operation with more than two tables:
We have three tables, Customers, Orders, and OrderDetails, with the following data:
| Customers | Orders | OrderDetails | ||
|---|---|---|---|---|
| CustomerID | CustomerName | OrderID | CustomerID | OrderID |
| 1 | John | 101 | 1 | 101 |
| 2 | Jane | 102 | 2 | 102 |
| 3 | Bob | 103 | 3 | 103 |
| 4 | Sara | 104 | 4 | 104 |
To retrieve the CustomerName, OrderID, and ProductName from all three tables where the related columns are equal, we can use the following SQL query:
SELECT Customers.CustomerName, Orders.OrderID, OrderDetails.ProductName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;
The above query will return the following result:
| CustomerName | OrderID | ProductName |
|---|---|---|
| John | 101 | Product A |
| Jane | 102 | Product B |
| Bob | 103 | Product C |
| Sara | 104 | Product D |
The above result shows that Inner Join operation can be used with more than two tables to retrieve data from multiple tables that have a relationship between them.
In conclusion, Inner Join operation is a powerful tool in SQL that helps to retrieve data from multiple tables that have a relationship between them. It returns only the matching rows from both tables and excludes the non-matching rows. Inner Join operation can also be used with more than two tables to retrieve data from multiple tables that have a relationship between them.