Join and Subquery in SQL

In SQL querying, we sometimes need to fetch data from more than one table, for which we need to join the tables together or apply subqueries. Subqueries and the Join operation help us retrieve related data by linking the tables or running nested queries within a larger query.

In this chapter, we will have an elaborate discussion on joins and subqueries with the help of clear examples and their resulting tables.

Sample Tables and Data

For the examples in this chapter, we will use the following three tables: Customers, Orders, and Products. We will use these three tables to demonstrate joins and subqueries.

Here is the Customers table −

customer_id	first_name	last_name	email	phone
1	Amrita	Joshi	[email protected]	123-456-7890
2	Bimal	Saha	[email protected]	234-567-8901
3	Chandan	Tudu	[email protected]	345-678-9012

This is the Products table −

product_id	product_name	category	price	stock_quantity
101	Laptop	Electronics	1000.00	10
102	Smartphone	Electronics	800.00	20
103	Office Chair	Furniture	150.00	5

And, the Orders table is as follows −

order_id	customer_id	product_id	quantity	order_date
1	1	101	1	2023-11-01 10:00:00
2	2	103	2	2023-11-02 12:30:00
3	3	102	1	2023-11-03 15:45:00
4	1	103	1	2023-11-04 14:00:00

Joins in SQL

Joins combine rows from two or more tables based on a related column. In SQL we have option for several types of joins. We will see the most commonly used techniques.

The Inner Join

The Inner Join retrieves rows that have matching values in both tables. If some matching is not there the entire row will be neglected.

Example: Fetch Customer Orders

Use the following query to fetch all the customer orders −

SELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

This query combines the Orders, Customers, and Products tables to display complete order details −

first_name	last_name	product_name	quantity	order_date
Amrita	Joshi	Laptop	1	2023-11-01 10:00:00
Bimal	Saha	Office Chair	2	2023-11-02 12:30:00
Chandan	Tudu	Smartphone	1	2023-11-03 15:45:00
Amrita	Joshi	Office Chair	1	2023-11-04 14:00:00

The Left Join

The Left Join retrieves all the rows from the left table and the matching rows from the right table. If no match exists, NULL values are returned for the right table’s columns.

Example: Customers with or without Orders

Use the following query to fetch the customer details with or without orders −

SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFTJOIN Orders o ON c.customer_id = o.customer_id;

It fetches all customers, including those with no orders (if there were any).

first_name	last_name	order_id
Amrita	Joshi	1
Amrita	Joshi	4
Bimal	Saha	2
Chandan	Tudu	3

The Right Join

The Right Join retrieves all the rows from the right table and the matching rows from the left table.

Example: Products with or without Orders

Use the following query to fetch the products with or without orders −

SELECT p.product_name, o.order_id
FROM Products p
RIGHTJOIN Orders o ON p.product_id = o.product_id;

The query fetches all the rows from the Orders table. When it is a product that does not match, it will be NULL. All products are matched in this case.

product_name	order_id
Laptop	1
Office Chair	2
Smartphone	3
Office Chair	4

The Full Outer Join

Combining both the Left and Right join makes the new join a Full Outer Join. It will fetch all the rows from both the tables.

Subqueries in SQL

Subqueries are nested queries inside another query. They can return single values or entire tables. Subqueries are useful for solving complex problems step-by-step.

Subqueries in WHERE Clause

Subqueries are mostly used in the WHERE clause to filter data based on the result of another query.

Example: Find Customers Who Ordered the Most Expensive Product

Here, we have three queries all together. The innermost query finds the product_id having the maximum number of products. Then, this product_id is matched with the Orders table in the second inner query, from where it gets the customer_id. Based on the customer_id, it fetches the name of the customer.

SELECT first_name, last_name
FROM Customers
WHERE customer_id IN(SELECT customer_id
   FROM Orders
   WHERE product_id =(SELECT product_id
  FROM Products
  WHERE price =(SELECTMAX(price)FROM Products)));</pre>


This query identifies the customers who ordered the most expensive product (Laptop).



first_name
last_name
Amrita
Joshi



Subqueries in SELECT Clause



Subqueries can also be used in the SELECT clause to include calculated data in the output.



Example: Total Quantity Ordered by Each Product



Use the following query to get the details of the total quantity ordered by each product −



SELECT product_name,(SELECTSUM(quantity)FROM Orders
   WHERE Orders.product_id = Products.product_id)AS total_quantity_ordered
FROM Products;



This query calculates the total quantity ordered for each product using a subquery. It is an advanced version of GROUP BY querying.



product_name
total_quantity_ordered
Laptop
1
Smartphone
1
Office Chair
3



Subqueries in FROM Clause



Subqueries in the FROM clause are called derived tables and are used to create temporary tables.



Example: Find Revenue per Product



Use the following query to get the revenue details per product −



SELECT product_name, revenue
FROM(SELECT p.product_name,SUM(o.quantity * p.price)AS revenue
   FROM Orders o
   JOIN Products p ON o.product_id = p.product_id
   GROUPBY p.product_name
)AS ProductRevenue;



The subquery calculates revenue per product, and the main query fetches it for display.



product_name
revenu
Laptop
1000.00
Smartphone
800.00
Office Chair
450.00



When to Use Joins vs. Subqueries



Both the approaches, joins and subqueries, are equally useful and often interchangeable. However, the joining operation is avoided sometimes due to its resulting large intermediate table size. In such cases, using an inner query is much more efficient. But, choosing the right option depends on the situation.




Use Joins when you need to combine rows from multiple tables directly.



Use Subqueries when you need to break down a problem into steps or filter the data based on calculated values.