SQL Tutorial



SQL JOINS


SQL JOINS

SQL JOINS are used to combine rows from two or more tables based on a related column between them. They allow you to query and analyze data from multiple tables in a single result set.

🔍 Pro Tip: Understanding SQL joins is essential for working with relational databases, as most data comes from multiple tables.

📊 Types of SQL JOINS

  • INNER JOIN – Combines rows from both tables where there is a match.
  • LEFT JOIN (or LEFT OUTER JOIN) – Returns all rows from the left table and matching rows from the right table.
  • RIGHT JOIN (or RIGHT OUTER JOIN) – Returns all rows from the right table and matching rows from the left table.
  • FULL JOIN (or FULL OUTER JOIN) – Returns all rows when there is a match in one of the tables.
  • CROSS JOIN – Returns the Cartesian product of both tables.

💡 Understanding SQL JOINS

Each join type helps us retrieve data from related tables in different ways. Let’s break them down with examples.

🔍 INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables.

Example: Get orders with customer details:

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

🔍 LEFT JOIN

A LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there is no match, NULL values will be returned for columns from the right table.

Example: Get all customers and their orders (if any):

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

🔍 RIGHT JOIN

A RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If there is no match, NULL values will be returned for columns from the left table.

Example: Get all orders and the corresponding customer details (if any):

SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers
ON orders.customer_id = customers.customer_id;

🔍 FULL JOIN

A FULL JOIN returns all rows when there is a match in one of the tables. If there is no match, NULL values will be returned for the table without a match.

Example: Get all customers and orders (even if there are no orders):

SELECT customers.name, orders.order_id
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;

🔍 CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning every row in the left table is combined with every row in the right table.

Example: Get every combination of products and customers:

SELECT products.product_name, customers.name
FROM products
CROSS JOIN customers;

⚙️ JOINs in Action: Visualizing the Results

✅ Interactive Task: Try combining different tables using the join types above. See how the results differ based on the join you use.

🔑 Key Takeaways

  • INNER JOIN: Combines only matching rows from both tables.
  • LEFT JOIN: Includes all rows from the left table and matched rows from the right table.
  • RIGHT JOIN: Includes all rows from the right table and matched rows from the left table.
  • FULL JOIN: Includes all rows from both tables, with NULL for unmatched rows.
  • CROSS JOIN: Combines every row from the first table with every row from the second table.

🔄 Next Step: Try creating your own SQL JOIN queries to practice with different tables!


🌟 Enjoyed Learning with Us?

Help others discover Technorank Learning by sharing your honest experience.
Your support inspires us to keep building!

Leave a Google Review