Objective: 🔍 Goal: Master the foundational techniques to combine rows from two or more tables using SQL JOIN operations, including INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN.

Introduction: 💡 In the world of relational databases, data often resides in different tables. But what if you need data from multiple tables in one go? Enter the JOIN operations. These operations let you combine rows from two or more tables based on related columns, helping you fetch comprehensive data insights.

1️⃣ INNER JOIN:

• Concept: The INNER JOIN fetches only the rows where there's a match in both tables.
• Sample Question: "Get the first name of every customer along with the order table columns?"

<aside> 💡 In order to solve this, we need to identify a common field or column in the customers and orders table. In this case the common column is customer_id. Why? It exist in both the tables and question explicitly mention that we need to add the customer name along with other columns in the orders table.

</aside>

Example:

SELECT orders.*, customers.first_name
FROM orders 
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

<aside> 💡 Please notice that how we have joined primary key - customer_id from customers table with foreign key customer_id in orders table to get the desired output.

</aside>

<aside> 💡 JOINS usually comprises of type of join (INNER, LEFT, RIGHT, FULL). Just JOIN usually means INNER JOIN. The common columns that we join 2 tables are defined under ON condition.

</aside>

• Sample Question: "Show product category and brand along with orderdetails table fields"

Example:

SELECT orderdetails.*, product.category, product.brand
FROM orderdetails
INNER JOIN product
ON customers.product_id = orders.product_id;