INNER JOIN and OUTER JOIN are types of SQL Joins used to combine data from two or more tables. SQL joins allow data to be extracted from multiple tables based on how the tables are related to each other through one or more keys or columns.
Inner Join:
- An Inner Join returns only the matching rows from both tables based on a specified condition.
- The resulting table will only contain rows where the join condition is true in both tables.
- Inner Join is the most commonly used type of Join.
- The below SQL query is an example of Inner Join.
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;Outer Join:
- Outer Join returns all the rows from one table and only the matching rows from the second table based on the specified condition.
- If the specified condition does not match any row in the second table, NULL values are used for that table.
- There are three types of Outer Join:
1. LEFT OUTER JOIN: returns all rows from the left table and matching rows from the right table. If no matching rows are found in the right table, NULL values are used.
2. RIGHT OUTER JOIN: returns all rows from the right table and matching rows from the left table. If no matching rows are found in the left table, NULL values are used.
3. FULL OUTER JOIN: returns all rows from both tables where there is a match. If no match is found for a table, NULL values are used.
- The below SQL query is an example of Left Outer Join.
SELECT *
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;Example:
Consider two tables: "Customers" and "Orders". Customers table has a column "CustomerId" while Orders table has a column "CustomerId" and "OrderId".
Customers Table:
| CustomerId | CustomerName | ContactName | Country |
|------------|--------------|-------------|---------|
| 1 | Alfreds | Maria | Germany |
| 2 | Bob's | Carlos | USA |
| 3 | Bruckner | Helena | Austria |Orders Table:
| OrderId | Product | CustomerId |
|---------|---------|------------|
| 1 | Coke | 2 |
| 2 | Pepsi | 1 |
| 3 | Fanta | 2 |
| 4 | Sprite | 4 |
| 5 | 7UP | 3 |Inner Join:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
INNER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId;Output:
| CustomerName | Product |
|--------------|---------|
| Alfreds | Pepsi |
| Bob's | Coke |
| Bob's | Fanta |Left Outer Join:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId;Output:
| CustomerName | Product |
|--------------|---------|
| Alfreds | Pepsi |
| Bob's | Coke |
| Bob's | Fanta |
| Bruckner | NULL |Right Outer Join:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId;Output:
| CustomerName | Product |
|--------------|---------|
| Alfreds | Pepsi |
| Bob's | Coke |
| Bob's | Fanta |
| NULL | Sprite |
| NULL | 7UP |Full Outer Join:
SELECT Customers.CustomerName, Orders.Product
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerId = Orders.CustomerId;Output:
| CustomerName | Product |
|--------------|---------|
| Alfreds | Pepsi |
| Bob's | Coke |
| Bob's | Fanta |
| Bruckner | NULL |
| NULL | Sprite |
| NULL | 7UP |