WikiGalaxy
SQL Self Join
Understanding SQL Self Join:
A self join is a regular join but the table is joined with itself. This is useful for comparing rows within the same table.
Use Case: Employee Hierarchy
To find employees and their managers, where both are in the same employees table.
SELECT e1.employee_id, e1.name, e2.name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
Sample Output:
This query lists each employee alongside their manager's name.
Self Join for Product Bundles
Concept:
Identify products that are bundled together in the same order.
SELECT a.order_id, a.product_id, b.product_id AS bundled_product_id
FROM order_details a
JOIN order_details b ON a.order_id = b.order_id AND a.product_id != b.product_id;
Explanation:
This query finds pairs of different products ordered together.
Self Join for Finding Duplicates
Scenario:
Determine duplicate entries in a table based on a specific column.
SELECT a.id, a.name
FROM users a
JOIN users b ON a.name = b.name AND a.id < b.id;
Result:
Lists users with the same name, showing only one instance of each duplicate pair.
Self Join for Comparing Dates
Objective:
Compare dates in the same table to find overlapping events.
SELECT a.event_id, a.start_date, a.end_date, b.event_id
FROM events a
JOIN events b ON a.start_date < b.end_date AND a.end_date > b.start_date AND a.event_id != b.event_id;
Outcome:
Identifies events that overlap in time with each other.
Self Join for Customer Referrals
Use Case:
Find customers who referred other customers.
SELECT a.customer_id, a.name, b.name AS referred_customer
FROM customers a
JOIN customers b ON a.customer_id = b.referred_by;
Explanation:
Displays the customer who made referrals and the customers they referred.
Self Join for Product Compatibility
Scenario:
Determine compatible products within the same category.
SELECT a.product_id, a.name, b.name AS compatible_product
FROM products a
JOIN products b ON a.category_id = b.category_id AND a.product_id != b.product_id;
Result:
Lists products that can be used together, based on their category.
Self Join for Matching Friendships
Concept:
Identify mutual friendships in a social network.
SELECT a.user_id, a.friend_id
FROM friendships a
JOIN friendships b ON a.user_id = b.friend_id AND a.friend_id = b.user_id;
Explanation:
Finds pairs of users who have added each other as friends.
Self Join for Location Proximity
Objective:
Identify locations that are near each other based on coordinates.
SELECT a.location_id, a.name, b.name AS nearby_location
FROM locations a
JOIN locations b ON ST_Distance(a.coordinates, b.coordinates) < 10 AND a.location_id != b.location_id;
Result:
Lists locations that are within 10 units of distance from each other.
