📌 Objective: To understand what SELF JOIN and CROSS JOIN are? and, when they can be used.

Introduction:

When analyzing data, there are often scenarios where you need to compare data within the same table or produce combinations of records from two tables. SELF JOIN and CROSS JOIN are powerful tools to achieve these objectives. In this module, using our electronics store database, we'll uncover the potential of these JOINs through practical examples.

Concepts:

1. SELF JOIN

A SELF JOIN is used to join a table to itself, primarily for comparison purposes within the same dataset.

Example 1: Question: "List all the smartphones with their name and price which has a lower price than iPhone 12"

SELECT A.product_name, A.price
FROM products A
JOIN products B 
ON A.price < B.price
AND B.product_name = 'iPhone 12'
AND A.category = 'Smartphone'

Sample Output (SQL format):

| product_name | price |
|--------------|-------|
| Pixel 5      | 699   |
| ...          | ...   |

<aside> 💡 You can use inequality “<” in the joins. JOINS are not just used on the categorical variables but can be used on other types of variables as and when needed. ****This is applicable to any kind of joins we have learned until now. Some SQL settings might not allow this but using “<” is a normal practice. One could argue that why did we need to join if we could just pull the price of the iPhone 12 manually and then filter the table? That’s not wrong at all and based on the use case, you might just do that but consider a case where prices keep changing all the time, this would be a more fail-safe approach.

</aside>

Example 2: Question: "List all the products with their name and product ID from the categories that are being sold by the brand “Apple”. Exclude Apple products from the final output"

SELECT A.id as product_id, A.product_name
FROM products A JOIN products B
ON A.category_id = B.category_id
WHERE B.brand in ('Apple') and A.Brand not in ('Apple')
GROUP BY A.id as product_id, A.product_name;

Sample Output: (SQL format)

| Product1     | Product2      | Price1 | Price2 |
|--------------|---------------|--------|--------|
| iPhone 12    | Pixel 5       | 799    | 789    |
| Bose QC35    | Sony WH-1000XM| 299    | 305    |
| ...          | ...           | ...    | ...    |