Difference between JOIN and INNER JOIN?

on

 

JOIN and INNER JOIN are often used interchangeably in SQL to combine data from two or more tables based on a specified relationship. However, there is technically no difference between them; INNER JOIN is a specific type of JOIN.

In SQL, there are different types of joins:

  1. INNER JOIN (or just JOIN): Returns only the rows that have matching values in both tables involved in the join.

  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table (table1), and the matched rows from the right table (table2). The result will contain NULL values in the columns from the right table if there is no match.

  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table (table2), and the matched rows from the left table (table1). Similar to LEFT JOIN, it can also result in NULL values in the columns from the left table.

  4. FULL OUTER JOIN: Returns all the rows when there is a match in either the left table (table1) or the right table (table2). It includes rows from both tables and fills in NULL values where there is no match.

Here's an example to illustrate the difference between INNER JOIN and other types of joins using two hypothetical tables: Customers and Orders.

Customers Table:

diff
+----+-----------+
| ID | Name      |
+----+-----------+
| 1  | Alice     |
| 2  | Bob       |
| 3  | Charlie   |
| 4  | Dave      |
+----+-----------+

Orders Table:

diff
+----+------------+--------+
| ID | OrderDate  | Amount |
+----+------------+--------+
| 101| 2023-01-15 | 100    |
| 102| 2023-02-10 | 50     |
| 103| 2023-03-05 | 200    |
+----+------------+--------+

INNER JOIN Example:

If you want to retrieve a list of customers who have placed orders, you would use an INNER JOIN like this:

sql
SELECT Customers.Name, Orders.OrderDate, Orders.Amount
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID;

Result:

diff
+--------+------------+--------+
| Name   | OrderDate  | Amount |
+--------+------------+--------+
| Alice  | 2023-01-15 | 100    |
| Bob    | 2023-02-10 | 50     |
| Charlie| 2023-03-05 | 200    |
+--------+------------+--------+

In this example, only customers who have placed orders are returned because an INNER JOIN selects only the rows where there is a match between the specified columns (Customers.ID and Orders.CustomerID).

If you were to use a LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN instead, the result would include rows with NULL values for customers who haven't placed orders.

Comments

Post a Comment