2.Working with Data
Mastering SQL Joins, Subqueries, and UNION Operations
Meta Description: Learn how to work with data in SQL using Joins, Subqueries, and UNION operations. Includes code examples, sample data, practice exercises, and real-world applications.
Focus Keywords: SQL Joins, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, SQL Subqueries, SQL UNION, SQL Tutorial, SQL Practice Exercises
1. SQL Joins
Joins are used to combine rows from two or more tables based on a related column between them.
Sample Tables
Customers
| CustomerID | Name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
Orders
| OrderID | CustomerID | Product |
|---|---|---|
| 101 | 1 | Book |
| 102 | 2 | Pen |
| 103 | 2 | Notebook |
INNER JOIN
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Output:
| Name | Product |
|---|---|
| Alice | Book |
| Bob | Pen |
| Bob | Notebook |
LEFT JOIN
SELECT Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
RIGHT JOIN
SELECT Customers.Name, Orders.Product
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
FULL OUTER JOIN
SELECT Customers.Name, Orders.Product
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
2. Subqueries
Subqueries are nested queries used to perform operations within SELECT, WHERE, or FROM clauses.
Subquery in SELECT
SELECT Name,
(SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;
Subquery in WHERE
SELECT Name
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE Product = ‘Pen’);
Subquery in FROM
SELECT AvgOrders.CustomerID, AvgOrders.AvgOrderCount
FROM (
SELECT CustomerID, COUNT(*) AS AvgOrderCount
FROM Orders
GROUP BY CustomerID
) AS AvgOrders;
3. SQL UNION vs JOIN
UNION combines results from multiple SELECT statements into a single result set. JOIN combines columns from multiple tables based on a related column.
SQL UNION Example
SELECT Name FROM Customers
UNION
SELECT Product FROM Orders;
SQL JOIN Example
SELECT Customers.Name, Orders.Product
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
Practice Exercises
- Create a query using LEFT JOIN to list all customers and their orders.
- Write a subquery to find customers who ordered more than one item.
- Use UNION to combine customer names and product names into one list.
Real-World Project Walkthrough
Project: E-commerce Order Analysis
- Design tables for Customers, Orders, Products.
- Use JOINs to generate customer purchase reports.
- Use subqueries to identify top customers by order volume.
- Use UNION to merge promotional and regular product lists.
FAQs
- Q: What is the difference between INNER JOIN and LEFT JOIN?
A: INNER JOIN returns only matching rows, while LEFT JOIN returns all rows from the left table and matched rows from the right. - Q: When should I use a subquery?
A: Use subqueries when you need to compute intermediate results or filter data based on another query. - Q: Is UNION the same as JOIN?
A: No. UNION combines rows vertically, JOIN combines columns horizontally.
Visual Aid Placeholders
📊 ER Diagram: Customers & Orders Relationship
📈 BI Dashboard: Monthly Sales by Customer
📉 Data Flow: Subquery Execution Flow