How to master SQL join – internal, external, via, and self -joining examples

introduction

SQL Joins is an essential feature of combining data from multiple tables based on a relevant column. Understanding different types of connections and their applications are necessary to work with relationships with relationships. In this article, we will explore different types of SQL with clear explanations and practical examples.

Table sample

We will use customer tables and the following requests to get examples:

Customer schedule:

Application table:

1. Internal joining: combining matching data

Inner Join retrieves the rows that have identical values ​​in both tables. This is the most common type of joining.

Example: combining customers and their orders

inquiry:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

a result:

clarification:

• Only customers who have orders appear in the result.
• Diana and Order 105 customer is excluded because he has no matching records in both tables.

2. The left join: including all records of the left table

The left is due to the return of all rows of the left table (customers), along with identical rows of the right schedule (requests). The rows that do not match the right table will have empty values.

Example: including customers without orders

inquiry:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

a result:

clarification:

• All customers are included, even if they do not have orders.
• Diana appears with Null for the product and quantity.

3. Correct joining: including all records of the right table

The right accession is the opposite of the left joining. It includes all rows of the right schedule (requests) and the identical rows of the left schedule (clients). Rows that do not match the left schedule will have empty values.

Example: including requests without customers

inquiry:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

a result:

clarification:

• All requests are included, even if they do not have identical clients.
• The request is 105 with Null for Name because Customerid = 5 is not in the customer table.

4. Full external accession: including all records of both tables

It combines the complete external link between the results of the left and right accession, and the restoration of all rows of both tables. The rows without matching will be free values ​​for lost columns.

Example: combining all customers and orders

inquiry:

SELECT Customers.Name, Orders.Product, Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

a result:

clarification:

• All customers and orders are included.
• Diana appears (no orders) and request 105 (no identical customer) with empty values.

5.

Return Cross Join to the decorative product of two tables, each row is paired with the left table with each row of the right table.

Example: Customer pairing products

inquiry:

SELECT Customers.Name, Orders.Product
FROM Customers
CROSS JOIN Orders;

a result:

clarification:

• Each customer is paired with each product, which leads to 20 rows (4 customers x 5 requests).

6. Self -joining: Join a table with himself

Self -joining is used to compare rows within the same table. It is useful for hierarchical or relationship data.

Example: The relationship of a manager manager

Suppose we have the employee table:

inquiryFinding employees and their managers.

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
LEFT JOIN Employees E2
ON E1.ManagerID = E2.EmployeeID;

a result:

clarification:

• The table with itself is included using Managerid and Employeid to connect employees to their managers.

Summary of access

conclusion

Understanding SQL is the key to work with relationship databases. Each type of joining is a unique purpose, and its mastery will help you combine data and analyze it efficiently. Do these examples to unify your understanding!

Thank you for spending time to explore data related visions with me. I can share you. If you find this information useful, I invite you to follow up or contact me on LinkedIn. Happy exploring!